Associative Array and it's collection Methods

CREATE OR REPLACE PROCEDURE xx_AssocArr
AS

TYPE xx_AsscArr IS TABLE OF VARCHAR2(20)
INDEX BY PLS_INTEGER;

TYPE xx_AsscArr1 IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;

TYPE xx_AsscArr2 IS TABLE OF VARCHAR2(40)
INDEX BY VARCHAR2(20);

TYPE xx_AsscArr3 IS TABLE OF NUMBER
INDEX BY VARCHAR2(20);

V1 xx_AsscArr;

V2 xx_AsscArr1;

V3 xx_AsscArr2;

V4 xx_AsscArr3;

i NUMBER;
J NUMBER;
z VARCHAR2(30);

BEGIN

dbms_output.put_line('Hi..');

/*Like in VARRAY and NESTED TABLE 
associative array doesn't require 
initialization directly values
can be assigned*/

--These are also called as key-value pairs

--v1 has values of string type and indexed by integer

v1(1000):='Vizag';

v1(1500):='Hyderabad';

v1(-100):='Atlanta';

dbms_output.put_line('FIRST INDEX of V1 : '||v1.FIRST);
dbms_output.put_line('LAST INDEX of V1 : '||v1.LAST);
dbms_output.put_line('count of V1 : '||v1.COUNT);

IF (v1.EXISTS(-100)) THEN
   dbms_output.put_line('Element exists at index -100 : '|| v1(-100));
ELSE
   dbms_output.put_line('Element does not exist');
END IF;

dbms_output.put_line('Index prior to element at 1000 : '||v1.PRIOR(1000));

dbms_output.put_line('Index next to element at 1000 : '||v1.NEXT(1000));

dbms_output.put_line('Printing V1');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

v1.DELETE(1000);

dbms_output.put_line('Printing V1');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

dbms_output.put_line('Working on V3');

/*v3 has values of string and indexed by strings!!
*/

v3('vizag'):='This is vizag city';

v3('pune'):='This is pune city';

v3('banglore'):='This is blore city';

dbms_output.put_line('Printing V3');

z:=v3.FIRST;

WHILE z is NOT NULL LOOP
dbms_output.put_line('Element at index # '||z||' '||v3(z));
        z:=v3.NEXT(z);
END LOOP;

v3.DELETE('pune');

dbms_output.put_line('Printing V3');

z:=v3.FIRST;

WHILE z is NOT NULL LOOP
dbms_output.put_line('Element at index # '||z||' '||v3(z));
        z:=v3.NEXT(z);
END LOOP;


EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error is : '||SQLERRM);

END xx_AssocArr;
/

Comparison of PL/SQL COLLECTIONS - III

NAME	VARRAY	NESTED TABLE	ASSOCIATIVE ARRAY
TRIM Removes one element from last in a collection	v1.TRIM	v1.TRIM	NA
TRIM(n) Removes n elements from last in a collection	v1.TRIM(3)	v1.TRIM(3)	NA
DELETE Deletes all the elements in a collection	v1.DELETE	v1.DELETE	v1.DELETE
DELETE(n) Deletes elements at index n in a collection	NA	v1.DELETE(6)	v1.DELETE(6)
DELETE(m,n) Deletes all the elements between m and n indexes in a collection	NA	v1.DELETE(4,7)	v1.DELETE(4,7)

Comparison of PL/SQL COLLECTIONS - II

NAME	VARRAY	NESTED TABLE	ASSOCIATIVE ARRAY
DECLARATION	TYPE VarTab IS VARRAY(10) of NUMBER;	TYPE VarTab IS TABLE OF NUMBER;	TYPE xx_AsscArr IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
INITIALIZATION	v1 := VarTab();	v1 := VarTab();	NA
ASSIGNMENT	v1.extend(); v1(1):=1001	v1.extend(); v1(1):=1001	v1(1):='Vizag';
LIMIT Gives Max length of collection	v1.LIMIT	NA as it is unbounded	NA as it is unbounded
EXTENDS Allocates one NULL element to a collection	v1.EXTENDS	v1.EXTENDS	NA
EXTENDS(n) Allocates n NULL elements to a collection	v1.EXTENDS(5)	v1.EXTENDS(5)	NA
EXTENDS(n,i) Allocates n elements with valueof element at index i collection	v1.EXTENDS(4,1)	v1.EXTENDS(4,1)	NA
EXISTS(n) Returns TRUE if element exists at Index n else returns FALSE	v1.EXISTS(-100)	v1.EXISTS(-100)	v1.EXISTS(-100)
COUNT Returns total no of elements in a collection	v1.COUNT	v1.COUNT	v1.COUNT
FIRST Returns the index of the first element in the collection	v1.FIRST	v1.FIRST	v1.FIRST
LAST Returns the index of the last element in the collection	v1.LAST	v1.LAST	v1.LAST
PRIOR(n) Returns the index of the element prior to the current element	v1.PRIOR(n)	v1.PRIOR(n)	v1.PRIOR(n)
NEXT(n) Returns the index of the element next to the current element	v1.NEXT(n)	v1.NEXT(n)	v1.NEXT(n)

