Home » SQL & PL/SQL » SQL & PL/SQL » Something weird is happening...
Something weird is happening... [message #36287] Wed, 14 November 2001 09:10 Go to next message
George Larry
Messages: 10
Registered: October 2001
Junior Member
Something bizzare is happening in my code.
I am trying the same thing- using a PL/SQL procedure or not- and getting different results- actually- I'm getting the same results, to start with, but for some reason when I call the procedure my code ends abruptly. It begins displaying the results... lists a hundred or so then just stops- midword sometimes. No errors... no nothing. I don't understand why this is happening, nor how to make it stop...
I need to get the procedures to work because I have some other queries that are too long to call directly from the jsp.
My JSP (procedure calls commented):
while( rs2.next() ) {
out.println( "Code: " + rs2.getString( "CODE" ) + " - " + rs2.getInt( "CCOUNT" ) + " user(s)</br>" );
out.println( "
" + rHeader );

query = "SELECT LNAME, FNAME, CODE, COMMENTS FROM TELCOMUSERS WHERE CODE = '" + rCode + "' AND DEPTNAME = '" + rDept + "'";
// cs = con.prepareCall ( "{ call userByDept ( ?, ?, ? )}" );
// cs.registerOutParameter( 1, oracle.jdbc.driver.OracleTypes.CURSOR );
// cs.setString( 2, rDept );
// cs.setString( 3, rCode );

// cs.execute();
// rs3 = ( ResultSet ) cs.getObject( 1 );
rs3 = stmt3.executeQuery( query );
while( rs3.next() ) {
rComments = rs3.getString( "COMMENTS" );
if ( rComments == null ) rComments = "";
out.println( "
" +
" - - " +
" - " + rs3.getString( "LNAME" ) + " - " +
" - " + rs3.getString( "FNAME" ) + " - " +
" - " + rs3.getString( "CODE" ) + " - " +
" - " + rComments + " - " +
"
" );
}
out.println( "

" );
}

Here's my procedure:
PROCEDURE userByDept(
rSet OUT reportC.rc,
rDep IN TELCOMUSERS.DEPTNAME%TYPE,
rCod IN TELCOMUSERS.CODE%TYPE
) AS
BEGIN
OPEN rSet FOR
SELECT LNAME, FNAME, CODE, COMMENTS
FROM TELCOMUSERS
WHERE DEPTNAME = rDep
AND CODE = rCod
ORDER BY LNAME;
END;

Any ideas why that would happen?

----------------------------------------------------------------------
Re: Something weird is happening... [message #36290 is a reply to message #36287] Wed, 14 November 2001 10:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Best to get your DBA to assist. Querying some of the data dictionary views could show if something is locked (v$lock, v$session, v$locked_object, all_objects etc). A simple approach my also be to try to re-create your proc as soon as you get the error. If you can't it could indicate that the proc is still running. If you can it could indicate a JDBC or other problem Try to isolate it to the DB or your app/app server.

----------------------------------------------------------------------
Re: Something weird is happening... [message #36293 is a reply to message #36290] Wed, 14 November 2001 10:43 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Oracle says that there is "no" performance impact on setting this parm on the DB high. You should always close the cursor explicitly once you are done with it. Be especially careful to close it (and any JDBC connections too) when an error occurs (finally... close connection).

----------------------------------------------------------------------
Previous Topic: How to retrieve the recordset from PROCEDURE????
Next Topic: status invalid for stored procedures
Goto Forum:
  


Current Time: Sun May 31 18:30:16 CDT 2020