Error ORA-06530 from function that returns an object type

Question

I have the following function GET_UN_COLLECTED_4LD which returns the type ld_data_type:

CREATE OR REPLACE TYPE ld_data_type AS OBJECT(collected NUMBER, uncollected NUMBER);
/

CREATE OR REPLACE FUNCTION GET_UN_COLLECTED_4LD (VAPPOINTOFCAID    NUMBER,
                                                 VPREVLIQUIDATE    NUMBER,
                                                 VCURRLIQUIDATE    NUMBER,
                                                 VNEXTLIQUIDATE    NUMBER,
                                                 YEPT              NUMBER)
   RETURN LD_DATA_TYPE
AS
   OUT_VAR        LD_DATA_TYPE;
   VNETV23        NUMBER;
   VNETV24        NUMBER;
   VCURR23        NUMBER;
   VCURR24        NUMBER;
   VCOLLECTED     NUMBER;
   VUNCOLLECTED   NUMBER;
BEGIN
   SELECT SUM (NETX) - SUM (NETP)
     INTO VNETV23
     FROM VIEW_CUSTOMER_TRN4INVS4LD
    WHERE APPOINTOFCAID = VAPPOINTOFCAID AND VAT = ABS (1.23);

   SELECT SUM (NETX) - SUM (NETP)
     INTO VNETV24
     FROM VIEW_CUSTOMER_TRN4INVS4LD
    WHERE APPOINTOFCAID = VAPPOINTOFCAID AND VAT = ABS (1.24);

   VCOLLECTED := 0;
   VUNCOLLECTED := 0;

   CASE
      WHEN YEPT = 0
      THEN
         VCURR24 := VCURRLIQUIDATE - VNETV24;

         CASE
            WHEN VCURR24 > 0
            THEN
               VCOLLECTED := VNETV24 * 1.24;
               VCURR23 := VCURRLIQUIDATE - VNETV23;

               CASE
                  WHEN VCURR23 > 0
                  THEN
                     VCOLLECTED := -999;
                  ELSE
                     VCOLLECTED :=
                        VCOLLECTED + ( (VCURRLIQUIDATE - VCURR24) * 1.23);
               END CASE;
            ELSE
               VCOLLECTED := -1999;
         END CASE;
      ELSE
         OUT_VAR.COLLECTED := -888;
         OUT_VAR.UNCOLLECTED := -889;
   END CASE;

   SELECT VCOLLECTED, VUNCOLLECTED
     INTO OUT_VAR.COLLECTED, OUT_VAR.UNCOLLECTED
     FROM DUAL;

       /*
OPEN buffer_cur;
FETCH buffer_cur INTO out_var.val1, out_var.val2;

CLOSE buffer_cur; */


   RETURN OUT_VAR;
END GET_UN_COLLECTED_4LD;

When I call the function like

select GET_UN_COLLECTED_4LD(171231, 42240, 31680, 0, 0) from dual;`

I get an error:

Execution (50: 8): ORA-06530: Reference to uninitialized composite
ORA-06512: at "C##SOLSA.GET_UN_COLLECTED_4LD", line 56

I'm use Oracle 12c Standard edition.

How should I be calling the function GET_UN_COLLECTED_4LD and Where is the problem that causes the error?

Answer 1

As the error says, you haven't initialised your object variable:

...
   RETURN LD_DATA_TYPE
AS
   OUT_VAR        LD_DATA_TYPE := new LD_DATA_TYPE(null, null);
...

Your logic is slightly confused though; at the end of your case you do:

  ELSE
     OUT_VAR.COLLECTED := -888;
     OUT_VAR.UNCOLLECTED := -889;

but then overwrite the value immediately afterwards with:

   SELECT VCOLLECTED, VUNCOLLECTED
     INTO OUT_VAR.COLLECTED, OUT_VAR.UNCOLLECTED
     FROM DUAL;

Possibly you meant to set VCOLLECTED and VUNCOLLECTED in that ELSE. If you did that then you wouldn't need to initialise OUT_VAR, as the first remaining reference would create it; which you could do as:

  OUT_VAR := LD_DATA_TYPE (VCOLLECTED, VUNCOLLECTED);

instead of selecting from dual; or you could not have the local variable at all and just do:

...
  ELSE
    VCOLLECTED := -888;
    VUNCOLLECTED := -889;
  END CASE;

  RETURN LD_DATA_TYPE (VCOLLECTED, VUNCOLLECTED);
END GET_UN_COLLECTED_4LD;

Answer 2

First you need to initialize your object type OUT_VAR before you can set its properties like this:

OUT_VAR:= LD_DATA_TYPE(0,0); 

See the details here.