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

[rafalmd]

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!

 

[Dimitri Kurashvili]

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!

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.

 

[rafalmd]

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...

 

[Dimitri Kurashvili]

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...

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