Thursday, 1 October 2009

Custom Collections and Objects

I've been struggling with using Custom collections and types this week in some PL/SQL code, eventually I found out the you have to initialise both the collection and the object otherwise you get a "ORA-06530: Reference to uninitialized composite" error.
CREATE OR REPLACE TYPE OBJ_VARCHAR AS OBJECT
( VCCOL VARCHAR2(250))

CREATE OR REPLACE type USER_TAB as table of OBJ_VARCHAR;

DECLARE
VARTAB
USER_TAB := USER_TAB(NULL);
VAROBJ OBJ_VARCHAR := OBJ_VARCHAR(NULL);
BEGIN

VARTAB.EXTEND;
VAROBJ := OBJ_VARCHAR('test entry');
VARTAB(VARTAB.LAST) := VAROBJ;

END;
Kerching!