This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes difficulty in inserting value into oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "difficulty in inserting value into oracle "long" datatype column" Watch "difficulty in inserting value into oracle "long" datatype column" New topic
Author

difficulty in inserting value into oracle "long" datatype column

Tapas Chand
Greenhorn

Joined: Dec 29, 2009
Posts: 23
i am having problem with inserting values into "long" datatype column in oracle through java
i am doing this

where bai is ByteArrayInputStream
when length of bai is less than 4096, data is inserted properly, but when it exceeds 4095 then
im getting error


please help
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Try to convert long columns to LOB (CLOB, BLOB, NLOB) - Oracle does not recommend using LONG.
LONG datatype is provided only for backward compability and has many limitations.
See here (section "LONG datatype") for details:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#i46018

This could be possible reason of your troubles:
If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Hi Tapas,

As Ireneusz said long data type is not supported since Oracle 8, use lobs instead of long type.

But,

Also with long it should be working. There may be a problem with your "bai" stream. Or may be a driver problem.

This is my test:

Oracle 11g R1 database (NLS_CHARACTERSET AL32UTF8)


Java 1.5
ojdbc14dms.jar


As you can see, I can use setString method to insert values up to 32767 into a long column.

For checking results, I create a mirror table as long column converted to clob to see length etc.


And results:


Regards,

Fatih.
Tapas Chand
Greenhorn

Joined: Dec 29, 2009
Posts: 23
many many thanks for your precious instructions
you mentioned you are using
Oracle 11g R1 database (NLS_CHARACTERSET AL32UTF8), Java 1.5 and ojdbc14dms.jar

and im using Oracle 9i enterprize edition release 9.2.0.5.0, Java 1.5 and ojdbc14_g.jar
so may be im facing the problem because of oracle9i or ojdbc14_g.jar

and you are inserting 32000 bytes of data by "setString method"
but when im using

to insert into the "long" column, im getting an error


if you got any clue on this, please mention

thanks and regards
tapas chand
Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
Hi Tapas,

Your driver is supported, you can see it here.
But you should really think of migrating to CLOB/BLOB data types.
Take a look at this:
What is the longest value I can bind?

Regards,

Fatih.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: difficulty in inserting value into oracle "long" datatype column