Passing a null value to a stored procedure from Java (DBMS_SQL.Varchar2

Passing a null value to a stored procedure from Java (DBMS_SQL.Varchar2_Table)

Thread view:

rafalmd@gmail.com - 09 Mar 2007 09:40 GMT

Hi,

Can anyone tell me how to pass a null value as IN parameter to a PL/
SQL stored procedure?

E.g:

PROCEDURE sendMessage(p_tuIds IN DBMS_SQL.Varchar2_Table) (...)

It works well with filled array using OracleCallableStatement:

cs.setPlsqlIndexTable (index, values, maxLen, currentLen, elemSqlType,
elemMaxLen);

Question is - what method should I use to pass an empty (or null)
table?

Thanks in advance for your help!

Reply to this Message

Dimitri Kurashvili - 09 Mar 2007 09:46 GMT

On Mar 9, 9:40 am, rafa...@gmail.com wrote:

> Hi,
>
[quoted text clipped - 14 lines]
>
> Thanks in advance for your help!

you need to call setNull of PreparedStatement class (and extensions)
and define index of the parameter and it's type according to
java.sql.Types interface.

Reply to this Message

rafalmd@gmail.com - 09 Mar 2007 10:08 GMT

Yup - I'm aware of this.

But...
What OracleType or java.sql.Type shoud I use for
DBMS_SQL.Varchar2_Table?

and should I use?
cs.setNull(index, sqlType)
or
cs.setNull(index, sqlType, String typeName) - what is my typeName?

Moreover - I tried to use those methods at first place but the give me
nothing but exceptions :/

None of following works:
cs.setNull(index, OracleTypes.ARRAY);
cs.setNull(index, OracleTypes.JAVA_OBJECT);
cs.setNull(index, OracleTypes.JAVA_STRUCT);

Any more precise help would be appreciated...

> On Mar 9, 9:40 am, rafa...@gmail.com wrote:
>
[quoted text clipped - 20 lines]
> and define index of the parameter and it's type according to
> java.sql.Types interface.

Reply to this Message

Dimitri Kurashvili - 09 Mar 2007 10:40 GMT

On Mar 9, 10:08 am, rafa...@gmail.com wrote:

> Yup - I'm aware of this.
>
[quoted text clipped - 41 lines]
> > and define index of the parameter and it's type according to
> > java.sql.Types interface.

i'm not sure but maybe java.sql.Types.OTHER type?

Reply to this Message

Passing a null value to a stored procedure from Java (DBMS_SQL.Varchar2_Table)

Free Magazines