...

View Full Version : execute a stored procedure from java program



guptaneah12
01-24-2008, 08:07 AM
Hi,

I am trying to execute a stored procedure from the java app.

Following is the stored procedure:

create or replace procedure GET_WMS_USER_DETAILS(
P_USER_ID IN VARCHAR2,
P_PASSWORD OUT VARCHAR2,
P_PRIVILEGE OUT VARCHAR2)
IS
BEGIN
SELECT WMS_PWD, WMS_PRIVILEGE
INTO P_PASSWORD, P_PASSWORD
FROM WMS_USER_DETAILS
WHERE WMS_USER_ID=P_USER_ID;
END GET_WMS_USER_DETAILS;



java code to execute the SP is as follows:

private static String READ_USER_DETAILS_SQL = "call GET_WMS_USER_DETAILS(?,?,?)";

initConnection(WMSDBUtil.getCallbackQuery(READ_USER_DETAILS_SQL));
logger.info("INSIDE CHECK() AFTER CALLING initConnection");
addStringValue(1, strUserName);
logger.info("AFTER addStringValue(). value set to IN parameter: " + strUserName);
logger.info("BEFORE REGISTERING OUT PARAMETER");
cstmt.registerOutParameter(2, Types.VARCHAR);
logger.info("BEFORE REGISTERING OUT PARAMETER");
cstmt.registerOutParameter(3, Types.VARCHAR);
logger.info("BEFORE EXECUTING THE CALLABLE STATEMENT");
cstmt.execute();
logger.info("*********** AFTER EXECUTING THE CALLABLE STATEMENT **********");


As per the logs:
I am getting NullPointer exception at statement "cstmt.registerOutParameter(2, Types.VARCHAR);"


please help

sobrien79
01-24-2008, 07:33 PM
Either Types.VARCHAR is null or cstmt. I'm pretty certain the problem lies with cstmt but do a check on the Types.VARCHAR just to rule out the possibility.



if (Types.VARCHAR == null) {
logger.info("Types.VARCHAR is null LALALALALALALALALA");
}
if (cstmt == null) {
logger.info("cstmt is null LALALALALALALALALA");
}


See if you see either of those statements in the log file. If you do, try to track down why either is null.

Note: put that code before your cstmt.registerOutParameter(2, Types.VARCHAR); line.

shyam
01-25-2008, 06:03 AM
private static String READ_USER_DETAILS_SQL = "call GET_WMS_USER_DETAILS(?,?,?)";

initConnection(WMSDBUtil.getCallbackQuery(READ_USER_DETAILS_SQL));
logger.info("INSIDE CHECK() AFTER CALLING initConnection");
addStringValue(1, strUserName);
logger.info("AFTER addStringValue(). value set to IN parameter: " + strUserName);
logger.info("BEFORE REGISTERING OUT PARAMETER");
cstmt.registerOutParameter(2, Types.VARCHAR);
logger.info("BEFORE REGISTERING OUT PARAMETER");
cstmt.registerOutParameter(3, Types.VARCHAR);
logger.info("BEFORE EXECUTING THE CALLABLE STATEMENT");
cstmt.execute();
logger.info("*********** AFTER EXECUTING THE CALLABLE STATEMENT **********");

are u sure cstmt is not null...the initialization code for it is not presented here



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum