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( "<font font=\"ariel\" size=\"+1\">Code: " + rs2.getString( "CODE" ) + "</font> - <i>" + rs2.getInt( "CCOUNT" ) + " user(s)</i></br>" );
out.println( "<table width=\"90%\">" + 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( "<tr>" +
"<td></td>" +
"<td>" + rs3.getString( "LNAME" ) + "</td>" +
"<td>" + rs3.getString( "FNAME" ) + "</td>" +
"<td>" + rs3.getString( "CODE" ) + "</td>" +
"<td>" + rComments + "</td>" +
"</tr>" );
}
out.println( "</table><br>" );
}
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?
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( "<font font=\"ariel\" size=\"+1\">Code: " + rs2.getString( "CODE" ) + "</font> - <i>" + rs2.getInt( "CCOUNT" ) + " user(s)</i></br>" );
out.println( "<table width=\"90%\">" + 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( "<tr>" +
"<td></td>" +
"<td>" + rs3.getString( "LNAME" ) + "</td>" +
"<td>" + rs3.getString( "FNAME" ) + "</td>" +
"<td>" + rs3.getString( "CODE" ) + "</td>" +
"<td>" + rComments + "</td>" +
"</tr>" );
}
out.println( "</table><br>" );
}
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?
