SQLException Cursor Closed

[Srilakshmi Vara]

Hi All,

I am executing a callable statement had multiple cursors as OUT parameters.
After executing the callable statement i cannot able to get cursors as resultsets

And i am getting error java.sql.SQLException: Cursor is closed.
at ResultSet rs = (ResultSet)callStmt.getObject(5); this line.

Can i get multiple result sets from a callable statement?

Any suggestions please.

Thanks,
Srilakshmi

[Makarand Parab]

Hi
You can surely get multiple resultset from prepared statement. Just see that you are using different ResultSet variable name for each output.
Like
ResultSet rs = (ResultSet)callStmt.getObject(4);
ResultSet rs1 = (ResultSet)callStmt.getObject(5);
And then operate on each of them once.

also check the sql statement which is returning you the cursor. i hope ur not closing the cursor inside that sql statement. I am not sure abt the last line i have just stated but u need to check on db side also.

Let me know if you are still facing the same problem.

Regards
Makarand Parab

[Srilakshmi Vara]

Thanks Makarand,

My procedure is

CREATE OR REPLACE PACKAGE BODY PK_ACD_REPORTS
AS

PROCEDURE GENERATE_TOURLOG (
p_CALLDATEIN VARCHAR2,
p_TOURNUMBERINNUMBER,
p_CALLDATAOUTrep_cursor,
p_USERDATAOUT rep_cursor,
p_COMMENTDATAOUT rep_cursor
) AS
call_date DATE;
prev_call_date DATE;
tourFromDateTime VARCHAR2(17); -- Format is MM/DD/YYYY HH24:MI:SS
tourToDateTime VARCHAR2(17);
BEGIN
call_date := TO_DATE(p_CALLDATE,'mm/dd/yyyy');
prev_call_date := call_date - 1;
IF p_TOURNUMBER = 1 THEN
tourFromDateTime := TO_CHAR(prev_call_date,'MM/DD/YYYY') ||
' ' || '22:00:00';
tourToDateTime := TO_CHAR(call_date,'MM/DD/YYYY') ||
' ' || '05:59:59';
ELSIF p_TOURNUMBER = 2 THEN
tourFromDateTime := TO_CHAR(call_date,'MM/DD/YYYY') ||
' ' || '06:00:00';
tourToDateTime := TO_CHAR(call_date,'MM/DD/YYYY') ||
' ' || '13:59:59';
ELSIF p_TOURNUMBER = 3 THEN
tourFromDateTime := TO_CHAR(prev_call_date,'MM/DD/YYYY') ||
' ' || '14:00:00';
tourToDateTime := TO_CHAR(call_date,'MM/DD/YYYY') ||
' ' || '21:59:59';
END IF;
OPEN p_CALLDATA for
select CALL_ID,
TO_CHAR(CALL_DATETIME,'HH24:MI'),
CAD_NUM,
UNIT_ID,
METHOD,
LL_NUM,
BIOTEL_CH_NUM,
REASON,
CAD_FINAL_TYPE
FROM CTS_CALL_HIST
WHERE TO_CHAR(CALL_DATETIME,'MM/DD/YYYY HH24:MI:SS')
BETWEEN tourFromDateTime AND tourToDateTime
AND STATUS_ID='COMP'
ORDER BY TO_CHAR(CALL_DATETIME,'HH24:MI');

OPEN p_USERDATA for
select distinct call.USER_ID,
pk_cts.SF_USERNAME(usr.USER_LNAME,usr.USER_FNAME,usr.USER_MNAME),
rol.ROLE_NAME
from cts_call_hist call, cts_user usr, cts_sec_role rol
where TO_CHAR(call.CALL_DATETIME,'MM/DD/YYYY HH24:MI:SS')
BETWEEN tourFromDateTime AND tourToDateTime
and call.USER_ID = usr.USER_ID
and usr.ROLE_ID = rol.ROLE_ID;
OPEN p_COMMENTDATA for
Select TO_CHAR(DATETIME,'HH24:MI'),
COMMENTS
FROM CTS_TOUR_COMMENT
WHERE TO_CHAR(DATETIME,'MM/DD/YYYY HH24:MI:SS')
BETWEEN tourFromDateTime AND tourToDateTime
ORDER BY TO_CHAR(DATETIME,'HH24:MI');
EXCEPTION
WHEN NO_DATA_FOUND THEN Null;
WHEN OTHERS THEN Null;
END GENERATE_TOURLOG;

