?
Solved

CLOB inserts

Posted on 2003-02-25
8
Medium Priority
?
4,054 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
[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
8 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

764 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