• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to create BLOB and CLOB data

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi friends
can you please help me..

how store BLOB data in Oracle database and then how to get those data to my java program....
i am unable to store these type of data in db....

please
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Madhusudan,
Welcome to JavaRanch!

What code do you have so far? What error message is it giving>

I usually use setBinaryStream for BLOBs on a prepared statement to update.
 
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I usually use setBinaryStream for BLOBs



And can we use StringBuffer data to writing data to a CLOB .

 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In my opinion the hard part is creating the blob/clob and getting its handle. If you are at least on Oracle 10.2 and habe Oracle 10g JDBC Driver, you could try this. (I've devised this technique from the Oracle JDBC Guide, which I strongly suggest for reading.)

Let's have a table:

Inserting a blob then could look like:

I have just taken part of my code and tried to remove my specific logic. If it doesn't compile or work, I'll try to revisit it. A few additional notes:

The technique described here works by creating an empty blob in the database "in the right place" and returning its handle. Although you can create a blob using other means, these techniques often cause a temporary blob to be created, which is then copied when you actually insert or update it. For really, really large data this overhead might be noticeable.

Once you have the Blob, you can set its data using any of its methods you like or need, eg. using the setBinaryStream method, as mentioned above.

Remember to process transactions correctly. I'd definitely advice to turn off autocommit and commit the transaction manually, I honestly don't know what would happen to the blob handle if the insert statement got commited. However, the transactional logic would be clearly broken if you allowed autocommit to happen.

If you want to update a blob, you can either update its field to an empty_blob() value as shown here ("update mytable set data=empty_blob() where id=? returning data into ?"), or just read the blob handle (select data from mytable where id=?) without the need for Oracle-specific methods.

I think you could hide the SQL statement with the returning into clause into a stored procedure which would return you the Blob and then use standard callable statement to get the handle, thus avoiding the need for Oracle-specific methods. However I personally haven't tried this. Maybe this way it could be made somehow database independent, though I'm definitely not sure of it.

You can bind clob data directly as Strings, using PreparedStatement.setString() method. Assuming the data field was clob, not blob, you could use:

The default String length limit for this is 32 KB; to increase the limit, see http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html.


 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Martin Vajsar wrote:If you want to update a blob, you can either update its field to an empty_blob() value as shown here ("update mytable set data=empty_blob() where id=? returning data into ?"), or just read the blob handle (select data from mytable where id=?) without the need for Oracle-specific methods.


I need to correct myself: for the second way to work, the row would need to be locked in Oracle: select data from mytable where id=? for update. I've posted link to this post on this forum recently, so I feel I should correct this mistake.
reply
    Bookmark Topic Watch Topic
  • New Topic