END PK_CTS_REPORTS;


Java Program which is calling the procedure is ...

Connection con = dbconn.getDBConnection();
String query="{ call PK_ACD_REPORTS.GENERATE_TOURLOG(?,?,?,?,?) }";
CallableStatement callStmt = con.prepareCall(query);
callStmt.setString(1,"07/01/04");
callStmt.setInt(2,3);
callStmt.registerOutParameter(3,OracleTypes.CURSOR);
callStmt.registerOutParameter(4,OracleTypes.CURSOR);
callStmt.registerOutParameter(5,OracleTypes.CURSOR);
callStmt.execute();
ResultSet rs = (ResultSet)callStmt.getObject(5);
if(rs != null) {
while(rs.next()) {
System.out.println(""+rs.getString(0));
}
} else System.out.println("RS is NULL");

After this program executed i am getting RS is NULL and and getting the sql exception "cursor is closed" but i am not closing cursor in the procedure.

Any suggestions please...
Srilakshmi

[Makarand Parab]

Hi
Can you do one thing
Just print out
ResultSet rs = (ResultSet)callStmt.getObject(3);
ResultSet rs1 = (ResultSet)callStmt.getObject(4);
ResultSet rs2 = (ResultSet)callStmt.getObject(5);

System.out.println(rs);
System.out.println(rs1);
System.out.println(rs2);

and let me know. Also do one thing run this part of SP in toad or sql plus
OPEN p_COMMENTDATA for
Select TO_CHAR(DATETIME,'HH24:MI'),
COMMENTS
FROM CTS_TOUR_COMMENT
WHERE TO_CHAR(DATETIME,'MM/DD/YYYY HH24:MI:SS')
BETWEEN tourFromDateTime AND tourToDateTime
ORDER BY TO_CHAR(DATETIME,'HH24:MI');
EXCEPTION
WHEN NO_DATA_FOUND THEN Null;
WHEN OTHERS THEN Null;

I think this procedure is failing, either it is returning "NO_DATA_FOUND" or there is some exception. Please check and let me know.
Java code looks fine, i sense problem with SP

Regards
Makarand Parab

[Makarand Parab]

SriLaxmi
Usually SP is written this way

PROCEDURE SP_SET_STATUS(p_cycleid IN STATUS.cycleid%type,
p_customer_no IN STATUS.customer_no%type,
p_package_type IN STATUS.package_type%type
default 'C',
p_status IN STATUS.status%type,
p_requestid IN STATUS.requestid%type,
o_message OUT varchar2
)
AS
BEGIN
insert into status(cycleid,customer_no,package_type,status,requestid)
values(p_cycleid,p_customer_no,p_package_type,p_status,p_requestid);
o_message:='00000';
EXCEPTION
WHEN others then
o_message:=substr(sqlerrm,12,200);
END SP_SET_STATUS;

Where we have a o_message parameter, this represents that SP has executed properly or not based on the value returned. You can implement the same and try. We usually through exception when the value returned from the SP is not '00000'

Regards
Makarand Parab

[Srilakshmi Vara]

Makaranand,

I tried to execute the following code in the SQL Editor


OPEN p_COMMENTDATA for
Select TO_CHAR(DATETIME,'HH24:MI'),
COMMENTS
FROM CTS_TOUR_COMMENT
WHERE TO_CHAR(DATETIME,'MM/DD/YYYY HH24:MI:SS')
BETWEEN tourFromDateTime AND tourToDateTime
ORDER BY TO_CHAR(DATETIME,'HH24:MI');
EXCEPTION
WHEN NO_DATA_FOUND THEN Null;
WHEN OTHERS THEN Null;


It gave the following exception

ORA-00900: invalid SQL statement

Is this alone enough or do i need to add begin end and declaration part?

