Home » SQL & PL/SQL » SQL & PL/SQL » NestedTable
NestedTable [message #343733] Thu, 28 August 2008 00:03 Go to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi,
i am getting the below mentioned error.can anyone please tell me what i am missing in this code?

SQL> /
declare
cursor cur1 is select * from dept;
type type1 is table of dept.dname%type;
type2 type1 := type1();
v_count number := 0;
begin
for i in cur1 loop
v_count := v_count+1;
type2.extend;
type2(v_count) := i.dname;
end loop;
type2.trim(3);
type2.extend;
type2(3) := 'NEW';
--dbms_output.put_line(type2(3));
end;

ORA-06533: Subscript beyond count
ORA-06512: at line 14
SQL> 
Re: NestedTable [message #343735 is a reply to message #343733] Thu, 28 August 2008 00:06 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

which is line 14?
Re: NestedTable [message #343738 is a reply to message #343733] Thu, 28 August 2008 00:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
You have started with 4 rows in your collection. Then you trim 3 of them, leaving 1 row, then you extend 1 row which makes 2 rows, then you try to set the value for the third row that does not exist, hence the error.
Re: NestedTable [message #343740 is a reply to message #343738] Thu, 28 August 2008 00:27 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
ok..thankyou for your reply. But if i give Delete instead of Trim it is working fine.
can you tell me what is difference between DELETE & TRIM used in Nested Table?

[Updated on: Thu, 28 August 2008 00:30]

Report message to a moderator

Re: NestedTable [message #343742 is a reply to message #343740] Thu, 28 August 2008 00:47 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database PL/SQL User's Guide and Reference
Chapter 5 Using PL/SQL Collections and Records
Section Using Collection Methods
Paragraphs "Decreasing the Size of a Collection (TRIM Method)" and "Deleting Collection Elements (DELETE Method)"
TRIM
TRIM(n) removes n elements from the end of a collection.

DELETE
DELETE(n) removes the nth element from an associative array with a numeric key or a nested table.

Read the section, there are other options for these methods.

Regards
Michel
Previous Topic: Parallel query returns wrong results
Next Topic: Related to Select.....Functions
Goto Forum:
  


Current Time: Fri Dec 02 14:12:19 CST 2016

Total time taken to generate the page: 0.12535 seconds