Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06533 While returning collection from function
ORA-06533 While returning collection from function [message #268578] Wed, 19 September 2007 02:19 Go to next message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
Why am I getting error ORA-06533 when I try to return a collection from this function.

File containing all the scripts is attached.

Thanks!
Re: ORA-06533 While returning collection from function [message #268580 is a reply to message #268578] Wed, 19 September 2007 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-06533: 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.

You delete l_bus_audit_data items and then set item 1 without extending the array.

Regards
Michel
Re: ORA-06533 While returning collection from function [message #268626 is a reply to message #268578] Wed, 19 September 2007 03:37 Go to previous messageGo to next message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
..."without extending the array"...

How will I extend the array?

I do NOT know in advance how many items will be in the array.
Re: ORA-06533 While returning collection from function [message #268634 is a reply to message #268626] Wed, 19 September 2007 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"l_bus_audit_data.extend" each time you want to add an element.

Regards
Michel
Re: ORA-06533 While returning collection from function [message #268644 is a reply to message #268578] Wed, 19 September 2007 04:42 Go to previous messageGo to next message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
Michel,

Thanks very much for all your help!


I have changed this piece of the code now to look like this:

l_bus_audit_data.DELETE(l_cnt_attrib);
l_bus_audit_data.EXTEND;
l_bus_audit_data(l_cnt_attrib).business_attribute := 'test attrib no = ';


But now I keep on getting error ORA0-06530 again...

Do I need to initialize the array? How do I do that?

Thanks!
Re: ORA-06533 While returning collection from function [message #268651 is a reply to message #268644] Wed, 19 September 2007 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not what I said.
You changed the delete statement, you changed the set statement, so the allocation changes.
Please read the PL/SQL documentation about collection usage.

Regards
Michel
Re: ORA-06533 While returning collection from function [message #268726 is a reply to message #268578] Wed, 19 September 2007 08:33 Go to previous messageGo to next message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
Michel,

I've tried everything under the sun now after reading up on this, but I'm still getting the ORA-06530 error. I have also added what I think is an initialize statement for the type....

I'm attaching my latest scripts so if you have a minute, please can you try to help me?

Thanks!
Re: ORA-06533 While returning collection from function [message #268754 is a reply to message #268726] Wed, 19 September 2007 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try just "select fn_business_audit_record.... from dual".
What do you get?

Regards
Michel
Re: ORA-06533 While returning collection from function [message #268902 is a reply to message #268578] Thu, 20 September 2007 01:28 Go to previous messageGo to next message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
Michel,

I did actually try that yesterday and it still gives me the same error...
Re: ORA-06533 While returning collection from function [message #268909 is a reply to message #268902] Thu, 20 September 2007 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste the execution as well as your Oracle version with 4 decimals.

Regards
Michel
Re: ORA-06533 While returning collection from function [message #268923 is a reply to message #268578] Thu, 20 September 2007 02:08 Go to previous message
sayuri
Messages: 20
Registered: October 2006
Location: Cape Town
Junior Member
Michel,

I found the error thanks to my lovely (and brand new) Oracle 10g PL/SQL Manual!

The table populate statement must look like this:

l_bus_audit_data(l_cnt_attrib) := msa_audit.t_bus_audit_rec_data(r_attributes.business_attr,
SYSDATE, 'test creator', 'A', 'old val', 'new val');



Thanks for your help !
Previous Topic: ora-01722 when using to_number (merged)
Next Topic: Insert into CLOB and select
Goto Forum:
  


Current Time: Tue Dec 06 08:32:28 CST 2016

Total time taken to generate the page: 0.14172 seconds