Expert: Srini Nagarajan - 9/19/2007

Question
Hi,

I have following stored procedure in oracle. which returns cursor can you please send me the asp code block to call this procedure and display the results.
-----------------------------
PACKAGE NAME: ekg_lp_gen
------------------------------
STORED PROCEDURE:
PROCEDURE get_fin_approvers(p_user_id IN      VARCHAR2,
                           p_ccc     IN      VARCHAR2,
                           o_cursor1  OUT  NOCOPY   REF_CURSOR)
 AS
 
 v_ccc           VARCHAR2(100);
 v_grade         VARCHAR2(10);
 
 BEGIN
 
      select hou.attribute1 into v_ccc from apps.hr_all_organization_unitS hou,
                 apps.pay_cost_allocation_keyflex pcaf
      where pcaf.COST_ALLOCATION_KEYFLEX_ID = hou.COST_ALLOCATION_KEYFLEX_ID              
      and   pcaf.segment2 = p_ccc;
      
      select grade into v_grade from hrr_t_emp_master
      where staffnr = p_user_id and term_date is null;
      
      ekg_lp_gen.get_approvers(p_user_id => p_user_id,
                    p_ccc => v_ccc,
                    p_grade => substr(v_grade,4),
                    o_cursor => o_cursor1 );
 
END get_fin_approvers;

-----------------------------------------------
KINDLY GIVE ME THE ASP CODE BLOCK TO CALLING THIS STORED PROCEDURE AND GETTING THE VALUES FROM THE CURSOR. I AM USING FOLLOWING CODE WHICH IS NOT GIVING ME ANY RESULT.
---------------------------------------------
ASP CODE:
---------------------------------------------

<!--# include file="conn_open.asp" -->

<%
  Dim cn, rs, cmd, oParam
  Dim SQL
  SQL = "{call ekg_lp_gen.get_fin_approvers(?, {resultset 0, o_cursor1})}"
  
  set cmd = server.CreateObject ("ADODB.Command")
  set rs = server.CreateObject ( "ADODB.Recordset" )
  
  set cmd.ActiveConnection   = con1
  set cmd.CommandText         = SQL
  set cmd.CommandType         = 1      'adCmdText
  
  'Pass in 0 to retrieve all of the records
  Set oParam = cmd.CreateParameter("p_user_id", adVarChar, adParamInput, "023962")
     cmd.Parameters.Append oParam
  Set oParam = cmd.CreateParameter("p_ccc", adVarChar, adParamInput, "5381")
     cmd.Parameters.Append oParam
  Set oParam = cmd.CreateParameter("o_cursor1", adVarChar, adParamReturnValue, 5000)
     cmd.Parameters.Append oParam
  
  set rs = cmd.execute
  
  while not rs.eof and not rs.bof
  
  Response.Write(rs(0))
  
  wend
  
  
  
  
%>

<!--# include file = "conn_close.asp"-->
---------------------------------------------

ANY HELP WILL BE HIGHLY APPRECIATED.

THANKS
AZEEM QAISER

Answer
Hi,

Try the following sample

<%
sub saveData
 dim custID, custFName, custLName, custAddr, custCity, custState, custZip
dim pCustID, pCustFName, pCustLName, pCustAddr, pCustCity, pCustState,
pCustZip
dim pAction, pMessage
 custID = Request.Form( "id" )
custFName = Request.Form( "fname" )
custLName = Request.Form( "lname" )
custAddr = Request.Form( "address" )
custCity = Request.Form( "city" )
custState = Request.Form( "state" )
custZip = Request.Form( "zip" )
 Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = getDBConnection()
oCmd.CommandText = "updateCustomer"
oCmd.CommandType = 4

Set pCustID = oCmd.CreateParameter("custID",200,1,30,custID)
oCmd.Parameters.Append pCustID

Set pCustFName =
oCmd.CreateParameter("custFName",200,1,30,custFName)
oCmd.Parameters.Append pCustFName

Set pCustLName =
oCmd.CreateParameter("custLName",200,1,30,custLName)
oCmd.Parameters.Append pCustLName

Set pCustAddr = oCmd.CreateParameter("custAddr",200,1,30,custAddr)
oCmd.Parameters.Append pCustAddr

Set pCustCity = oCmd.CreateParameter("custCity",200,1,30,custCity)
oCmd.Parameters.Append pCustCity

Set pCustState =
oCmd.CreateParameter("custState",200,1,30,custState)
oCmd.Parameters.Append pCustState

Set pCustZip = oCmd.CreateParameter("custZip",200,1,30,custZip)
oCmd.Parameters.Append pCustZip

Set pAction = oCmd.CreateParameter("ioAction",131,3,50,0)
oCmd.Parameters.Append pAction

Set pMessage =
oCmd.CreateParameter("ioMessage",200,3,50,"")
oCmd.Parameters.Append pMessage

oCmd.Execute
 if ( oCmd("ioAction") <> 0 ) then
Response.Write( oCmd("ioMessage") )
end if
end sub
%>



For more infor visit the following url

http://www.15seconds.com/issue/000810.htm

-srini