Comparison of PL/SQL COLLECTIONS - I

NAME	VARRAY	NESTED TABLE	ASSOCIATIVE ARRAY
SIZE	Bounded	Unbounded	Unbounded
DENSE/SPARSE	DENSE	Both	Both
INDEX/SUBSCRIPT DATATYPE	Integer	Integer	Integer/String
WHERE CREATED	pl/sql block or at schema level	pl/sql block or at schema level	Only in a pl/sql block
CAN BE OBJECT TYPE ATTRIBUTE	Yes	No	No

PL/SQL Collection : Nested Table and it's Collection Methods

PL/SQL Collection : Nested Table and it's Collection Methods

Here I have created a procedure to see how to declare,initialize and manipulate a Nested Table.

Collection Methods on a Nested Table are :
FIRST,LAST,COUNT,
DELETE,DELETE(m,n),DELETE(n),
NEXT,PRIOR,
EXTENDS(n,i),EXTENDS(n),
EXISTS(n)

CREATE OR REPLACE PROCEDURE xx_nesttab
as

TYPE VarTab IS TABLE OF NUMBER;

V1 varTab;
--Initialize at declaration itself
v2 varTab := varTab(5001,5002,5003);
i NUMBER;
j NUMBER;

BEGIN

/*
Initialize the nestedTable using constructor
VarTab() is constructor which takes
same name as collection
*/

v1 := varTab();

v1.extend();

dbms_output.put_line(v1.COUNT);

v1(1):=1001;

dbms_output.put_line('First element in NestedTable : '||v1(1));

--assign 9 elements to existing Nested Table v1
v1.EXTEND(9);

FOR i in 2..10 LOOP
v1(i):=1000+i;
END LOOP;

--First and Last collection Methods
dbms_output.put_line('First Element Index : '||v1.FIRST);
dbms_output.put_line('Last Element Index  : '||v1.LAST);

--Print NestTab elements using First and Last methods

dbms_output.put_line('Elements in the NestTab are : ');

FOR i in v1.FIRST..v1.LAST LOOP
dbms_output.put_line('Element at index # '||i||' '||v1(i));
END LOOP;

--DELETE Method
v1.DELETE(10); -- Deletes element at index # 10

dbms_output.put_line('Elements in the NestTab are : ');

FOR i in v1.FIRST..v1.LAST LOOP
dbms_output.put_line('Element at index # '||i||' '||v1(i));
END LOOP;

--Say if we delete an element at index # 5
v1.DELETE(5); -- Deletes element at index # 5
--Now we cannot print using first and last methods

--Here we use NEXT and PRIOR Methods
dbms_output.put_line('Next index of an element : '||v1.NEXT(4));
dbms_output.put_line('Previous index of an element : '||v1.PRIOR(4));

dbms_output.put_line(' ');
dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

v1.DELETE(6,8); -- Deletes elements from 6th to 8th indexes

dbms_output.put_line(' ');
dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

v1.DELETE; -- Deletes all elements

dbms_output.put_line(v1.COUNT);

--Once we delete an element we need to assign and extending

--assign elements to existing Nested Table v1
v1.EXTEND(5);

FOR i in 1..5 LOOP
v1(i):=5000+i;
END LOOP;

dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

--EXTEND(n,i) assign element at index # 2 to 3 elements after last element
v1.EXTEND(3,2);

dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

dbms_output.put_line('Count before trim : '||v1.COUNT);
--TRIM Function 
v1.TRIM(2);
dbms_output.put_line('Count after trim : '||v1.COUNT);

--EXISTS(n) returns True if element exists at index # n

v1.DELETE(4);

if (v1.EXISTS(4)) THEN
dbms_output.put_line('Element Exists');
ELSE
dbms_output.put_line('Element doest not exist');
END IF;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line(SQLERRM);

