| Something weird is happening... [message #36287] |
Wed, 14 November 2001 09:10  |
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  |
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 |
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).
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
