Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to insert data with more than 4000 char in clob datatype in oracle 9 i

Posted on 2009-04-01
7
Medium Priority
?
3,752 Views
Last Modified: 2013-12-12
Hi Experts ..
 I am trying to migrate postgreSQL database to oracle 9i. I have scripts from postgreSQL to create tables and to insert data in to oracle. I have data with more than 4000 chars in a column. For that i used clob data type in oracle. When i am trying to run my script for inserting data it give me an error like.
"The string literal is longer than 4000 characters.
*Action:   Use a string literal of at most 4000 characters.
           Longer values may only be entered using bind variables. "

Can any one help me with this please. thanks in Advance.
0
Comment
Question by:santy08
7 Comments
 
LVL 3

Expert Comment

by:johnsmith1962
ID: 24044613
You can use a stored procedure with a bulk collect.
0
 

Author Comment

by:santy08
ID: 24044798
thanks for the response. I am very new to database. can u give me an example please.
0
 
LVL 11

Expert Comment

by:yuching
ID: 24045667
try this
- TABLEA - oracle
- TABLEB - your table

INSERT into TABLEA (col1)
SELECT utl_raw.cast_to_raw(col1) FROM TABLEB
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:santy08
ID: 24051188
ill try this . Tell me what is this utl_raw.cost_to_raw do? my table is in postgres.. How i can write a select for a table in postgresql in oracle?
0
 
LVL 11

Expert Comment

by:yuching
ID: 24057285
utl_raw.cost_to_raw is a function for oracle. Unfortunately this can be use in postgres database.
How did you insert into oracle database?
Did you export the data in postgres into text and import it into oralce?
or you use a 3rd party tools?
0
 

Author Comment

by:santy08
ID: 24062295
I am Exporting data from postgresql to a text file and then using a .ctl and bat file to lode data using sql * loader
0
 
LVL 1

Accepted Solution

by:
ddas_auth earned 1500 total points
ID: 24062484
I had faced similar problems while migrating data from sybase db to oracle9i. There are some help documents in oracle's metalink.oracle.com site.

However I resorted to migrating the data using java program.
At first fill the clob field using "EMPTY_CLOB()". Then update using java programs as attached below(not a complete code).

String sql = "SELECT clobColumn, recordId FROM clobTable FOR UPDATE";
conn.setAutoComit(false); //make sure autocomit is false
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while( rs.next()){
       CLOB clob = (CLOB)rs.getClob(1);
       Writer clobWriter = clob.setCharacterStream(1L);
       clobWriter.write("data from text file for the recordId");
       clobWriter.close();
}
con.commit();
//make sure you comit

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question