You Asked
Hi, I have the following test code that is giving the issue mentioned above - I've reviewed other threads given on this issue and managed to nearly get it working but having 2 issues as mentioned in the questions below - In this simple example, I've just 1 header record with 4 lines but in reality there will be many headers with differing number of lines
Effectively, I'm trying to build a PLSQL Table TYPE array from the records in the STAGING table for then passing into another program
-------------------------------------
drop package body TEST;
drop package TEST;
drop TYPE TEST_HEADER_TYPE;
drop TYPE TEST_HEADER_OBJ;
drop TYPE TEST_LINE_TYPE;
drop TYPE TEST_LINE_OBJ;
drop table TEST_HEADER_STAGING;
drop table TEST_LINE_STAGING;
set serverout on size 999999;
create or replace TYPE TEST_LINE_OBJ is object (line_id number, col1 varchar2(100));
/
create or replace TYPE TEST_LINE_TYPE is table of TEST_LINE_OBJ;
/
create or replace TYPE TEST_HEADER_OBJ is object (header_id number, col1 varchar2(100),
line_info TEST_LINE_TYPE);
/
create or replace TYPE TEST_HEADER_TYPE is table of TEST_HEADER_OBJ;
/
create table TEST_HEADER_STAGING (header_index number, col1 varchar2(100));
create table TEST_LINE_STAGING (header_index number, line_index number, col1 varchar2
(100));
insert into TEST_HEADER_STAGING (header_index, col1) values (1, 'invoice1');
insert into TEST_LINE_STAGING (header_index, line_index, col1) values (1,1,'line1');
insert into TEST_LINE_STAGING (header_index, line_index, col1) values(1,2,'line2');
insert into TEST_LINE_STAGING (header_index, line_index, col1) values(1,3,'line3');
insert into TEST_LINE_STAGING (header_index, line_index, col1) values(1,4,'line4');
commit;
create or replace PACKAGE TEST AS
PROCEDURE TEST;
END;
/
create or replace PACKAGE BODY TEST AS
PROCEDURE TEST IS
acc_header TEST_HEADER_TYPE := TEST_HEADER_TYPE();
acc_line TEST_LINE_TYPE := TEST_LINE_TYPE();
CURSOR cur_get_headers IS
select header_index, col1
from test_header_staging
order by header_index;
CURSOR cur_get_lines IS
select header_index, line_index, col1
from test_line_staging
order by header_index, line_index;
BEGIN
acc_header := TEST_HEADER_TYPE(null);
acc_line := TEST_LINE_TYPE(null);
FOR hrec IN cur_get_headers LOOP
FOR lrec IN cur_get_lines LOOP
acc_line.extend;
acc_line(lrec.line_index) := TEST_LINE_OBJ(lrec.line_index,lrec.col1);
END LOOP;
acc_header.extend;
acc_header(hrec.header_index) := TEST_HEADER_OBJ(hrec.header_index,
hrec.col1,
TEST_LINE_TYPE(acc_line(1), <-- How do I not hard code these lines
acc_line(2), <-- as the number will change per Invoice Header
acc_line(3),
acc_line(4)));
END LOOP;
FOR i IN acc_header.FIRST .. acc_header.LAST LOOP
dbms_output.put_line('Header ID = '||acc_header(i).header_id);
dbms_output.put_line('Header Col1 = '||acc_header(i).col1);
dbms_output.put_line('Line ID = '||acc_header(i).line_info(1).line_id);
dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(1).col1);
dbms_output.put_line('Line ID = '||acc_header(i).line_info(2).line_id);
dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(2).col1);
dbms_output.put_line('Line ID = '||acc_header(i).line_info(3).line_id);
dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(3).col1);
dbms_output.put_line('Line ID = '||acc_header(i).line_info(4).line_id);
dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(4).col1);
END LOOP;
END;
END;
/
show error
exec test.test
------------------------------------------------
This gives results
Header ID = 1
Header Col1 = invoice1
Line ID = 1
Line Col1 = line1
Line ID = 2
Line Col1 = line2
Line ID = 3
Line Col1 = line3
Line ID = 4
Line Col1 = line4
Header ID =
Header Col1 =
BEGIN test.test; END;
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "APPS.TEST", line 45
ORA-06512: at line 1
-------------------------------------------------------
Questions
1. Why is there an additional NULL element in the HEADER array ?
2. How can I populate the lines embedded in the header Object Type without hard coding the number of lines ? In this example, I've just 4 lines
but I won't know how many are usually in the Line Staging table for each header.
and Connor said...
1) Because x := nested_table(null) is not the same as x:= nested_table();
2) You dont need to.. you just assign the whole array
SQL>
SQL> drop package body TEST;
Package body dropped.
SQL> drop package TEST;
Package dropped.
SQL> drop TYPE TEST_HEADER_TYPE;
Type dropped.
SQL> drop TYPE TEST_HEADER_OBJ;
Type dropped.
SQL> drop TYPE TEST_LINE_TYPE;
Type dropped.
SQL> drop TYPE TEST_LINE_OBJ;
Type dropped.
SQL> drop table TEST_HEADER_STAGING purge;
Table dropped.
SQL> drop table TEST_LINE_STAGING purge;
Table dropped.
SQL>
SQL> set serverout on size 999999;
SQL> create or replace TYPE TEST_LINE_OBJ is object (line_id number, col1 varchar2(100));
2 /
Type created.
SQL>
SQL> create or replace TYPE TEST_LINE_TYPE is table of TEST_LINE_OBJ;
2 /
Type created.
SQL>
SQL> create or replace TYPE TEST_HEADER_OBJ is object (header_id number, col1 varchar2(100),
2 line_info TEST_LINE_TYPE);
3 /
Type created.
SQL>
SQL> create or replace TYPE TEST_HEADER_TYPE is table of TEST_HEADER_OBJ;
2 /
Type created.
SQL>
SQL> create table TEST_HEADER_STAGING (header_index number, col1 varchar2(100));
Table created.
SQL>
SQL> create table TEST_LINE_STAGING (header_index number, line_index number, col1 varchar2(100));
Table created.
SQL>
SQL> insert into TEST_HEADER_STAGING (header_index, col1) values (1, 'invoice1');
1 row created.
SQL> insert into TEST_LINE_STAGING (header_index, line_index, col1) values (1,1,'line1');
1 row created.
SQL> insert into TEST_LINE_STAGING (header_index, line_index, col1) values(1,2,'line2');
1 row created.
SQL> insert into TEST_LINE_STAGING (header_index, line_index, col1) values(1,3,'line3');
1 row created.
SQL> insert into TEST_LINE_STAGING (header_index, line_index, col1) values(1,4,'line4');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL>
SQL> create or replace PACKAGE TEST AS
2 PROCEDURE TEST;
3 END;
4 /
Package created.
SQL>
SQL>
SQL>
SQL> create or replace PACKAGE BODY TEST AS
2
3 PROCEDURE TEST IS
4 acc_header TEST_HEADER_TYPE := TEST_HEADER_TYPE();
5 acc_line TEST_LINE_TYPE := TEST_LINE_TYPE();
6
7 CURSOR cur_get_headers IS
8 select header_index, col1
9 from test_header_staging
10 order by header_index;
11
12 CURSOR cur_get_lines IS
13 select header_index, line_index, col1
14 from test_line_staging
15 order by header_index, line_index;
16
17
18
19 BEGIN
20
21 acc_header := TEST_HEADER_TYPE();
22 acc_line := TEST_LINE_TYPE();
23
24 FOR hrec IN cur_get_headers LOOP
25 FOR lrec IN cur_get_lines LOOP
26 acc_line.extend;
27 acc_line(lrec.line_index) := TEST_LINE_OBJ(lrec.line_index,lrec.col1);
28 END LOOP;
29
30 acc_header.extend;
31 acc_header(hrec.header_index) :=
32 TEST_HEADER_OBJ(hrec.header_index,
33 hrec.col1,
34 acc_line
35 );
36
37 END LOOP;
38
39 FOR i IN acc_header.FIRST .. acc_header.LAST LOOP
40
41 dbms_output.put_line('Header ID = '||acc_header(i).header_id);
42 dbms_output.put_line('Header Col1 = '||acc_header(i).col1);
43 dbms_output.put_line('Line ID = '||acc_header(i).line_info(1).line_id);
44 dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(1).col1);
45 dbms_output.put_line('Line ID = '||acc_header(i).line_info(2).line_id);
46 dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(2).col1);
47 dbms_output.put_line('Line ID = '||acc_header(i).line_info(3).line_id);
48 dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(3).col1);
49 dbms_output.put_line('Line ID = '||acc_header(i).line_info(4).line_id);
50 dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(4).col1);
51
52 END LOOP;
53 END;
54
55 END;
56
57 /
Package body created.
SQL>
SQL> show error
No errors.
SQL> exec test.test
Header ID = 1
Header Col1 = invoice1
Line ID = 1
Line Col1 = line1
Line ID = 2
Line Col1 = line2
Line ID = 3
Line Col1 = line3
Line ID = 4
Line Col1 = line4
PL/SQL procedure successfully completed.
SQL>
SQL>
Or, if you're not getting paid by the line of code :-) you could do this
SQL> create or replace PACKAGE BODY TEST AS
2
3 PROCEDURE TEST IS
4 acc_header TEST_HEADER_TYPE := TEST_HEADER_TYPE();
5 begin
6 select TEST_HEADER_OBJ(hrec.header_index,
7 hrec.col1,
8 cast(multiset(
9 select TEST_LINE_OBJ(lrec.line_index,lrec.col1)
10 from test_line_staging lrec) as TEST_LINE_TYPE)
11 )
12 bulk collect into acc_header
13 from test_header_staging hrec
14 order by header_index;
15
16 FOR i IN acc_header.FIRST .. acc_header.LAST LOOP
17
18 dbms_output.put_line('Header ID = '||acc_header(i).header_id);
19 dbms_output.put_line('Header Col1 = '||acc_header(i).col1);
20 dbms_output.put_line('Line ID = '||acc_header(i).line_info(1).line_id);
21 dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(1).col1);
22 dbms_output.put_line('Line ID = '||acc_header(i).line_info(2).line_id);
23 dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(2).col1);
24 dbms_output.put_line('Line ID = '||acc_header(i).line_info(3).line_id);
25 dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(3).col1);
26 dbms_output.put_line('Line ID = '||acc_header(i).line_info(4).line_id);
27 dbms_output.put_line('Line Col1 = '||acc_header(i).line_info(4).col1);
28
29 END LOOP;
30 END;
31
32 END;
33
34 /
Package body created.
SQL>
SQL> show error
No errors.
SQL> exec test.test
Header ID = 1
Header Col1 = invoice1
Line ID = 1
Line Col1 = line1
Line ID = 2
Line Col1 = line2
Line ID = 3
Line Col1 = line3
Line ID = 4
Line Col1 = line4
PL/SQL procedure successfully completed.
SQL>
SQL>