Solved

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

Posted on 2009-04-01
7
3,180 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

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…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

785 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