3

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 :)

Timbob
  • 35
  • 1
  • 4

1 Answers1

4
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;
Adam Paynter
  • 44,176
  • 30
  • 143
  • 162
  • Perfect :) Thank you very much! One question - after the begin statement, why do you have newTable := myTable(); when newTable has already been declared as being of type myTable? – Timbob Jan 20 '13 at 21:25
  • @Timbob: If I understand correctly, `newTable := myTable()` *initializes* `newTable`. Before that statement, `newTable` is *uninitialized*. In that state, any usage of `newTable` would result in an error being raised. – Adam Paynter Jan 20 '13 at 23:14
  • 2
    @Timbob fyi a slightly shorter way of doing this is just `newTable := myTable(myObject(50, 5), myObject(40, 7));` ie no need for the initilisation or extend. – DazzaL Jan 21 '13 at 10:29
  • @DazzaL Thanks - thats tidied things up nicely :) – Timbob Jan 22 '13 at 20:58