• 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

handling CLOB from java

 
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I need to write a simple program to fetch a column data which is clob (oracle 9.2)
and insert/modify it and save it back to database.

I tried to use java.sql.Clob which has a setString function but that gives me

**************************************
java.sql.SQLException: Unsupported feature
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.dbaccess.DBError.throwUnsupportedFeatureSqlException(DBError.java:689)
at oracle.sql.CLOB.setString(CLOB.java:1148)

Please help me with this, I need this working urgently.
I also came to know that I can get a clob as a String and save back a String as a clob but that is not working in oracle 9.2.
I think that only works with oracle 10g

Regards
 
Ranch Hand
Posts: 167
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I did something like this


If i remember correctly, we used 9x version of Oracle

Jhakda
 
Ishita Saha
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It gives me following error:

ORA-22920: row containing the LOB value is not locked
 
Jhakda Velu
Ranch Hand
Posts: 167
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you post your code?
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
A solution is discussed here: http://forums.oracle.com/forums/thread.jspa?messageID=2146346

This person solved the issue by using select for update.
 
Jhakda Velu
Ranch Hand
Posts: 167
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you see the snippet that i posted, it is also selecting the clob/blob initially and then updating it.
 
Ishita Saha
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried it other way and it worked:



I found it from my collegue
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jhakda Velu wrote:If you see the snippet that i posted, it is also selecting the clob/blob initially and then updating it.

Jhakda, "select fort update" does not mean that you first select, and then update. It is an SQL clause that you use, in your select statement, to tell the database that you are going to change it.

Glad you found a solution.
 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think I found the answer to this.

1) Consult this link for background info http://download.oracle.com/docs/cd/B12037_01/java.101/b10979/jdbcversion.htm

It talks about how some features were available in JDBC 3.0 and some were not.

In general though my problem was solved by:
A) Putting (what I think was) a more update-to-date version of ojdbc14.jar in Tomcat under common/lib folder. ( (I pulled this from Weblogic 8.1 server/lib folder)
B) Put a library reference to the exact same jar in my eclipse build path.
C) Do a clean and recompile of the your class/project
4) Utilize the JDBC methods instead of the proprietary Oracle methods when performing a "select for update".

Writer w = null;
w = yourFetchedClob.setCharacterStream(1);
w.write(yourLargeString);
w.flush();
w.close();

I'm betting it was an old ojdbc14.jar that did not have Clob write feature implemented.


reply
    Bookmark Topic Watch Topic
  • New Topic