CODEWITHSUNDEEP
oracle-databasefunctionprocedure
user1308908
user1308908

Reputation:

Struggling with Oracle Function with parameter (Reference to uninitialized collection)

I can't find what is wrong here, finally I decided to ask here for help.

This is how I am invoking:

  DECLARE
   RESSULT VARCHAR2(1000 CHAR);
   V_TEST1 CARS_TABLE:=CARS_TABLE();
   idx pls_integer :=0;
   BEGIN 
   V_TEST1.EXTEND(2);
    IDX := V_TEST1.COUNT; -- here there is NO problem

  RESSULT:=MYFUNCTION(1, V_TEST1);
  END;

This is what I am invoking..

CREATE OR REPLACE
  FUNCTION MYFUNCTION(
  CAR_ID IN INTEGER,
  MYOUTTABLE OUT CARS_TABLE)
RETURN VARCHAR2
 IS
RESSULT VARCHAR2(1000 CHAR);
idx pls_integer :=0;
BEGIN
IDX := MYOUTTABLE.COUNT; - HERE IS PROBLEM.. but why - I pass initialized collection
RETURN 'return param';
END MYFUNCTION;


 CREATE OR REPLACE TYPE carElement
IS
OBJECT
 (
  kind VARCHAR2(60),
    created DATE );
CREATE OR REPLACE TYPE CARS_TABLE

AS TABLE OF carElement;

  1. 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: 0

Views: 973

Answers (1)

DazzaL
DazzaL

Reputation: 21993

"IDX := MYOUTTABLE.COUNT; - HERE IS PROBLEM.. but why - I pass initialized collection"

you're resetting the collection to NULL on the input to the function call as you've defined the parameter as OUT which automatically nulls out the parameter as it calls the function (documentation link).

 MYOUTTABLE OUT CARS_TABLE

if you want to preserve the input, then you need to pass it as IN or if you want to preserve teh input and also manipulate the object in the function then use IN OUT.

eg note with the first call it shows its null if i use out:

SQL> declare
  2    v_test1 cars_table:=cars_table();
  3
  4    function myfunction(
  5      car_id in integer,
  6      myouttable  &mode cars_table)
  7    return varchar2
  8    is
  9      ressult varchar2(1000 char);
 10      idx pls_integer :=0;
 11    begin
 12      if (myouttable is null)
 13      then
 14        dbms_output.put_line('ARRAY IS NULL');
 15      else
 16        idx := myouttable.count;
 17      end if;
 18      return 'return param';
 19    end myfunction;
 20  begin
 21
 22    v_test1.extend(2);
 23    dbms_output.put_line(myfunction(1, v_test1));
 24
 25  end;
 26  /
Enter value for mode: OUT
old   6:     myouttable  &mode cars_table)
new   6:     myouttable  OUT cars_table)
ARRAY IS NULL
return param

PL/SQL procedure successfully completed.

SQL> /
Enter value for mode: IN OUT
old   6:     myouttable  &mode cars_table)
new   6:     myouttable  IN OUT cars_table)
return param

PL/SQL procedure successfully completed.

Upvotes: 2

Related Questions