Re: Passing long string to Oracle through JDBC?

In article <7E1s4.30161$3b6.143138_at_ozemail.com.au>,   "ak" <a.klammer_at_austrac.gov.au> wrote:
> We have a number of tables that contain CLOB columns. Our new
application,
> consisting of JSP modules using JDBC within beans, needs to retrieve
and
> write these CLOB text strings. The beans interface with packaged
> procedures, so no SQL is executed directly by the beans. Each screen
field
> maps to a parameter in the called procedure.
>
> When I try to push more than 4000 chars down from the screen into
Oracle, I
> get this error:
>
> java.sql.SQLException: Data size bigger than max size for this type
>
> As far as I can understand, the Java side can handle big strings fine.
> According to the JDBC doco, the Oracle and JDBC side of things should
also
> understand them OK as well.
>
> We have explored writing a separate bean to handle the CLOBs where
the bean
> directly reads and writes the CLOB, and, going by the LobExample.java
sample
> supplied with JDBC, it would work fine, but our transactions are
supposed to
> be wholy contained within the packaged procedure that the bean
calls. Using
> a separate bean would mean that we would make two separate
transactions per
> record - the first to (say) update all non-CLOB columns, the second to
> update the CLOB column. This is not really a good solution.
>
> How can I pass a string > 4000 chars into an Oracle packaged
procedure using
> JDBC? Or, more broadly, how else could I achieve our needs?
>
> Please reply to my email address as well, as I don't have continuous
access
> to news.
>
> Thanx very much
>
> -a
>
>

One way is to move your "commit" out of Oracle package to your "java" code. The package code will return "success" or "fail" code. No matter how many times you call package to insert partial string, you will only commit after all calls return "success", otherwise "rollback".

Guang

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Feb 21 2000 - 09:46:49 CST