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 of myObject;
CREATE OR REPLACE TYPE myTable IS TABLE OF myObject;
I would now like to create a new instance of myTable and add several hard-coded rows to myTable
on the SQL Plus command line then pass the object to myProcedure
as a parameter.
I have 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 sort-of works although the second select into
statement overwrites the first.
My question is; how can I add multiple rows to newTable?
Many Thanks in Advance :)
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;