problems while passing the parameters to an oracle procedure Ref Cursor

[Mahmood Ali]

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

<%@ 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();

%>

and here is my procedure which is processed successfully

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 while passing the parameters.

Thanks in advance

[Sudheer Bhat]

Did you try running the SQL in the procedure either from SQL*plus or any of the GUI tools for a sample year_v and usr_v values? Does it return values??

[Mahmood Ali]

How do I run this procedure from toad?

[Sudheer Bhat]

declare

l_refcur SYS_REFCURSOR; -- if your cursor is weakly typed. Else the declaration will be l_refcur Types.cursor_type

begin

p_get_budget_p(<value for year>, <value for usr_v>, l_refcur);

-- try fetching the value from refcur. If you are not getting any values, then you have a problem with your procedure. You need to be fetching the into a record type which contains all the fields selected by the SELECT statement with proper datatypes.

end;