Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06530: Reference to uninitialized composite (11.2.0.4.0)
ORA-06530: Reference to uninitialized composite [message #665783] Wed, 20 September 2017 10:25 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Hi All,

I am getting ORA-06530 while running the procedure. Please correct me where I am doing wrong.

CREATE OR REPLACE TYPE WBS_DET_OBJ IS OBJECT  
(   anchorid        VARCHAR2(32),
    anchortype      VARCHAR2(255),
    anchorname      VARCHAR2(255),
    assignmentid    VARCHAR2(32),
    assignmentname  VARCHAR2(255),
    owner           VARCHAR2(255),
    ret_code	    VARCHAR2(20),
    ret_msg	    VARCHAR2(1000),
    error_msg	    VARCHAR2(1000));

CREATE OR REPLACE TYPE WBS_DET_TAB IS TABLE OF WBS_DET_OBJ; 
create or replace PROCEDURE prc_eri_tagtofs_dup_anchor_val (
    p_wbselementid    VARCHAR2, p_WBS_DET_tab OUT WBS_DET_tab) AS

--V_TAGTOFS_WBSDET WBS_DET_tab := WBS_DET_tab();
V_TAGTOFS_WBSDET WBS_DET_tab;
v_start_time        NUMBER; 
v_repcount          NUMBER;
v_mapcount          NUMBER;
v_actcount          NUMBER;
v_owner_msg           VARCHAR2(10);
v_apr_msg           VARCHAR2(10);
v_sqlerrmsg         VARCHAR2(4000);
n                   NUMBER := 1;
--m                   number := 0;

BEGIN

--V_TAGTOFS_WBSDET := WBS_DET_tab();
v_start_time := dbms_utility.get_time();

INSERT INTO pc_eri_matching_log(qp_req_id,
                                    pcname,
                                    errmsg,
                                   loggedate)
                              VALUES (NULL,'PRC_ERI_TAGTOFS_DUP_ANCHOR_VAL','WBSelementID '||p_wbselementid, sysdate);
                              
    --Checking the existence of wbselement. If not exists then error or else proceed
    SELECT COUNT(1)
      INTO v_mapcount
    FROM pc_eri_erimatch_ast_anchor_map
    WHERE wbselementid = p_wbselementid;

    V_TAGTOFS_WBSDET.extend;
    
    IF v_mapcount = 0 THEN
        V_TAGTOFS_WBSDET(1).ret_code        := 0;
        V_TAGTOFS_WBSDET(1).ret_msg         := 'false';
        V_TAGTOFS_WBSDET(1).error_msg       := 'WBSelementId does not exist';
        v_sqlerrmsg                         := 'WBSelementId does not exist';
     
      INSERT INTO pc_eri_matching_log(QP_REQ_ID,
                                    PCNAME,
                                    ERRMSG,
                                   LOGGEDATE)
                              VALUES (NULL,'PRC_ERI_TAGTOFS_DUP_ANCHOR_VAL',v_sqlerrmsg , sysdate);        
        
    --webelement exist then check for user active with role existence.
    ELSIF v_mapcount > 0 THEN 

          FOR i IN (SELECT anchorid, anchortype, anchorname, assignmentid, assignmentname, owner
                      FROM pc_eri_erimatch_ast_anchor_map
                    WHERE wbselementid = p_wbselementid ) LOOP

              n := n + 1;
              V_TAGTOFS_WBSDET.extend;
          
              SELECT count(1) 
                INTO v_repcount
              FROM pc_eri_erimatch_user_repos
              WHERE corpid = V_TAGTOFS_WBSDET(n).OWNER;
             
              --owner is inactive or not exists is true then check the APRs status
              IF v_repcount = 0 THEN
                v_owner_msg := 'true';
              ELSE
                  SELECT count(1) 
                    INTO v_actcount
                  FROM pc_eri_erimatch_user_repos
                  WHERE corpid = V_TAGTOFS_WBSDET(n).OWNER
                  AND role IN ('RPM', 'RPM_RDM', 'RPM_RDM_Super User')
                  AND activeflag = 'true';
                  
                  if v_actcount = 0 then
                    v_owner_msg := 'true';
                  else  
                    v_owner_msg := 'false';
                  END IF;
              END IF;
              --Checking all the APRs statuses 
              FOR J IN ( SELECT pyid, pystatuswork FROM pc_eri_global_ematch_work
                          WHERE assignmentid = i.assignmentid
                          and pyid like 'APR%') LOOP

                      if ( (j.pyid is null and j.pystatuswork is null)
                        OR ( j.pyid is not null and j.pystatuswork not in ('Resolved-Cancelled', 'Delivery Completed') ) )then
                            --V_TAGTOFS_WBSDET(n).ret_code        := 0;
                            --V_TAGTOFS_WBSDET(n).ret_msg         := 'false';
                            v_apr_msg := 'false';
                      elsif j.pyid is not null and j.pystatuswork in ('Resolved-Cancelled', 'Delivery Completed') then
                            v_apr_msg := 'true';
                      end if;
                      
                      IF ( v_owner_msg = 'true' and v_apr_msg = 'true' ) THEN
                            V_TAGTOFS_WBSDET(n).anchorid        := i.anchorid;
                            V_TAGTOFS_WBSDET(n).anchortype      := i.anchortype;
                            V_TAGTOFS_WBSDET(n).anchorname      := i.anchorname;
                            V_TAGTOFS_WBSDET(n).assignmentid    := i.assignmentid;
                            V_TAGTOFS_WBSDET(n).assignmentname  := i.assignmentname;
                            V_TAGTOFS_WBSDET(n).owner           := i.owner; 
                            V_TAGTOFS_WBSDET(n).ret_code        := 1; 
                            V_TAGTOFS_WBSDET(n).ret_msg         := 'true';
                            V_TAGTOFS_WBSDET(n).error_msg       := 'Owner is inactive and APRs status is in Resolved-Cancelled and Delivery Completed';
                      ELSIF ( v_owner_msg = 'true' and v_apr_msg = 'false' ) THEN
                            V_TAGTOFS_WBSDET(n).anchorid        := i.anchorid;
                            V_TAGTOFS_WBSDET(n).anchortype      := i.anchortype;
                            V_TAGTOFS_WBSDET(n).anchorname      := i.anchorname;
                            V_TAGTOFS_WBSDET(n).assignmentid    := i.assignmentid;
                            V_TAGTOFS_WBSDET(n).assignmentname  := i.assignmentname;
                            V_TAGTOFS_WBSDET(n).owner           := i.owner; 
                            V_TAGTOFS_WBSDET(n).ret_code        := 0; 
                            V_TAGTOFS_WBSDET(n).ret_msg         := 'false';
                            V_TAGTOFS_WBSDET(n).error_msg       := 'Assignment contains APRs with other status';
                      ELSIF ( v_owner_msg = 'false' and v_apr_msg = 'true' ) THEN
                            V_TAGTOFS_WBSDET(n).anchorid        := i.anchorid;
                            V_TAGTOFS_WBSDET(n).anchortype      := i.anchortype;
                            V_TAGTOFS_WBSDET(n).anchorname      := i.anchorname;
                            V_TAGTOFS_WBSDET(n).assignmentid    := i.assignmentid;
                            V_TAGTOFS_WBSDET(n).assignmentname  := i.assignmentname;
                            V_TAGTOFS_WBSDET(n).owner           := i.owner; 
                            V_TAGTOFS_WBSDET(n).ret_code        := 0; 
                            V_TAGTOFS_WBSDET(n).ret_msg         := 'false';
                            V_TAGTOFS_WBSDET(n).error_msg       := 'Owner is Active';
                      ELSIF ( v_owner_msg = 'false' and v_apr_msg = 'false' ) THEN
                            V_TAGTOFS_WBSDET(n).anchorid        := i.anchorid;
                            V_TAGTOFS_WBSDET(n).anchortype      := i.anchortype;
                            V_TAGTOFS_WBSDET(n).anchorname      := i.anchorname;
                            V_TAGTOFS_WBSDET(n).assignmentid    := i.assignmentid;
                            V_TAGTOFS_WBSDET(n).assignmentname  := i.assignmentname;
                            V_TAGTOFS_WBSDET(n).owner           := i.owner; 
                            V_TAGTOFS_WBSDET(n).ret_code        := 0; 
                            V_TAGTOFS_WBSDET(n).ret_msg         := 'false';
                            V_TAGTOFS_WBSDET(n).error_msg       := 'Owner is Active and Assignment contains APRs with other status';
                      END IF;
              END LOOP;
          END LOOP;
        END IF;

END prc_eri_tagtofs_dup_anchor_val;
ORA-06530: Reference to uninitialized composite
ORA-06512: at "TEST.PRC_ERI_TAGTOFS_DUP_ANCHOR_VAL", line 44
ORA-06512: at line 7
Re: ORA-06530: Reference to uninitialized composite [message #665784 is a reply to message #665783] Wed, 20 September 2017 10:50 Go to previous messageGo to next message
fixxxer
Messages: 45
Registered: August 2014
Member
Hi,

You have created a type "WBS_DET_TAB", which is a table of type "WBS_DET_OBJ". If you are going to assign an element in the table "WBS_DET_TAB", it has to be in the form of "WBS_DET_OBJ".

You need to assign your values into the object ""WBS_DET_OBJ". Example snippet from your code, corrected:

    IF v_mapcount = 0 THEN
        ------------ THE BELOW PART IS WHERE THE PROBLEM IS! -------------
        --V_TAGTOFS_WBSDET(1).ret_code        := 0;
        --V_TAGTOFS_WBSDET(1).ret_msg         := 'false';
        --V_TAGTOFS_WBSDET(1).error_msg       := 'WBSelementId does not exist';
        v_sqlerrmsg                         := 'WBSelementId does not exist';
        ------------ IT SHOULD BE: -------------
        V_TAGTOFS_WBSDET(1) := WBS_DET_OBJ(NULL,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL,
                                           NULL,
                                           0,
                                           'false',
                                           'WBSelementId does not exist');
     
      INSERT INTO pc_eri_matching_log(QP_REQ_ID,
                                    PCNAME,
                                    ERRMSG,
                                   LOGGEDATE)
                              VALUES (NULL,'PRC_ERI_TAGTOFS_DUP_ANCHOR_VAL',v_sqlerrmsg , sysdate);        
        
    --webelement exist then check for user active with role existence.

Remember, the element in your table is an object, so when assigning a value to that element, it has to be in object form, i.e. WBS_DET_OBJ(........). You need to change this everywhere in your procedure that it is being done.

You should also uncomment this line:

V_TAGTOFS_WBSDET := WBS_DET_tab();

FIXXXER
Re: ORA-06530: Reference to uninitialized composite [message #665785 is a reply to message #665783] Wed, 20 September 2017 10:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
V_TAGTOFS_WBSDET.extend adds element to collection. You have collection of objects and object needs to be initialized (constructed) before you can reference its attributes. So look at:

   ELSIF v_mapcount > 0 THEN 

          FOR i IN (SELECT anchorid, anchortype, anchorname, assignmentid, assignmentname, owner
                      FROM pc_eri_erimatch_ast_anchor_map
                    WHERE wbselementid = p_wbselementid ) LOOP

              n := n + 1;
              V_TAGTOFS_WBSDET.extend;
          
              SELECT count(1) 
                INTO v_repcount
              FROM pc_eri_erimatch_user_repos
              WHERE corpid = V_TAGTOFS_WBSDET(n).OWNER;

n = 2 while nothing is assigned to object V_TAGTOFS_WBSDET(2).

SY.
Re: ORA-06530: Reference to uninitialized composite [message #665789 is a reply to message #665785] Wed, 20 September 2017 11:47 Go to previous message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you very much Fixxxer and SY
Previous Topic: compare 2 collections data
Next Topic: Update Logic
Goto Forum:
  


Current Time: Thu Mar 28 16:42:03 CDT 2024