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 |
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 |
|
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 |
Solomon Yakobson
Messages: 3273 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.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 03:56:55 CDT 2024
|