?
Solved

CLOB inserts

Posted on 2003-02-25
8
Medium Priority
?
4,063 Views
Last Modified: 2012-05-04
I need some advice on inserting CLOB data using SQL*Plus.

I have a table with a CLOB, and a sweries of insert statements containing CLOB data. These inserts were obtained from a MySQL data dump when migrating to Oracle. The CLOB data is long - at least 8000 characters.

When executing the insert I get an ORA-01704 error message, saying that my data is too long.

What's the easiest way to get this data into my table ?

I have checked other posts here, but can't find an answer.

Thanks
0
Comment
Question by:caverns
7 Comments
 
LVL 1

Expert Comment

by:hooker042299
ID: 8018234
sql*loader not sql*plus
0
 
LVL 4

Accepted Solution

by:
boriskalavsky earned 100 total points
ID: 8021434
SQL*Plus cannot insert more than 255 characters into a LONG column.

You can use bind variable instead of a string literal.  

ex. PL/SQL script with local variable to store the value first (maximum size of a local variable is 32k)
0
 
LVL 2

Assisted Solution

by:JohnnyGr
JohnnyGr earned 100 total points
ID: 8023348
try a insert into table(clob_field) values(empty_clob())

and then do an update after that on the same field...

update table set clob_field='long_text_here' where ....

that worked for me.. dunno if its the right way to do it tho :)
0
Technology Partners: 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:caverns
ID: 8024079
I am using SQLPlus not SQLLoader.

There are hundreds of insert statements, so I'm looking for the simplest way possible to get this into Oracle. Is using PL/SQL really the only way to do this? If so, will there be a problem trying to pass 6000 characters fro SQLPlus to a PL/SQL procedure as well?

If I try the update idea, then I get messages from sqlplus saying that the string literal is too large.
0
 
LVL 2

Expert Comment

by:JohnnyGr
ID: 8024264
Seems there is a limitation in oracle so you cant insert statements longer than 4000 chars it seems you have to temporarily store the data in a file and stream it into the database.

REF: http://www.oracle.com/forums/thread.jsp?forum=144&thread=164256&message=416364&q=73716c706c757320636c6f62206669656c6473#416364

hope that helps... ive had too much trouble with clob fields so i try to not use them... but sometimes you really dont have a choice...
0
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 8029006
SQL*Plus cannot insert more than 255 characters into a LONG column.

You can use bind variable instead of a string literal.  

ex. PL/SQL script with local variable to store the value first (maximum size of a local variable is 32k)
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9927593
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: JohnnyGr {http:#8024264} & boriskalavsky {http:#8029006}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
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…

621 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