CODEWITHSUNDEEP
oracle-databaseplsql
user2254180
user2254180

Reputation: 856

Calling Oracle procedure with a Type

I want to create a simple table that contains an Oracle UDT, create a procedure that will let me add values, and then test the procedure. I have the following code.

    CREATE OR REPLACE TYPE IDS_TYPE AS OBJECT ( id1 NUMBER, id2 NUMBER, id3 NUMBER );

CREATE OR REPLACE TYPE IDS_TABLE AS TABLE OF IDS_TYPE;

CREATE OR REPLACE PROCEDURE getInfo(p_ids IN IDS_TABLE) IS
  BEGIN
     FOR i IN 1 .. p_ids.COUNT LOOP
        dbms_output.put_line(p_ids(i).id1
                             || ',' || p_ids(i).id2
                             || ',' || p_ids(i).id3);
     END LOOP;
  END getInfo;

declare
      my_data   IDS_TABLE;
  begin
      my_data(1).id1 := 1234;
      my_data(1).id2 := 10;
      my_data(1).id3 := 10;

      my_data(2).id1 := 1234;
      my_data(2).id2 := 10;
      my_data(2).id3 := 10;

       getInfo( my_data );
   end;

I get the following error however. Can someone point out the mistake?

Error report -
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 4
06531. 00000 -  "Reference to uninitialized collection"
*Cause:    An element or member function of a nested table or varray
           was referenced (where an initialized collection is needed)
           without the collection having been initialized.
*Action:   Initialize the collection with an appropriate constructor
           or whole-object assignment.

Upvotes: 1

Views: 175

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You have to better handle the initialization and the creation of object variables; for example you could do :

declare
    my_data   IDS_TABLE;
    my_data2  IDS_TABLE;
begin
    /* initialize  the collection */ 
    my_data := new IDS_TABLE();

    /* extend to host a new value */
    my_data.extend(1);
    /* create the object */
    my_data(1) := new IDS_TYPE(1234, 10, 10);

    my_data.extend(1);
    my_data(2) := new IDS_TYPE(1234, 10, 10);

     /* you can change the value of an attribute of a given element of the collection */
    my_data(2).id3 := 99999;

    getInfo( my_data );

    /* OR in a more compact way, as suggested by Peter Lang */
    my_data2 := NEW ids_table( NEW ids_type(991234, 9910, 9910), NEW ids_type(991234, 9910, 9910) );
    getInfo( my_data2 );
end;

Upvotes: 2

Related Questions