Solved

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

Posted on 2009-04-01
7
3,041 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now