Solved

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

Posted on 2009-04-01
7
3,372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

739 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