Solved

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

Posted on 2009-04-01
7
3,493 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
 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

 

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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

630 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