Clever Geek Handbook

Oracle - multiple insertion into collection of type tables

I have created the following object in oracle 11g.

CREATE OR REPLACE TYPE myObject as object(
fieldOne number,
fieldTwo number
);

And created a new table type myObject;

CREATE OR REPLACE TYPE myTable IS TABLE OF myObject;

Now I would like to create a new instance of myTable and add some hard-coded strings in myTable

at the SQL Plus command line and then pass the object in myProcedure

as a parameter.

I've tried the following:

declare newTable myTable;
begin
select myObject(50,5) bulk collect into newTable from dual;
select myObject(40,7) bulk collect into newTable from dual;
myProcedure(newTable);
commit;
end;

Which kind works, although the second statement select into

overwrites the first.

My question is, how can I add multiple rows to a newTable?

Thanks a lot in Advance :)

+3


source to share


1 answer 


declare
    newTable myTable;
begin
    newTable := myTable();
    newTable.extend(2); -- The desired size of the collection

    -- Oracle collections begin at index 1, not 0
    newTable(1) := myObject(50, 5);
    newTable(2) := myObject(40, 7);

    myProcedure(newTable);
end;


+4


source






More articles:


All Articles