sqlserver to oracle port - multiple ref cursor problem.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: sqlserver to oracle port - multiple ref cursor problem.

  1. #1
    Join Date
    Aug 2002
    Posts
    115

    sqlserver to oracle port - multiple ref cursor problem.

    Hi,

    We are working on an oracle port project where in the application is being moved from sql server database to oracle, without effecting the front end code a bit...which means..I need to do in oracle what happens in sqlserver ...DITTO.

    Now I have 1 scenario which I am not sure how to go about with.

    SQLSERVER has this funda of of returing recordsets to front end just by using a select statement.eg...

    Code:
    create proc test
    begin
    select empname,empno from emp
    end
    /
    this would return a recordset to front end..

    In oracle I need to use ref cursor's for this...

    But there are some places where in I have something like this in sqlserver
    Code:
    CREATE PROC totalshyt
    BEGIN
    DECLARE c1 CURSOR FOR
    SELECT empno,empname FROM emp
    OPEN AND FETCH THE CURSOR
    
    SELECT empsomething FROM address WHERE empname=@c1.empname
    CLOSE CURSOR
    END
    /
    Now I am not really sure how many select's are going to be in cursor before hand...so the count of ref-cursor's is unknown.

    Can anyone please let me know how to go about with this?

    Thanks
    Sam

  2. #2
    Join Date
    Aug 2006
    Posts
    9
    The cursor in side the procedure is opened and closed and it has nothing to do with cursor which is returning the recordset.
    Usually last Select statement is returned as recordset.
    So u can simply use refcursor for the Select statement for which u want recordset.. see below code

    create proc test (declare in out refcursor)
    begin
    open cursor cur_ref for select empname,empno from emp;
    end
    /


    CREATE PROC totalshyt (declare in out refcursor)
    BEGIN
    DECLARE c1 CURSOR FOR
    SELECT empno,empname FROM emp
    OPEN AND FETCH THE CURSOR
    CLOSE CURSOR

    open cursor cur_ref for SELECT empsomething FROM address WHERE empname=@c1.empname
    END
    /

  3. #3
    Join Date
    Aug 2002
    Posts
    115
    Hi Rakesh,

    Thanks for the reply.

    But that was just an example.

    There are procedures in the sql server system where in a cursor is opened and each fetch returns a recordset to the front end.

    So, for eg, if the cursor fetches 14 records, 14 recordsets are returned to the front end from sql server.

    I want to do something similar in oracle, return multiple recordsets without actually knowling how many (as the cursor is also dynamically created with a where clause)

    Hope I am clear this time.

    Thanks
    Sam

  4. #4
    Join Date
    Aug 2006
    Posts
    9
    In that case I would suggest to replace the existing Cursor statement with single ref cursor and process the records in front end. Retruning recordset on each Fetch is not good idea....as this would cause network load.

    Secondly in Oracle we do not have equivalent feature as of SQLserver.

    Thanks

Posting Permissions

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


Click Here to Expand Forum to Full Width