Hi,
I tried to insert collection of data ( below) into two individual tables, when i was tried to insert single collection of data into two individual tables it was inserted.
But when i tired to insert more than one collections into two individual tables i am getting below error.
elow is my PLSQL CODE with error details,
could you please give me solution.
thanks in advance .
set serveroutput on
DECLARE
/*
pl ORG_ADDRESSES_PHONES_TAB := ORG_ADDRESSES_PHONES_TAB(ORG_ADDRESSES_PHONES_TYPE( 'a1','c1','s1','p1',1,ORG_PHONES_TAB(ORG_PHONES_TYPE('a2','p1','s1','e1'))));
inserted if single collections
*/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------ But Below data is not inserting because of multiple collections ----------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pl ORG_ADDRESSES_PHONES_TAB := ORG_ADDRESSES_PHONES_TAB(ORG_ADDRESSES_PHONES_TYPE( 'a1','c1','s1','p1',1,ORG_PHONES_TAB(ORG_PHONES_TYPE('a2','p1','s1','e1'))),ORG_ADDRESSES_PHONES_TYPE( 'a2','c2','s3','p4',2,ORG_PHONES_TAB(ORG_PHONES_TYPE('d3','f3','ws3','re3'))));
BEGIN
FOR i IN pl.FIRST..pl.LAST
LOOP
pl.EXTEND;
dbms_output.put_line(i);
dbms_output.put_line('next '||pl(i).ORG_PHONES.next(1));
IF pl.EXISTS(i) THEN -- if item exists then display
dbms_output.put_line('ADDRESS '||pl(i).ADDRESS);
dbms_output.put_line('CITY '||pl(i).CITY);
dbms_output.put_line('STATE_CODE '||pl(i).STATE_CODE);
dbms_output.put_line('POSTAL_CODE '||pl(i).POSTAL_CODE);
dbms_output.put_line('PRIMARY_YESNO '||pl(i).PRIMARY_YESNO);
dbms_output.put_line('AREACODE ' ||pl(i).ORG_PHONES(i).AREACODE);
dbms_output.put_line('PREFIX ' ||pl(i).ORG_PHONES(i).PREFIX);
dbms_output.put_line('SUFFIX ' ||pl(i).ORG_PHONES(i).SUFFIX);
dbms_output.put_line('EXTENSION ' ||pl(i).ORG_PHONES(i).EXTENSION);
--pl.EXTEND;
--pl(i).ORG_PHONES.EXTEND;
END IF;
pl(i).ORG_PHONES.EXTEND;
END LOOP;
COMMIT;
--dbms_output.put_line( 'hii');
end;
----------------------------------------
Show parameters query failed
Error starting at line : 3 in command -
DECLARE
-- declare a variable of projecttable type
pl ORG_ADDRESSES_PHONES_TAB := ORG_ADDRESSES_PHONES_TAB(ORG_ADDRESSES_PHONES_TYPE( 'a1','c1','s1','p1',1,ORG_PHONES_TAB(ORG_PHONES_TYPE('a2','p1','s1','e1'))),ORG_ADDRESSES_PHONES_TYPE( 'ta2','uc2','vs3','wp4',2,ORG_PHONES_TAB(ORG_PHONES_TYPE('d3','f3','ws3','re3'))));
-- procedure to display the values of the table
begin -- beginning of the main block
for i in pl.first..pl.last
loop
dbms_output.put_line(i);
dbms_output.put_line('ADDRESS '||pl(i).ADDRESS);
dbms_output.put_line('CITY '||pl(i).CITY);
dbms_output.put_line('STATE_CODE '||pl(i).STATE_CODE);
dbms_output.put_line('POSTAL_CODE '||pl(i).POSTAL_CODE);
dbms_output.put_line('PRIMARY_YESNO '||pl(i).PRIMARY_YESNO);
dbms_output.put_line('AREACODE ' ||pl(i).ORG_PHONES(i).AREACODE);
dbms_output.put_line('PREFIX ' ||pl(i).ORG_PHONES(i).PREFIX);
dbms_output.put_line('SUFFIX ' ||pl(i).ORG_PHONES(i).SUFFIX);
dbms_output.put_line('EXTENSION ' ||pl(i).ORG_PHONES(i).EXTENSION);
-- pl.EXTEND;
pl(i).ORG_PHONES.EXTEND;
end loop;
COMMIT;
--dbms_output.put_line( 'hii');
end;
Error report -
ORA-06533: Subscript beyond count
ORA-06512: at line 19
06533. 00000 - "Subscript beyond count"
*Cause: An in-limit subscript was greater than the count of a varray
or too large for a nested table.
*Action: Check the program logic and explicitly extend if necessary.
1
ADDRESS a1
CITY c1
STATE_CODE s1
POSTAL_CODE p1
PRIMARY_YESNO 1
AREACODE a2
PREFIX p1
SUFFIX s1
EXTENSION e1
2
ADDRESS ta2
CITY uc2
STATE_CODE vs3
POSTAL_CODE wp4
PRIMARY_YESNO 2