Re: passing >4000 VARCHAR2 argument with JDBC to PL/SQL stored functions in 8.1.5

Hello Mattias,
  I didn't look at your links, just interpretted what you said in the text. Oracle is behaving
correctly, the max size of the varchar2 is 4000. It will hold strings up to 4000 characters in length.

When you say no errors occur in sqlplus, did you have serveroutput on? Did you select the column after your supposed successful run in sqlplus?

You need to redefine your table columns to be of a type that will handle strings > 4000 in length.
See CLOB type (or only attempt to insert up to 4000 characters of your string...)

Hope that helps,
Cindy

Mattias Malmgren wrote:

> Hello!
>
> I have listed a few short programs here
> http://mattias.kontakt.slu.se/f/
> and this html-page shows the output
> http://mattias.kontakt.slu.se/f/res.html
>
> I can pass strings >4000 characters as an argument to a stored
> function from a Java-program using oracle JDBC-thin driver
> when I am using the oracle 8.0.4 system. Exactly the same
> code givs an error
> java.sql.SQLException: Data size bigger than max size for this type
> on my new 8.1.5 system. However, if the stored function is called from
> another PL/SQL program inside sqlplus there is no errors, even on the
> 8.1.5 system.
>
> I have talked to Oracle support here in sweden. They claims that the
> 8.1.5 system is working correctly and have no comment to that it
> does work on 8.0.4. This is ofcourse very serious for me becous the
> application that I am responisbel fore make heavy use of >4000 strings
> passed to stored functions. Must I revrite my java code to make my
> applications work on 8.1.5?
>
> I guess that the Oracle JDBC people simply have missunderstood how
> the varchar2 variable works. It can be 32 kb in pl/sql, but onely 4 kb
> as a field in a table. I guess the code in 815 driver looks something
> like this:
>
> int MAX=4000;
> if (arg.length()>MAX) {
> throw new SQLException("Data size bigger than max size for this
> type");
> return null;
> }
>
> MAX shoud be 32767.
>
> I need a fix! Or is it me that have missundersood everything?
>
> Regards / Mattias
Received on Thu Sep 09 1999 - 11:53:34 CDT