Thanks,
Srilakshmi

[Srilakshmi Vara]

Hi Makaranand,

Thanks for suggestions can you give a sample procedure which returns multiple cursors, so that i can correct my stored procedure

Thanks
Srilakshmi

[Makarand Parab]

Hi
I can give you a example of SP with one cursor, same you can repeat for other 2 cursor. I am a j2ee developer having a knowledge of oracle.
Here comes the SP

CREATE OR REPLACE PACKAGE PKG_CUST_TRANSACTION AS
t_ref_cursor ref cursor;

PROCEDURE SP_GET_FAILED_RECS(p_cycleid IN STATUS.cycleid%type,
o_rset OUT t_ref_cursor,
o_message OUT varchar2);

END PKG_CUST_TRANSACTION;
/

CREATE OR REPLACE PACKAGE BODY PKG_CUST_TRANSACTION AS

PROCEDURE SP_GET_FAILED_RECS(p_cycleid IN STATUS.cycleid%type,
o_rset OUT t_ref_cursor,
o_message OUT varchar2)
AS
BEGIN
open o_rset for
select * from
(SELECT CUSTOMER.CUSTOMER_NO as c1,CUSTOMER.CYCLEID as c2,CUSTOMER.BROKERID as
c3,CUSTOMER.ST_ALLIANCEID as c4,STATUS.status as s1
FROM CUSTOMER, STATUS
WHERE Customer.CYCLEID=p_cycleid and status.customer_no(+)=customer.customer_no)
where s1 is null or s1 = 'F';

/*keep this common for all 3 cursors*/
EXCEPTION
when others then
o_message:=substr(sqlerrm,12,200);

END SP_GET_FAILED_RECS;

END PKG_CUST_TRANSACTION;


update the above code as per ur requirement. Before u access any rs in the java code don't forget to check the message output.

Let me know.

Regards
Makarand Parab

[Srilakshmi Vara]

Thanks Makaranand,

I changed my procedure according to the sample procedure, with that now i can execute the procedure with out errors and able to execute from the java program successfully, now system outs for result sets are displaying properly but i am getting ORA-01843 invalid month error at rs.next() in the following java program,

String query="{ call PK_CTS_REPORTS.GENERATE_TOURLOG(?,?,?,?,?,?) }";
CallableStatement callStmt = con.prepareCall(query);
callStmt.setString(1,tourFromDateTime);
callStmt.setString(2,tourToDateTime);
callStmt.registerOutParameter(3,OracleTypes.VARCHAR);
callStmt.registerOutParameter(4,OracleTypes.CURSOR);
callStmt.registerOutParameter(5,OracleTypes.CURSOR);
callStmt.registerOutParameter(6,OracleTypes.CURSOR);
callStmt.execute();
String sqlErrMessage = callStmt.getString(3);
System.out.println("SQL ERROR MESSAGE FROM STORED PROCEDURE -- "+sqlErrMessage);
ResultSet rs = (ResultSet)callStmt.getObject(4);
System.out.println("RS "+rs);

ResultSet rs1 = (ResultSet)callStmt.getObject(5);
ResultSet rs2 = (ResultSet)callStmt.getObject(6);

System.out.println("RS 1 "+rs1);
System.out.println("RS 2 "+rs2);
while(rs.next()) {
System.out.println(""+rs.getString(0));
}
} catch(SQLException se){se.printStackTrace(System.out);}

i am not getting any error message from Stored Procedure
Help me please.

Srilakshmi

[Makarand Parab]

Hi
Please check my name, u have mispelled it.
Okay about your error
ORA-01843: not a valid month
Cause: A date specified an invalid month. Valid months are: January-December, for format code MONTH, and Jan-Dec, for format code MON.
Action: Enter a valid month value in the correct format.

This is what the oracle code means.
Check your code. Oracle exception is thrown by SP not by java code. Java Exception will just the Oracle error code and show it to you. Hope i am right.

Let me know.

Regards
Makarand Parab

[Srilakshmi Vara]

Hi Makarand,

I am so sorry about mispelling your name.

About the program i will try it as you said

Thanks
Srilakshmi