END;
/

PL/SQL Collection : VARRAY and it's collection Methods Usage

PL/SQL Collection : VARRAY and it's collection Methods Usage

Collection Methods are used to manipulate Varray's

Collection Methods on VARRAY

1. count
2. limit
3. trim
4. next
5. prior
6. delete
7. extends
8. first
9. last

Here is a procedure which uses all collection methods

create or replace procedure xx_varray as -- Declare a user defined datatype Varray TYPE VarTab IS VARRAY(10) of NUMBER; -- Declare a variable of varray type v1 VarTab; i number; j number; BEGIN /* Initialize the varray using constructor VarTab() is constructor which takes same name as collection which is mandatory! */ v1 := VarTab(); --Limit gives maximum size of collection dbms_output.put_line('Max size of Varray : '||v1.limit); dbms_output.put_line(' '); --Count gives no of elements present in a collection dbms_output.put_line('No of elements in varray : '||v1.count); dbms_output.put_line(' '); --Assigning values to varray by extending till it's limit for i in 1..10 loop v1.extend(); v1(i):=i+100; end loop; -- to traverse from 1st element to last element use -- first and last methods dbms_output.put_line('Collection Method First : '||v1.first); dbms_output.put_line('Collection Method Last : '||v1.last); dbms_output.put_line(' '); --printing varray elements for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; --v1.delete; dbms_output.put_line(' '); dbms_output.put_line('Using TRIM method'); v1.trim; --trims last element in the varray dbms_output.put_line('Collection Method First : '||v1.first); dbms_output.put_line('Collection Method Last : '||v1.last); dbms_output.put_line(' '); --printing varray elements for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; dbms_output.put_line(' '); dbms_output.put_line('Using TRIM(n) Method'); v1.trim(2);--trims or deletes last 2 elements dbms_output.put_line('No of elements in varray : '||v1.count); dbms_output.put_line(' '); --printing varray elements after trimming for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; --Using Next and Prior Methods, these returns index values j:=3; dbms_output.put_line('next(3) : '||v1.next(3)); dbms_output.put_line(' '); dbms_output.put_line('prior(3) : '||v1.prior(3)); --Using method EXISTS(n), returns TRUE if element exists at index - n --else returns FALSE if (v1.exists(6)) then dbms_output.put_line('Element exists at index 6'); else dbms_output.put_line('Element doesnt exist at index 6'); end if; if (v1.exists(9)) then dbms_output.put_line('Element exists at index 9'); else dbms_output.put_line('Element doesnt exist at index 9'); end if; --Using DELETE method, deletes complete collection v1.delete; dbms_output.put_line(' '); dbms_output.put_line('No of elements in varray after DELETE Method : '||v1.count); dbms_output.put_line(' '); dbms_output.put_line('Again if we need to add an element use extend method'); v1.extend(); v1(1):=101; dbms_output.put_line(v1(1)); --Using EXTENDS(n,i) appends n copies of the ith element to a collection. v1.extend(3,1); dbms_output.put_line('Check the Varray Now: '); --printing varray elements for i in v1.first..v1.last LOOP dbms_output.put_line(v1(i)); END LOOP; --We can traverse Varray using next Collection method for printing/accessing collection elements j:=1; WHILE j is NOT NULL LOOP dbms_output.put_line(v1(j)); j:=v1.next(j); end loop; EXCEPTION When OTHERS then dbms_output.put_line(sqlerrm); END; /

Global Local Public Private terminology in a pl/sql Package

Upload Data into Table using SQL*Loader in Oracle Apps

Upload Data into Table using SQL*Loader in Oracle Apps

For SQL*Loader we need
control file
badfile
discardfile
and a data file which has data to be uploaded. We create a table : employee, in which data has to be uploaded

create table employee(eid number(3),ename char(20),dept char(20));

save below code as main.ctl
LOAD DATA INFILE '$1' APPEND INTO TABLE employee FIELDS TERMINATED BY ',' ( eid integer external, ename char, dept char )
below is the data file and save it as file.txt
999,ram,cse 101,vee,mpc 201,haa,bipc

Now control file and datafiles are ready. We need to create a concurrent program for this.
Place the control file in custom top, $XX_TOP/bin and mention data file path in the parameter form of the concurrent program. Create an executable

Create a concurrent program with a parameter which has the datafile path

Attach to a request group and run the program.