Resultset with using Oracle Cursor
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Resultset with using Oracle Cursor

  1. #1
    Join Date
    Nov 2002
    Location
    NYC
    Posts
    9

    Resultset with using Oracle Cursor

    Hi,
    I am very much new to oracle. we have a JDBC code and SQL Server procedure and which works fine. Now that we want to implement the same in Oracle also.we deceided not to change the JDBC but to change Stored Procedure and schema.
    Assume for Eg there is a stored procedure in SQL Server that return a simple query from table
    say

    creat procedure Test
    as
    begin
    select * from Accounts
    return
    end
    This procedure returns all the rows in the Accounts table and corresponding java code the fetch all the rows from ResultSet.
    CallableStatement cStmt = con.prepareCall("{ call test ? }" );
    cStmt.setInt( 1 , id_ );
    ResultSet rs = cStmt.executeQuery();
    while( rs.next() )
    {
    // get all the rows
    }
    will give all the rows from the table account.
    Now that we want to implement the same in Oracle.
    I just want to know, does Oracle provides any way to write a pl/sql or Procedure that returns resultset not as a cursor, but i should work with existing code.
    I don't want to declare like the below code

    CallableStatement cStmt = con.prepareCall("{? = call BrowseAccount.getRefCursor }");
    cStmt.registerOutParameter(1, OracleTypes.CURSOR);
    cStmt.execute();
    ResultSet rs = null;
    rs = (ResultSet)cStmt.getObject(1);
    while (rs.next() )
    {
    // get the resultset
    }

    If anybody have any suggestion or sample code will be really appreciated

    thanks
    vijay

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    You'll need to use REF CURSORS and do something like this:

    http://www.oracle-base.com/Articles/...Recordsets.asp

    Remember, the conversion of a ref cursor to a ResultSet, or RecordSet, will only happen if you are using an Oracle driver.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Nov 2002
    Location
    NYC
    Posts
    9
    Thanks for you reply. if there is any way that you return result set WITH OUT using cursors
    any help will be really appreciated

    vijay

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by vijay321
    Thanks for you reply. if there is any way that you return result set WITH OUT using cursors
    Yeah, you can use the old table-array methodology, which sucks. What is the problem with using the Ref Cursors?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Nov 2002
    Location
    NYC
    Posts
    9
    I don't want to change the java code which now works with sybase.
    here is the eg for Sybase/SQL Server coding

    CallableStatement cStmt = con.prepareCall("{ call }");
    cStmt.execute();
    ResultSet rs = null;
    rs = cStmt.getmoreResults();
    while (rs.next() )
    {
    System.out.println(" No of Rows returned is ===> " + iC++);
    }

    the above code works fine for Sybase/sql server. if i want to use oracle i want to add
    cStmt.registerOutParameter(1, OracleTypes.CURSOR);
    for get the ref cursor, which will not work in SQL server/sybase.
    i hope you understand the problem
    any help in this regard will be really helpful.
    thanks
    vijay

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I must confess to not following that code at all .

    However, I suspect that you are confused as well. How Oracle implements a resultset is of no concern to you. Mind you, it used to be, when those nasty table-arrays were used. Now, however, the Oracle driver interprets that Ref Cursor parameter for you and simply returns a resultset from the proc - you never see the parameter.

    So, from your perspective, it simply appears as if you are calling a proc with no ref cursor parameter that simply returns a resultset.

    Make sense?

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    Nov 2002
    Location
    NYC
    Posts
    9
    thanks for your information.
    can you please help find some links or sample program to implement it

    vijay

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