?
Solved

Insert string with 4500+ chars into an oracle 9i database

Posted on 2003-02-24
10
Medium Priority
?
313 Views
Last Modified: 2008-02-01
Trying to insert a string thats over 4500 chars but i cant get it to work, i tryed varchar2 datatype wich seemed to only be able to store 4000 chars... and CLOB thats supposed to be able to store 4GB of data... but i still get [Oracle][ODBC][Ora]ORA-01704: string too long

method in the form is POST so that works good...

anyone have any idea on what to do? i dont want to split the string up in 2 parts since im doing text searches of the field.
0
Comment
Question by:JohnnyGr
[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
10 Comments
 
LVL 7

Expert Comment

by:pegasys
ID: 8007228
You're using the wrong datatype :)

Take alook at this:

http://members.tripod.com/mdameryk/OrclDataTypes.htm

in regards to oracle I wouldnt know the 1st thing abuot it, so reaching above 4000? I dunno :(

regards

pgx

0
 
LVL 2

Author Comment

by:JohnnyGr
ID: 8007993
Well cant find any datatype other than clob that takes that amount of data except long... but what i understand longs is kindof limited...
0
 
LVL 7

Expert Comment

by:pegasys
ID: 8008138
I know in SQL server that blob is used for pretty long stings (like the one you mentioned). SO maybe clob is the same thing?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Author Comment

by:JohnnyGr
ID: 8008176
yeah well the code worked fine in MSsql server, i inserted like 6000+ chars into varchar fields there... but since im migrating the whole system to oracle it seems odd that it cant handle it... seems clob has to be initialized or something... but there has to be a better way...
0
 
LVL 7

Expert Comment

by:pegasys
ID: 8008410
0
 
LVL 2

Expert Comment

by:rbagdonas
ID: 8011309
A CLOB will be the correct data type for you to use.  Varchar2 is 4000 in limitation.  And if you can squeeze it into there you will be a lot happier.

But there are special rules to using CLOBs:

You have to use a set of DBMS function calls to manipulate the data.  It is designed to store paragraphs of data.

Go to http://otn.oracle.com.  Registration is free, but then search for CLOB and you will find out the particulars.

R
0
 
LVL 2

Author Comment

by:JohnnyGr
ID: 8014676
got it now... the sollution was to initialize the clob field first using the empty_clob() function:

insert into table values(empty_clob())

and then loading that record into a recordset and doing a update...

--------------------- Snip -------------------

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=test;UID=xxx;PASSWORD=xxx;"

conn.beginTrans

set rs = server.createObject("adodb.recordset")

rs.open "SELECT * FROM table where id = 1, conn, adOpenStatic, adLockOptimistic

rs.fields("clob_field").appendChunk(veryLongText)

rs.update

conn.commitTrans

--------------------- Snip -------------------

Hope that will help anyone else having a problem with clob fields... i guess that would work with Blob fields too... but then you need to use the empty_blob() function instead.
0
 

Expert Comment

by:CleanupPing
ID: 9576926
JohnnyGr:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 58

Expert Comment

by:Gary
ID: 9754025
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ'd and pts refunded

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
GaryC123
EE Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 9803936
PAQed, with points refunded (100)

Computer101
E-E Admin
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

770 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