Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    5

    Unanswered: Returning multiple rows

    Hello friends
    We are migrating oracle application to sybase.
    In oracle , in one of the stored procedure we use cursor to return multiple rows.
    This is being used in java as shown below

    CallableStatement call = con.prepareCall(
    "{ call PKG_OBJECTBROWSER.PRC_GET_SECFN_DETAILS(?,?,?,?,?) }");
    call.setInt(1, userId);
    call.setInt(2, usGpId);
    call.setString(3, superUser);
    call.registerOutParameter(4, OracleTypes.CURSOR);
    call.registerOutParameter(5, OracleTypes.NUMBER);


    Now we need the same functionality in sybase.
    Sybase does not have cursor but i guess it uses result set(is this correct ?) to return multiple rows.

    Can please some one point to a sample code where sybase return multiple rows ?

    Thanks in advance

    Vinay

  2. #2
    Join Date
    Mar 2007
    Posts
    25
    Hi Vinay,

    Can you be more specific.. It will be great if you let us know the Oracle procedure that you are currently using.

    Regards,
    Poornima.

  3. #3
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi Vinay,
    In Oracle, the stored procedure does'nt return the result set so they use to have REF cursor and return the records. we have explicit IN and OUT specified for stored procedures in Oracle but in sybase, we dont need to use cursors to retrieve the result sets

    Ex:
    table name : test with 2 columns
    id value
    1 aa
    2 bb
    3 cc

    the stored procedure in sybase look like below
    create procedure proc1
    as
    begin
    select id, value from test
    end

    On executing the above procedure(exec proc1) will return the result set

    id value
    1 aa
    2 bb
    3 cc

    This will be much simpler than stored procedures in Oracle. The same procedure in oracle will be like

    create or replace procedure proc1(
    cv_results in out sys_refcursor)
    is
    begin
    open cv_results for
    select id, value from test;
    end;

    call proc1 will give u the result set

    where sys_refcursor is a type of REF CURSOR type. This is available from Oracle 9i

    you can check with the below link for the jdbc related stuffs
    http://manuals.sybase.com/onlinebook...w/6745;pt=7121
    Last edited by parangiri; 03-22-07 at 08:01.

  4. #4
    Join Date
    Mar 2007
    Posts
    5

    The procedure

    Thanks paran..I had been really wondering how to do this stuff
    Poonam .. here is the stored procedure
    I have made it short
    Paran showed me how can I get the result set but can I have the result set as well as the another parameter which is 'po_ERRORCODE' being returned from the procedure ?

    PROCEDURE PRC_GET_DETAILS(
    pi_lUSER_SEQ_ID IN PKG_COMMON.SEQ_ID_TYPE%TYPE,
    pi_lUSGP_SEQ_ID IN PKG_COMMON.SEQ_ID_TYPE%TYPE,
    pi_SUPERUSER IN CHAR,
    po_SECFDETAILS OUT REF_CUR,
    po_ERRORCODE OUT NUMBER
    )
    IS
    vQuery VARCHAR2(4000);


    BEGIN

    IF (pi_SUPERUSER='Y') THEN

    vQuery:= 'SELECT DISTINCT FROM ABC....'

    ELSE

    vQuery:= 'SELECT DISTINCT FROM XYZ....'

    END IF;

    OPEN po_SECFDETAILS FOR vQuery;

    po_ERRORCODE:=0;

    EXCEPTION

    WHEN NO_DATA_FOUND THEN
    po_ERRORCODE := 1;

    WHEN OTHERS THEN
    po_ERRORCODE := -1;
    RAISE_APPLICATION_ERROR(-20100, 'PRC_GET_SECFN_DETAILS' || ':' || sqlerrm);

    END PRC_GET_DETAILS;

  5. #5
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68

    Post

    Hi,
    As the result set is obtained as a result of stored procedure in sybase, u cant return the error code separately as output. it should be appended to the table and the result set will be obtained.This is a draw back in sybase. We cant use IN and OUT in sybase procedures for getting the OUT parameters separately. Following is the methodology by which u can get it

    create procedure proc1
    as
    begin
    declare @po_ERRORCODE as int
    select @po_ERRORCODE = 0
    select id, value from test
    if @@rowcount=0
    begin
    @po_ERRORCODE = 1
    select 0, "Null", "ErrorCode" as @po_ERRORCODE" into #temp from test
    end
    else
    select id, value, "ErrorCode" as @po_ERRORCODE" into #temp from test

    select id, value, ErrorCode from #temp
    drop #temp
    end

    result will be like if there are values in table
    id value ErrorCode
    1 aaa 0
    2 bbb 0

    result will be like if there are no values in table
    id value ErrorCode
    0 Null 1

    For every record, you have to append and send it.
    Last edited by parangiri; 03-23-07 at 09:12.

Posting Permissions

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