Giorgos Giorgos - 23 days ago 8
SQL Question

Error ORA-06530 from function that returns an object type

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

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.