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

R

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!
 
D

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

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

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top