Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    69

    Unanswered: Cursors in Oracle

    I am currently working on conversion of cursors from SQL Server to Oracle. There seems to a lot of differences between the two. Please help me with the following queries :

    1. A cursor variable is declared by the following syntax ECLARE @cur_var CURSOR in SQL Server. I think the equivalent for this is to declare a REF CURSOR TYPE and then declare a cursor variable of that TYPE in Oracle. Is this correct ?

    2. You can use a SET statement to assign a value for the cursor variable declared in SQL Server. Something like

    SET @cur_var = CURSOR FOR select * from emp

    How can such assignments be made in Oracle. I saw "OPEN cur_var for (select * from emp)" syntax in Oracle. Is this the equivalent. But then OPEN is specfied as a separate command after SET in SQL Server.

    3. SQL Server presents a lot of added functions while FETCHING the cursors rows. Following are examples :

    FETCH FIRST cursor_name INTO ....
    FETCH LAST cursor_name INTO ....
    FETCH PRIOR cursor_name INTO ....
    FETCH ABSOLUTE 2 cursor_name INTO ....
    FETCH RELATIVE 2 cursor_name INTO ....

    Here FIRST, LAST, PRIOR, ABSOLUTE and RELATIVE are used to fetch respective records from the result set. Are there equivalents for the above said in Oracle ?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    1. In Oracle 9i and above you can use the pre-defined SYS_REFCURSOR type:
    Code:
    DECLARE
      cur_var SYS_REFCURSOR;
    Prior to 9i you gad to define your own ref cursor type, as you say.

    2. Yes, in Oracle you have to OPEN the ref cursor to define its associated query.

    3. No, Oracle just has FETCH. Oracle cursor always start at record 1, and work forwards. You can do array fetches, where you fetch N rows at a time into an array.

  3. #3
    Join Date
    Apr 2004
    Posts
    69
    Thanks for your response Tony !

  4. #4
    Join Date
    Apr 2004
    Posts
    69
    Tony,

    I have another question. SQL Server has the following inbuilt functions :
    @@CURSOR_ROWS and CURSOR_STATUS()

    @@CURSOR_ROWS givens the number of rows in Result Set of the last opened Cursor.

    The Cursor_Status() function gives the status of the Cursor (opened/closed etc.) which was returned by another procedure.

    Is there a way to simulate this the above in Oracle

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There are similar things in Oracle - but not the same.

    cur_var%ROWCOUNT - tells you have many rows have been fetched from the cursor so far. It does not tell you in advance how many rows there are to fetch: that can't be done in Oracle.

    cur_var%IS_OPEN - tells you whether the cursor is currently open or closed.

  6. #6
    Join Date
    Nov 2003
    Posts
    4
    If you want a quick tour of ref cursors and how to access them from Java, here are two articles I wrote:

    http://www.databasedesign-resource.com/ref-cursor.html
    http://www.databasedesign-resource.c...r-in-java.html

    Hope it helps you,

    Alf

Posting Permissions

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