Re: CLOBS work with thin driver?

Looks like you hit the send button 3 times!

Terry's post made me go back to documentation on Oracle's thin driver. And it's true Oracle does have APIs to handle LOBs in type IV driver, and there is a 4K limit. My understanding is this limitation is only for direct insert. The workaround is to initialize the LOB first, then open a stream to its locator. You can find sample code in chapter 8 of the JDBC Developer's Guide. If this doesn't meet your needs you may have to talk to Oracle support.

"Chris Gokey" <cgokey_at_gcmd.nasa.gov> wrote in message news:<pan.2003.02.15.13.19.37.158629_at_gcmd.nasa.gov>...
> Unfortunately we need to use a Type 4 driver, so OCI will not work.
> So is there no other datatype in Oracle to store text (< 100,000
> characters) besided LONG? I'm not storing huge amounts of text,
> usually < 30,000 characters, I think. So, performance would
> probably be sufficient in a Type 4 driver, I just can use
> VARCHAR2 because of its limitations (it won't accept more than 4000
> characters I think).
>
> Thanks for your response.
> Chris
>
>
> On Fri, 14 Feb 2003 20:32:37 +0000, D.Y. wrote:
>
> > I am not sure if Oracle implemented LOB APIs in the Thin Driver in 9i. If I
> > have to guess I'd say no because performance would have been horrible.
> >
> > You can always access LOBs using the JDBC OCI driver. You would have to create
> > a separate, dedicated connection for the LOB streams. There are some good code
> > examples in JDBC Developer's Guide.
> >
> >
> > "Chris Gokey" <cgokey_at_gcmd.nasa.gov> wrote in message news:<pan.2003.02.14.23.26.56.253111_at_gcmd.nasa.gov>...
> >> Hi everyone,
> >>
> >> Here is my specs to give some context:
> >>
> >> Oracle 9.2.0
> >> Redhat 8.0
> >> java version "1.4.1_01"
> >> JDBC Thin Driver (ojdbc.jar, latest off the web site)
> >>
> >> I tried storing/retrieving data from Oracle using CLOBs and it looks like
> >> this will not work.  Attached is a short example of storing data
> >> to a CLOB and later retrieving it.  Here is the sample table that I
> >> created:
> >>
> >> create table test (summary CLOB);
> >>
> >> And here is the error I received:
> >>
> >> ----------------
> >>
> >> [cgokey_at_cgokey cgokey]$ java md.clients.Test Exception in thread "main"
> >> java.sql.SQLException: Io exception: Connection reset        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:333)
> >>         at
> >> oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2061)
> >>         at
> >> oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
> >>         at
> >> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
> >>         at
> >> oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
> >>         at md.clients.Test.main(Test.java:33)
> >>
> >> ----------------
> >>
> >> I'm trying to store the CLOB using:
> >> st.setCharacterStream(1,new StringReader(summary),summary.length());
> >> st.executeUpdate();
> >>
> >> I think this is the right way to do it. I've been told it is also
> >> possible to do this with stored procedures, but I'd like to keep my code
> >> as database independant as possible and use the method above or some
> >> slight variation where I don't have to cast to Oracle classes.
> >>
> >> I read somewhere the CLOBS are supported now under the latest Oracle thin
> >> driver, am I doing something wrong?
> >>
> >> Is there any other data type in Oracle that can store text (<100,000
> >> characters) besides CLOB and LONG. The LONG datatype will not work
> >> for me because I have a table with more than one column with LONG.
> >>
> >> Any help would be greatly appreciated :)
> >>
> >> Chris
Received on Sun Feb 16 2003 - 09:47:23 CST