crazy usage with association table cause ORA-06500: PL/SQL: storage error [message #395638] |
Thu, 02 April 2009 02:28  |
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 482 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 #395659 is a reply to message #395640] |
Thu, 02 April 2009 03:09   |
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.
|
|
|
|