File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Java in General and the fly likes Inserting and retrieving LONG data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Inserting and retrieving LONG data " Watch "Inserting and retrieving LONG data " New topic

Inserting and retrieving LONG data

Srihari Injeti
Ranch Hand

Joined: Jan 05, 2001
Posts: 31
I have a requirement to insert data into a column that is defined as LONG. We are using Oracle 7.3.4..
I am unable to insert more than 2000 characters into this table.
To insert this I had to write a stored procedure that did the Insert. A simple insert woudnt work. The data to be inserted is entered using a web form. I also have a problem of retrieving the data. I have to display the data using a java servlet.
Any Suggestions are most welcome.

Srihari Injeti<BR>SCJP2 MCP CIW
marilyn murphy
Ranch Hand

Joined: Aug 28, 2001
Posts: 84
What was the problem with the simple insert?
Ilja Preuss

Joined: Jul 11, 2001
Posts: 14112
Please provide some code and error messages.

The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Jim Yingst

Joined: Jan 30, 2000
Posts: 18671
To get and set values in a ResultSet or PreparedStatement for a column of type LONG (known as LONGVARCHAR in java.sql.Types) the preferred methods are usually getCharacterStream(Reader) and setCharacterStream(Writer). If you're currently using a Statement rather than a PreparedStatement, it may be worthwhile to switch so that you can use the above set method, which avoids the need to create a single huge String object in memory for this field (which may be larger than some database drivers are able to handle). Or maybe your problem is completely unrelated; I'm just guessing here. Good luck...

"I'm not back." - Bill Harding, Twister
Sean MacLean
Ranch Hand

Joined: Nov 07, 2000
Posts: 621
I believe that 2000 char's is the the limit on that version of Oracle. Anything beyond that and you have to use CLOB's or BLOB's in the manner that Jim describes. Research you question using this info and you should find some samples of inserting and retrieving CLOB data.
Srihari Injeti
Ranch Hand

Joined: Jan 05, 2001
Posts: 31
Thanks guys, for your suggestions. The problem with the simple
insert is due to the version of Oracle. Instead of a simple insert , I now use a stored procedure and call it directly from
NetDynamics which is the application server we use. I was earlier trying to use a shell script to call the procedure.
NetDynamics also has a limit some where around 40000 characters.
But my requirement is usually between 10000 to 20000 so I am safe there.

Srihari Injeti
I agree. Here's the link:
subject: Inserting and retrieving LONG data
It's not a secret anymore!