Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: Oracle ref cursor and ADO error

    Hi,

    I found some poitners on internet that says following should work.
    Only problem is 'It is not working! '
    What am I doing wrong?

    Package is compiled successfully. But on ADO side it gives error
    ORA-01036: illegal variable name/number
    --------------------------------------------------------
    ORACLE codes:
    Package:
    --------------------------
    type hepiscore is ref cursor;
    function GetHEPISiteScore2(orgid number, datayear number,datamonth number) return hepiscore;
    procedure GetHEPISiteScore(orgid number, datayear number,datamonth number, c out hepiscore);
    --------------------------
    Package Body:
    procedure GetHEPISiteScore(orgid number, datayear number,datamonth number, c out hepiscore) is


    v_sql varchar2(1000);


    begin
    v_sql:= 'SELECT * from hepi where orgid = rgid AND dyear = :datayear AND dmonth = :datamonth';

    OPEN c for v_sql
    USING orgid, datayear, datamonth;


    exception


    when others then
    null;







    end;
    ----------------------------
    ----------------------------------------------------------------
    ASP Code:

    Set cmd = Server.CreateObject("ADODB.Command")

    on error resume next
    OracleConn.Errors.clear

    cmd.ActiveConnection = OracleConn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "HEPI_REPORTS.GetHEPISiteScore"

    cmd.Parameters.append cmd.CreateParameter(,adNumeric,adParamInput,,orgid )
    cmd.Parameters.append cmd.CreateParameter(,adNumeric,adParamInput,,datay ear)
    cmd.Parameters.append cmd.CreateParameter(,adNumeric,adParamInput,,datam onth)
    cmd.Parameters.append cmd.CreateParameter(,adVariant,adParamOutput,,rs)

    set rs = cmd.execute
    ---------------------------------------------------------

  2. #2
    Join Date
    Dec 2003
    Posts
    4

    Re: Oracle ref cursor and ADO error

    Ok.
    I modified my ASP codes as below as per OLEDB docs.
    ----------------------------------------------------------------------------------
    cmd.CommandText = "{Call HEPI_REPORTS.GetHEPISiteScore(?,?,?) }"

    cmd.Parameters.append cmd.CreateParameter(,adNumeric,adParamInput,,orgid )
    cmd.Parameters.append cmd.CreateParameter(,adNumeric,adParamInput,,datay ear)
    cmd.Parameters.append cmd.CreateParameter(,adNumeric,adParamInput,,datam onth)

    cmd.properties("PLSQLRSet") = true
    set rs = cmd.execute
    cmd.properties("PLSQLRSet") = false

    if isNull(rs) then
    Response.write "rs is Null"
    elseif (isempty(rs)) then
    Response.write "rs is EMpty"
    else
    Response.write rs
    end if
    ----------------------------------------------------------------------

    Surprisingly my rs is empty though procedure returns a cursor.
    ANy insights on this one?

    thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •