Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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

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
JohnnyGr
Asked:
JohnnyGr
1 Solution
 
pegasysCommented:
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
 
JohnnyGrAuthor Commented:
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
 
pegasysCommented:
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
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!

 
JohnnyGrAuthor Commented:
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
 
pegasysCommented:
0
 
rbagdonasCommented:
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
 
JohnnyGrAuthor Commented:
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
 
CleanupPingCommented:
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
 
GaryCommented:
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
 
Computer101Commented:
PAQed, with points refunded (100)

Computer101
E-E Admin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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