Calling pl/sql stored procedures

[LeonTang]

Hi,

I've tried calling pl/sql packages but encountered this error:-

java.lang.NullPointerException
at oracle.jdbc.ttc7.TTCAdapter.newTTCType(TTCAdapter.java:270)
at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCColumnArray(TTCAdapter.java:256)
at oracle.jdbc.ttc7.TTCAdapter.createNonPlsqlTTCDataSet(TTCAdapter.java:231)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1363)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1446)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1371)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1900)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
at JDBCtest.main(JDBCtest.java:29)


This is my source code:-

String abc = new String("abc"
CallableStatement cstmt = myConnection.prepareCall("? = CALL myPackage.getAddress(?)"
cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
cstmt.setString(2, abc);
cstmt.executeUpdate(); // error occured at this line
String address = cstmt.getString(1);

However if I change to this, nothing is wrong:-

CallableStatement cstmt = myConnection.prepareCall("{CALL myPackage.insert_log(?,?,?,?)}"
cstmt.setString(1, "test01"
cstmt.setString(2, "No Module"
cstmt.setString(3, "No Event Code"
cstmt.setString(4, "No Event Description"
cstmt.executeUpdate();


This is my pl/sql procedure:-

FUNCTION getAddress(i_name IN VARCHAR2)
RETURN VARCHAR2
IS
v_address varchar2(30):=null;
BEGIN
SELECT address
INTO v_address
FROM tmy_details
WHERE name=i_name;

RETURN v_address;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'getAddress '||SQLERRM);
END getAddress;

I am sure there is nothing wrong with my pl/sql packages as there are other pl/sql packages calling them but no errors are encountered. I am also sure there is the record in my database so what could be the error?

Any help would be appreciated.

Regards,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best

 

[LittleWing]

NullPointerException on the executeUpdate() implies that either your input param or output is null. try hard coding in the input param and see if this still generates an error. if so, its the output.

i noticed from your other example all the inputs were expressed explicitly and no output was read. i'm pretty sure its the output that is null. let me know if it works!

 

[LeonTang]

Nope, it still doesn't work... the problem is there is this record in the database. I am wondering if my callablestatement is not initialised properly but it doesnt seems so since I don't find anything wrong with the codes...and even if my output was null, I am sure there wouldn't be a null pointer. It is alright for sql packages to return null values.

So what might be causing the error?

Thanks anyway,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best