[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3974
  • Last Modified:

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

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
santy08
Asked:
santy08
1 Solution
 
johnsmith1962Commented:
You can use a stored procedure with a bulk collect.
0
 
santy08Author Commented:
thanks for the response. I am very new to database. can u give me an example please.
0
 
yuchingCommented:
try this
- TABLEA - oracle
- TABLEB - your table

INSERT into TABLEA (col1)
SELECT utl_raw.cast_to_raw(col1) FROM TABLEB
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
santy08Author Commented:
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
 
yuchingCommented:
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
 
santy08Author Commented:
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
 
ddas_authCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now