Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Posts
    5

    Red face Unanswered: How to connect oracle package using asp

    Hi all below are my package are program.I had created package and asp programm for connecting with oracle database all the time it give me error.Some time it give me a error Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.3001 and some time it give ORA-06550: line 1, column 38: PLS-00201: identifier 'RETURNTYPE' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored -2147217904.Any help will be appreciate.Thanks in advance

    My Package look like
    Package Body TESTPACK
    IS

    procedure SA_TEST (
    TERMINAL_NO in varchar2,
    STOCKHOLDER in varchar2,
    returntype in out number
    )
    as

    begin

    insert into WORKING_ALLOCATION
    (
    seq_no,
    TERMINAL_NO,
    STKHLD_NO

    )
    values(
    XTERM_ALLOC_SEQ.NextVal, --sequence generator
    TERMINAL_NO,
    STOCKHOLDER
    );
    returntype := 1;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    returntype := 2;
    WHEN OTHERS THEN
    returntype := 3;
    end;


    -- Enter further code below as specified in the Package spec.
    END; -- Package Body TESTPACK








    One way
    -------------
    set sa_conn = server.CreateObject("adodb.connection")
    connstring = "Provider=MSDAORA.1;User ID=ss;password=ss;Data Source=ss"
    sa_conn.Open connstring

    Set sa_objRS = Server.CreateObject("ADODB.Recordset")

    sql = "{CALL TESTPACK.SA_TEST('8195', '0001' ,returntype, {resultset 0, retCur})}"

    sa_objRS.Open sql,sa_conn
    if err.number <> 0 then
    Response.Write err.Description & err.number
    Response.End
    end if

    2'nd way
    --------------------
    set sa_conn = server.CreateObject("adodb.connection")
    connstring = "Provider=MSDAORA.1;User ID=ss;password=ss;Data Source=ss"
    sa_conn.Open connstring
    set s_objCommand=Server.CreateObject("ADODB.command")
    tno = "8195"
    stk = "0001"
    with s_objCommand
    .ActiveConnection=sa_conn
    .CommandType = 4 'adCmdStoredProc
    .CommandText = "{CALL TESTPACK.SA_TEST (?)}"
    .Parameters.Append .CreateParameter ("terminal_no",200,adParamInput,7,tno)
    .Parameters.Append .CreateParameter("stockholder",200,adParamInput,7, stk)
    .Parameters.Append .CreateParameter("ReturnInt",3,adParamOutput)

    end with
    sa_objRS.CursorLocation = 3 'adUseClient
    s_objRS.CursorType = 3 'adOpenStatic
    sa_objRS.Open p_objCommand, , 0, 0

    if err.number <> 0 then
    Response.Write err.Description & err.number
    Response.End
    end if

  2. #2
    Join Date
    Jun 2002
    Posts
    18
    you are opening a recordset but havent assed one back from oracle. You need to use Ref cursors or the like ot dont bother with a recordset if you only need a return value simply check the paramter value of the output. also if you dont need to return recordsers make sure you use adexecutenorecords as an arguement on the command object to save resource

Posting Permissions

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