Skip to Main Content
  • Questions

  • Dealing with NULL parameters in a PL/SQL procedure(or fucntion) called by a CallableStatement in JDBC 2.0

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Krishna.

Asked: March 01, 2002 - 9:55 pm UTC

Last updated: August 31, 2004 - 3:09 pm UTC

Version: 9.1.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Could you please let me know how to deal with NULL parameters in a PL/SQL procedure (or function) that is called by using a CallableStatement in JDBC?

For example, a procedure has 4 IN parameters - Param1, Param2, Param3, and Param4. Values are available for Param1 and Param2. Param3 and Param4 have NULL values. Param3 has a SQL datatype of NUMBER and Param4 has a SQL datatype of Varchar2.

Thank you very much.

Krishna

and Tom said...

import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

class Callable
{

static public void main(String args[])throws SQLException
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Connection conn= DriverManager.getConnection
("jdbc:oracle:thin:@aria-dev:1521:ora817dev",
"scott","tiger");


CallableStatement cstmt = conn.prepareCall
("begin proc(?,?,?,?); end;");

cstmt.setString( 1, "parm1" );
cstmt.setString( 2, "parm2" );
cstmt.setNull( 3, java.sql.Types.NUMERIC );
cstmt.setNull( 4, java.sql.Types.VARCHAR );

cstmt.executeQuery();

cstmt.close();
conn.close();
}
}

is one way to do it.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

is there any other way to do it from java ?

Dee, August 31, 2004 - 11:03 am UTC

is there a way to do it using java.sql.Types.NULL instead ?

I tried to use
cStmt.setNull(3, java.sql.Types.NULL); and also
cStmt.setObject(3, null, java.sql.Types.NULL);

but both throw an "Invalid Column type SQLException"
it seems oracle needs to know the type so it can deal with
the null(unknown). pls. advise if there is an alternative


Thanks


Tom Kyte
August 31, 2004 - 1:21 pm UTC

just use varchar i guess?

or the real type, why cannot you do that? use the *real* type?

in case of java null, i do not know the real type

Dee, August 31, 2004 - 2:57 pm UTC

i m working on a middle layer - its a wrapper around jdbc
it determines the sql type from the real java type passed to it and uses prepStmt.setObject() always..

Actually, varchar would work but temporarily...because as of now that is the only object(String) type that can be null but i am afraid, it may not cover all cases..

As of now, i force the user of the wrapper to pass the 'Class' and i use that to determine the mapping
however, i wonder if there is a better solution


Thanks

Tom Kyte
August 31, 2004 - 3:09 pm UTC

oh good, more generic code on top of generic code on top of generic code :)

just wondering -- how would this wrapper class be

a) better
b) easier
c) anything

than just

preparestmt
bind
bind
bind
execute

?

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library