Home » SQL & PL/SQL » SQL & PL/SQL » crazy usage with association table cause ORA-06500: PL/SQL: storage error (Oracle 10g)
crazy usage with association table cause ORA-06500: PL/SQL: storage error [message #395638] Thu, 02 April 2009 02:28 Go to next message
csfreebird
Messages: 5
Registered: April 2009
Location: shanghai,china
Junior Member
Hi,every one:
A programmer in our company wrote a pl/sql application.It reads two large csv files and handles it.Our oralce 10g always reports exception when load these files in memory.
The exception is ORA-06500: PL/SQL: storage error.I search the error code in google and find the following explanation´╝Ü
PL/SQL runs out of memory or memory has been corrupted.
I found the codes are very crazy,the data structure are very complex.The additional image file called 1.png describes the data structure.

TYPE ARRAY_VARCHAR2 IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
TYPE ARRAY_BUKKEN_JOHO IS TABLE OF ARRAY_VARCHAR2 INDEX BY BINARY_INTEGER;
TYPE REC_RECORD_JOHO IS RECORD (
rowno NUMBER,
data ARRAY_VARCHAR2
);

TYPE ARRAY_RECORD_JOHO IS TABLE OF REC_RECORD_JOHO INDEX BY BINARY_INTEGER;
TYPE ARRAY_KEIYAKU_NO_1 IS TABLE OF ARRAY_RECORD_JOHO INDEX BY VARCHAR2(32767);

TYPE ARRAY_CUSTOMER_CD_1 IS TABLE OF ARRAY_KEIYAKU_NO_1 INDEX BY VARCHAR2(32767);

GV_keiyaku_key ARRAY_CUSTOMER_CD_1;

GV_keiyaku_key_chofuku ARRAY_CUSTOMER_CD_1;

TYPE ARRAY_BUKKEN_NO_2 IS TABLE OF ARRAY_RECORD_JOHO INDEX BY VARCHAR2(32767);

TYPE ARRAY_KEIYAKU_NO_2 IS TABLE OF ARRAY_BUKKEN_NO_2 INDEX BY VARCHAR2(32767);

TYPE ARRAY_CUSTOMER_CD_2 IS TABLE OF ARRAY_KEIYAKU_NO_2 INDEX BY VARCHAR2(32767);

GV_bukken_key ARRAY_CUSTOMER_CD_2;

GV_bukken_key_chofuku ARRAY_CUSTOMER_CD_2;

I guessed oracle implemented the index of table by using hash table,oracle uses a lot of memory for imporve the performance.When my programmer used multidimensional(five-dimentional) array in pl/sql for storing huge data,Oralce can't support this crazy usage.

Who can tell me some internal about this.I don't think these codes is good,but I want some information to tell my crazy programmer.
  • Attachment: 1.png
    (Size: 26.80KB, Downloaded 117 times)

[Updated on: Thu, 02 April 2009 02:33]

Report message to a moderator

Re: crazy usage with association table cause ORA-06500: PL/SQL: storage error [message #395640 is a reply to message #395638] Thu, 02 April 2009 02:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This really looks like someone who started out and got lost along the way. (Unless there is some deep logic behind 5 dimensional arrays to read in a simple csv.)

[Edit: typo]

[Updated on: Thu, 02 April 2009 02:36]

Report message to a moderator

Re: crazy usage with association table cause ORA-06500: PL/SQL: storage error [message #395659 is a reply to message #395640] Thu, 02 April 2009 03:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can implement a 5-d array using an index by Varchar2 pl/sql table.

All you have to do is to use an index key of the form 'a,b,c,d,e,f' - ie a comma delimited list of the 5 dimensional indices.

The data structure you're dealing with looks like a nightmare - I can't think of a single good reason for doing this unless your CSVs implement some sort of complex master-detail process, and even then I wouldn't do it this way.
Re: crazy usage with association table cause ORA-06500: PL/SQL: storage error [message #396210 is a reply to message #395659] Sun, 05 April 2009 00:37 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
why not go back to the original developer and ask them to explain the program? They might have had a reason for how it is designed (I for one would sure like to hear it).

Kevin
Previous Topic: How to work on a table type in a procedure
Next Topic: dynamic update of columns based on rows of other columns
Goto Forum:
  


Current Time: Fri Dec 02 20:37:32 CST 2016

Total time taken to generate the page: 0.08004 seconds