Question: I am getting an ORA-06530 error in my
PL/SQL. The following returns the ORA-06530:
SQL> create or replace type
rep_cnts is object (empno number,
2 cnt
number);
3 /
Type created.
SQL> create
or replace type repcnts is table of rep_cnts;
2 /
Type created.
1 create or replace
procedure ge_rm(empno_chng number) as
2 v_rm
emp.mgr%type;
3 type other_reportees is table of
emp.mgr%type;
4 v_other_reportees other_reportees;
5
reportees_n_counts repcnts;
6 begin
7
reportees_n_counts:= repcnts();
8 select mgr into
v_rm from emp where empno=empno_chng;
9 select empno
bulk collect into v_other_reportees from emp where mgr= v_rm and
empno <> empno_chng ;
10 for i in
1..v_other_reportees.count loop
11
reportees_n_counts.extend;
12 begin
13 select
mgr,count(*) into
reportees_n_counts(i).empno,reportees_n_counts(i).cnt from emp
where mgr=v_other_reportees(i) group by mgr;
14 exception
15 when no_data_found then
16
reportees_n_counts(i).empno:=v_other_reportees(i);
17
reportees_n_counts(i).cnt:=0;
18 end;
19 end loop;
20 for j in 1..reportees_n_counts.last loop
21
dbms_output.put_line(reportees_n_counts(j).empno||'-'||reportees_n_counts(j).cnt
);
22 end loop;
23* end;
SQL> /
Procedure
created.
SQL> exec ge_rm(7499);
BEGIN ge_rm(7499); END;
*
ERROR at line 1:
ORA-06530: Reference to uninitialized
composite
ORA-06512: at "SCOTT.GE_RM", line 16
ORA-01403: no
data found
ORA-06512: at line 1
What is the cause of the ORA-06530: Reference to uninitialized
composite error?
Answer:
The
oerr utility shows this for the ORA-06530 error:
ORA-06530: 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.
According to the information you have included, your error is at
line 16 of your procedure:
16
reportees_n_counts(i).empno:=v_other_reportees(i);
In this case, after the BEGIN, you need to initialize the object
using a constructor.
Another way to avoid the "ORA-06530: Reference to uninitialized
composite " is to use the RECORD type declaration:
1 create or replace
procedure ge_rm(empno_chng number) as
2 v_rm
emp.mgr%type;
3 type other_reportees is table of
emp.mgr%type;
4 v_other_reportees other_reportees;
5
type rep_cnts is record ( -- NEWLY INCLUDED RECORD DECLARATION
6
empno number, -- NEWLY INCLUDED RECORD DECLARATION
7 cnt number); -- NEWLY INCLUDED RECORD
DECLARATION
8 type repcnt is table of rep_cnts;
9
reportees_n_counts repcnt;
10 begin
11
reportees_n_counts:= repcnt();
12 select mgr into v_rm from emp
where empno=empno_chng;
13 select empno bulk collect into
v_other_reportees from emp where mgr= v_rm and empno <> empno_chng ;
14 for i in 1..v_other_reportees.count loop
15
reportees_n_counts.extend;
16 begin
17 select mgr,count(*)
into reportees_n_counts(i).empno,reportees_n_counts(i).cnt from emp
where mgr=v_other_reportees(i) group by mgr;
18 exception
19 when no_data_found then
20
reportees_n_counts(i).empno:=v_other_reportees(i);
21
reportees_n_counts(i).cnt:=0;
22 end;
23 end loop;
24
for j in 1..reportees_n_counts.last loop
25
dbms_output.put_line(reportees_n_counts(j).empno||'-'||reportees_n_counts(j).cnt
);
26 end loop;
27* end;
SQL> /
Procedure
created.
SQL> exec ge_rm(7499);
7521-0
7566-2
7654-0
7782-1
7844-0
PL/SQL procedure successfully
completed.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
