Hello All,

I am having troubles while passing parameters to an oracle procedure and retruning the values in a ref cursor. The result set is returning a false statement and did not return any value. . I am having year, username parameters which I am passing them to the procedure.

my code is

Java Code:
<%@ page import="java.sql.*,java.util.*,javax.sql.*, oracle.jdbc.*,oracle.jsp.dbutil.ConnCacheBean"%>   
  
    
Connection conn=null;   
  
  
try{   
  
String connectionURL = "jdbc:oracle:thin:@20.0.1.11:1521:cbbtest";   
  
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();   
  
conn = DriverManager.getConnection(connectionURL, "*****", "*****");   
  
    
String username=request.getParameter("username");   
  
out.println(username);   
  
String year=request.getParameter("year");   
  
out.println(year);   
  
    
  
CallableStatement stmt = conn.prepareCall("BEGIN p_get_budget_p(?,?,?); END;");   
  
  
stmt.setString(1, year);    
  
stmt.setString(2, username);    
  
stmt.registerOutParameter(3, OracleTypes.CURSOR);   
  
stmt.execute();   
  
ResultSet rs = ((OracleCallableStatement)stmt).getCursor(3);   
  
    
  
//out.println("rs.next() = "+rs.next());   
  
    while (rs.next()) {    // it is retruning a false value here and doea not enter the while loop   
  
        out.println("<br>ACCTID = "+rs.getString("ACCTID"));   
  
      }   
  
         
      stmt.close();   
  
      rs.close();   
  
  
}   
  
catch (SQLException se)  {out.println(se);se.printStackTrace();}   
  
catch (Exception e)  {out.println(e);e.printStackTrace();}   
  
    
conn.close();   
  
%>  
<%@ page import="java.sql.*,java.util.*,javax.sql.*, oracle.jdbc.*,oracle.jsp.dbutil.ConnCacheBean"%>   
  
    
Connection conn=null;   
  
  
try{   
  
String connectionURL = "jdbc:oracle:thin:@20.0.1.11:1521:cbbtest";   
  
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();   
  
conn = DriverManager.getConnection(connectionURL, "*****", "*****");   
  
    
String username=request.getParameter("username");   
  
out.println(username);   
  
String year=request.getParameter("year");   
  
out.println(year);   
  
    
  
CallableStatement stmt = conn.prepareCall("BEGIN p_get_budget_p(?,?,?); END;");   
  
  
stmt.setString(1, year);    
  
stmt.setString(2, username);    
  
stmt.registerOutParameter(3, OracleTypes.CURSOR);   
  
stmt.execute();   
  
ResultSet rs = ((OracleCallableStatement)stmt).getCursor(3);   
  
    
  
//out.println("rs.next() = "+rs.next());   
  
    while (rs.next()) {    // it is retruning a false value here and doea not enter the while loop   
  
        out.println("<br>ACCTID = "+rs.getString("ACCTID"));   
  
      }   
  
         
      stmt.close();   
  
      rs.close();   
  
  
}   
  
catch (SQLException se)  {out.println(se);se.printStackTrace();}   
  
catch (Exception e)  {out.println(e);e.printStackTrace();}   
  
    
conn.close();   
  
%>
PHP Code:
CREATE OR REPLACE procedure MIS_INT.p_get_budget_p(year_v IN varchar2,usr_v IN VARCHAR2, cursor_v OUT Types.cursor_type )   
  
AS   
  
