Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06530: Reference to uninitialized composite (Oracle 9i, Windows 2000)
ORA-06530: Reference to uninitialized composite [message #417629] Mon, 10 August 2009 11:25 Go to next message
xinyingho
Messages: 4
Registered: August 2009
Junior Member
Hello,

I get this error and I don't know how to fix it:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "PANCASH.PROFILE", line 53
ORA-06512: at line 9

Anybody can help ?
Here's the code:
CREATE TABLE PANCASH.TD_PROFILE
(
  CD_USER           VARCHAR2(6 BYTE),
  CD_KEY            VARCHAR2(64 BYTE),
  CD_TYPE           VARCHAR2(16 BYTE)           NOT NULL,
  LB_VALUE          CLOB,
  DT_LAST_MODIFIED  DATE                        NOT NULL
);

CREATE OR REPLACE TYPE PANCASH.PROFILE_REC AS OBJECT (
        key            VARCHAR2(64),
        type        VARCHAR2(256),
        value        CLOB
    );
CREATE OR REPLACE TYPE PANCASH.PROFILE_TAB AS TABLE OF PROFILE_REC;

CREATE OR REPLACE PACKAGE BODY PANCASH.Profile IS
    FUNCTION ToProfileRec (
        p_list          VARCHAR2
    ) RETURN PROFILE_REC
    IS
        l_idx           INTEGER;
        l_idx2            INTEGER;
        l_list          VARCHAR2(32767) := p_list;
        l_key            VARCHAR2(64);
        l_type            VARCHAR2(256);
        l_value            VARCHAR2(32767);
    BEGIN
        l_idx := INSTR(l_list,'|');
        l_idx2 := INSTR(l_list, '|', 1, 2);
        
        l_key := SUBSTR(l_list,1,l_idx-1);
        l_type := SUBSTR(l_list,l_idx+1, l_idx2-1);
        l_value := SUBSTR(l_list,l_idx2+1);
        
        RETURN PROFILE_REC(l_key, l_type, l_value);
    END ToProfileRec;
    
    FUNCTION ToProfileTab (
        p_list          VARCHAR2
    ) RETURN PROFILE_TAB PIPELINED
    IS
        l_idx           INTEGER;
        l_list          VARCHAR2(32767) := p_list;
    BEGIN
        LOOP
            l_idx := INSTR(l_list,';');
            
            IF l_idx > 0 THEN
                PIPE ROW(ToProfileRec(SUBSTR(l_list,1,l_idx-1)));
                l_list := SUBSTR(l_list,l_idx+1);
            ELSE
                PIPE ROW(ToProfileRec(l_list));
                EXIT;
            END IF;
        END LOOP;
        RETURN;
    END ToProfileTab;
    
    PROCEDURE RecordProfile (
        user        TD_PROFILE.CD_USER%TYPE,
        collection    VARCHAR2
    ) IS
        CURSOR profile_cur RETURN PROFILE_REC IS
            SELECT * FROM TABLE(CAST(ToProfileTab(collection) AS PROFILE_TAB));
        nbLines INTEGER;
    BEGIN
        
        FOR profileRow IN profile_cur LOOP
            SELECT count(*) INTO nbLines
            FROM TD_PROFILE
            WHERE CD_USER = user AND CD_KEY = profileRow.key;
            
            IF nbLines > 0 THEN
                UPDATE TD_PROFILE p
                SET
                    p.CD_TYPE = profileRow.type,
                    p.LB_VALUE = profileRow.value,
                    p.DT_LAST_MODIFIED = CURRENT_DATE
                WHERE
                    p.CD_USER = user AND
                    p.CD_KEY = profileRow.key;
            ELSE
                INSERT INTO TD_PROFILE (
                    CD_USER,
                    CD_KEY,
                    CD_TYPE,
                    LB_VALUE,
                    DT_LAST_MODIFIED
                ) VALUES (
                    user,
                    profileRow.key,
                    profileRow.type,
                    profileRow.value,
                    CURRENT_DATE
                );
            END IF;
        END LOOP;
    END RecordProfile;
END Profile;


To trigger the error, just run this:
DECLARE 
  USER VARCHAR2(200);
  COLLECTION VARCHAR2(200);

BEGIN 
  USER := 'Joe';
  COLLECTION := 'Key|Type|Value;Key2|Type2|Value2';

  PANCASH.PROFILE.RECORDPROFILE ( USER, COLLECTION );
  COMMIT; 
END; 


Thx for helping!
Re: ORA-06530: Reference to uninitialized composite [message #417630 is a reply to message #417629] Mon, 10 August 2009 11:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>ORA-06512: at "PANCASH.PROFILE", line 53
Too bad we don't know what line is #53.
Re: ORA-06530: Reference to uninitialized composite [message #417631 is a reply to message #417629] Mon, 10 August 2009 11:33 Go to previous messageGo to next message
xinyingho
Messages: 4
Registered: August 2009
Junior Member
I think it refers to this line in procedure RecordProfile:
FOR profileRow IN profile_cur LOOP


It seems like it doesn't like the mix of table of objects with CURSOR...
Re: ORA-06530: Reference to uninitialized composite [message #417632 is a reply to message #417629] Mon, 10 August 2009 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
6530, 00000, "Reference to uninitialized composite"
// *Cause:  An object, LOB, or other composite was referenced as a
//          left hand side without having been initialized.
// *Action: Initialize the composite with an appropriate constructor
//          or whole-object assignment.
Re: ORA-06530: Reference to uninitialized composite [message #417633 is a reply to message #417629] Mon, 10 August 2009 11:46 Go to previous messageGo to next message
xinyingho
Messages: 4
Registered: August 2009
Junior Member
I know about the uninitialized thing. But I really don't know what I must initialize. In my view, all seems fine.

I thinks the problem lies with the following cursor and the profileRow variable of the loop:
CURSOR profile_cur RETURN PROFILE_REC IS
            SELECT * FROM TABLE(CAST(ToProfileTab(collection) AS PROFILE_TAB));


Well, it's actually the first time I try to put a cursor on a table of objects. The cursor declaration is maybe wrong ?
Re: ORA-06530: Reference to uninitialized composite [message #417636 is a reply to message #417629] Mon, 10 August 2009 12:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
First make it work, then make it fancy.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

above might provide insight.
ASKTOM site has many fine coding examples.
Re: ORA-06530: Reference to uninitialized composite [message #417746 is a reply to message #417629] Tue, 11 August 2009 06:20 Go to previous message
xinyingho
Messages: 4
Registered: August 2009
Junior Member
Thx you for your help but I couldn't fix this.
So I just changed the ToProfileTab() function from a pipelined version to a regular one, removed the cursor in the RecordProfile procedure and just make a classic loop on the table returned by ToProfileTab().
Previous Topic: how to rewrite this query
Next Topic: Order by Issue in oracle ?
Goto Forum:
  


Current Time: Sun Dec 04 12:36:19 CST 2016

Total time taken to generate the page: 0.09692 seconds