Import Oracle table dump with insert statements and clob data types using Java

Question

I have an Oracle table dump containing insert statements. Some of the tables contain clob data fields. If I try to restore the dump using SQL Developer I get the error "ORA-01704: string literal too long"

Now I tried to restore the dump with JDBC using Java. The problem is that I already have the fully composed PreparedStatement so I cannot change the datatype of the columns from string to clob.

In the following the application is listed:

// strBufLine contains already the fully composed insert statement from the dump file
PreparedStatement pstmt = connection.prepareStatement("BEGIN " + strBufLine.toString() + " END;");
pstmt.executeUpdate();
pstmt.close();

Any idea how to solve this issue (using another program to restore the dump, other dump technique or how to change the datatype to clob)

Codo Codo · Accepted Answer · 2012-07-14T17:39:41

If the string literal is too long for SQL Developer, it will be too long for JDBC as well because it's parsed on the server side.

I propose you convert the file into a CSV or fixed length file. That shouldn't be too difficult since it's the dump of a single table. So each line will have a similar structure. It might even be possible to do it in a text editor with Search/Replace.

Then you can import it using SQL Developer (Import Data... in the context menu on the desired table).

Import Oracle table dump with insert statements and clob data types using Java

1 Answers