begin    
  
 OPEN cursor_v FOR   
  
 SELECT ACCTID, ACCTDESC, SUM(BUDGET) AS BUDGET,SUM(ACTUALS) AS ACTUALS,   
  
 SUM(AVAILABLE) AS AVAILABLE,SUM(USED) AS USED FROM   
  
 (   
  
 SELECT "GLAFS"."ACCTID","GLAMF"."ACCTDESC",   
  
 ("GLAFS"."NETPERD1"+ "GLAFS"."NETPERD2"+    
  
 "GLAFS"."NETPERD3"+ "GLAFS"."NETPERD4"+ "GLAFS"."NETPERD5"+    
  
 "GLAFS"."NETPERD6"+ "GLAFS"."NETPERD7"+ "GLAFS"."NETPERD8"+    
  
 "GLAFS"."NETPERD9"+ "GLAFS"."NETPERD10"+ "GLAFS"."NETPERD11"+    
  
 "GLAFS"."NETPERD12") as Budget,   
  
 (SELECT "GLAFSA"."NETPERD1"+ "GLAFSA"."NETPERD2"+    
  
 "GLAFSA"."NETPERD3"+ "GLAFSA"."NETPERD4"+ "GLAFSA"."NETPERD5"+    
  
 "GLAFSA"."NETPERD6"+ "GLAFSA"."NETPERD7"+ "GLAFSA"."NETPERD8"+    
  
 "GLAFSA"."NETPERD9"+ "GLAFSA"."NETPERD10"+ "GLAFSA"."NETPERD11"+    
  
 "GLAFSA"."NETPERD12"    
  
 FROM   "CBBD"."GLAFS" "GLAFSA" inner join "CBBD"."GLAMF" "GLAMFA" on    
  
 "GLAMFA"."ACCTID" = "GLAFSA"."ACCTID"  
  
 WHERE  "GLAFSA"."FSCSYR"="GLAFS"."FSCSYR" AND "GLAFSA"."FSCSDSG"='A'    
  
 AND "GLAFSA"."CURNTYPE"="GLAFS"."CURNTYPE"  
  
and "GLAFSA"."ACCTID" ="GLAFS"."ACCTID" ) as Actuals,   
  
("GLAFS"."NETPERD1"+ "GLAFS"."NETPERD2"+    
  
 "GLAFS"."NETPERD3"+ "GLAFS"."NETPERD4"+ "GLAFS"."NETPERD5"+    
  
 "GLAFS"."NETPERD6"+ "GLAFS"."NETPERD7"+ "GLAFS"."NETPERD8"+    
  
 "GLAFS"."NETPERD9"+ "GLAFS"."NETPERD10"+ "GLAFS"."NETPERD11"+    
  
 "GLAFS"."NETPERD12")-(SELECT "GLAFSA"."NETPERD1"+ "GLAFSA"."NETPERD2"+    
  
 "GLAFSA"."NETPERD3"+ "GLAFSA"."NETPERD4"+ "GLAFSA"."NETPERD5"+    
  
 "GLAFSA"."NETPERD6"+ "GLAFSA"."NETPERD7"+ "GLAFSA"."NETPERD8"+    
  
 "GLAFSA"."NETPERD9"+ "GLAFSA"."NETPERD10"+ "GLAFSA"."NETPERD11"+    
  
 "GLAFSA"."NETPERD12"    
  
 FROM   "CBBD"."GLAFS" "GLAFSA" inner join "CBBD"."GLAMF" "GLAMFA" on    
  
 "GLAMFA"."ACCTID" = "GLAFSA"."ACCTID"  
  
 WHERE  "GLAFSA"."FSCSYR"="GLAFS"."FSCSYR" AND "GLAFSA"."FSCSDSG"='A'    
  
 AND "GLAFSA"."CURNTYPE"="GLAFS"."CURNTYPE"  
  
and "GLAFSA"."ACCTID" ="GLAFS"."ACCTID" ) as Available,   
  
(SELECT "GLAFSA"."NETPERD1"+ "GLAFSA"."NETPERD2"+    
  
 "GLAFSA"."NETPERD3"+ "GLAFSA"."NETPERD4"+ "GLAFSA"."NETPERD5"+    
  
 "GLAFSA"."NETPERD6"+ "GLAFSA"."NETPERD7"+ "GLAFSA"."NETPERD8"+    
  
 "GLAFSA"."NETPERD9"+ "GLAFSA"."NETPERD10"+ "GLAFSA"."NETPERD11"+    
  
 "GLAFSA"."NETPERD12"    
  
 FROM   "CBBD"."GLAFS" "GLAFSA" inner join "CBBD"."GLAMF" "GLAMFA" on    
  
 "GLAMFA"."ACCTID" = "GLAFSA"."ACCTID"  
  
 WHERE  "GLAFSA"."FSCSYR"="GLAFS"."FSCSYR" AND "GLAFSA"."FSCSDSG"='A'    
  
 AND "GLAFSA"."CURNTYPE"="GLAFS"."CURNTYPE"  
  
and "GLAFSA"."ACCTID" ="GLAFS"."ACCTID" )/("GLAFS"."NETPERD1"+ "GLAFS"."NETPERD2"+    
  
 "GLAFS"."NETPERD3"+ "GLAFS"."NETPERD4"+ "GLAFS"."NETPERD5"+    
  
 "GLAFS"."NETPERD6"+ "GLAFS"."NETPERD7"+ "GLAFS"."NETPERD8"+    
  
 "GLAFS"."NETPERD9"+ "GLAFS"."NETPERD10"+ "GLAFS"."NETPERD11"+    
  
 "GLAFS"."NETPERD12") * 100 as Used   
  
FROM   "CBBD"."GLAFS" "GLAFS" RIGHT OUTER join "CBBD"."GLAMF" "GLAMF" on    
  
 "GLAMF"."ACCTID" = "GLAFS"."ACCTID"  
  
 WHERE  "GLAFS"."FSCSYR" = year_v AND "GLAFS"."FSCSDSG"='1' AND "GLAFS"."CURNTYPE"='F'  
  
and "GLAMF"."ACSEGVAL02" IN (SELECT BEGINID FROM CBBD.GLGSSEG WHERE    
  
"GLGSSEG"."USER" = usr_v AND SWCANSEE = '1')   
  
    
  
UNION ALL   
  
    
  
SELECT "GLAMF"."ACCTID","GLAMF"."ACCTDESC",     
  
 0 as Budget,   
  
 0 as Actuals,   
  
0 as Available,   
  
0 as Used   
  
FROM  "CBBD"."GLAMF" "GLAMF"    
  
 WHERE  "GLAMF"."ACSEGVAL02" IN (SELECT BEGINID FROM CBBD.GLGSSEG WHERE    
  
"GLGSSEG"."USER" = usr_v AND SWCANSEE = '1')   
  
)AA    
  
GROUP BY    
  
ACCTID, ACCTDESC ;   
  
    
  
--    
  
exception when others then    
  
raise;   
  
end p_get_budget_p;   
  
/
Can you please help in this matter