0

I want to know how to pass large data to Oracle PL/SQL procedure from a Java class. I am having around 1,500,000 records in file and I want to save it in the database.

Currently I am parsing the file making the collection and sending the ARRAY to the PL/SQL procedure but while making the Oracle ARRAY I am getting the OutOfMemoryError: Java heap size exception.

Kindly suggest how to get rid of this situation.

U880D
  • 8,601
  • 6
  • 24
  • 40
user5783725
  • 41
  • 8
  • 4
    Just ... don't try to push it all ot once, but rather split it into 10000-sized chunks? Anyway, if you really _need_ to push it all at once (you don't) and you have enough RAM on the computer, you can simply increase the memory available to the JVM heap by using [-Xmx...](https://stackoverflow.com/questions/14763079/what-are-the-xms-and-xmx-parameters-when-starting-jvms) – Petr Janeček Apr 04 '18 at 09:41
  • 1
    Can't help you with the implementation as I don't work with Oracle databases, but the general principle is that you should probably read one record at a time from the file (obviously using some kind of buffering!) and pass each record to the database for insertion. This will greatly reduce your memory requirement. – Magnus Apr 04 '18 at 09:41

2 Answers2

0

I don't have enough knowledge in Oracle. However you can consider the below idea

You can try to read data line by line from file instead of read at once and then move your record into your database as read.

How to read a large text file line by line using Java?

If you want to move data at once to PL SQL, there should be option in Oracle like select into query to save data as collection and process it further.

https://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems045.htm

Kumar
  • 3,782
  • 4
  • 39
  • 87
  • Actually i am reading line by line only and then making the object and sending it to oracle. But while creating object it throwing the error – user5783725 Apr 04 '18 at 10:34
  • You have to send data to oracle when reading each line. Making object with all data will produce memory issue. So don't store the data into array. Instead send data to oracle after read each line. – Kumar Apr 04 '18 at 10:56
0

Just found the solution of CLOB variable - a Character Large OBject is a collection of character data. I am using the CLOB variable in Java and reading the entire file and inserting it in a temporary table. And from stored procedure I am reading that variable.

Here is the demo:

http://www.idevelopment.info/data/Programming/java/jdbc/LOBS/CLOBFileExample.java

Thanks.

U880D
  • 8,601
  • 6
  • 24
  • 40
user5783725
  • 41
  • 8