4 Replies Latest reply: Sep 25, 2014 8:59 PM by ora_12c RSS

    SQL Developer Sybase->Oracle:: procedure output - recordset instead of cursor?

    user814560

      Hi all,

       

      my customer is using SQL Developer to migrate from Sybase to Oracle. Apparently stored procedures in Sybase return "record sets" which can then be processed by the client receiving the output; after the migration done through SQL Developer, the equivalent converted Oracle stored procedures appear to be returning "cursors" instead of "record sets" and these cannot be processed by clients (unless some hard rework is performed at the client level).

       

      a) is there a way to force SQL Developer to produce Oracle stored procedures returning record sets?

      b) has a similar problem been experienced at other customers? how was it addressed?

       

      Thanks for your help

      Antonio

        • 1. Re: SQL Developer Sybase->Oracle:: procedure output - recordset instead of cursor?
          Jeff Smith Sqldev Pm-Oracle

          In Database 12c, we now have implicit cursors for stored procedures. You just need to add a line to your application to get the data...dbms_sql.return_results();

           

          If you're migrating to 11gR2 and lower versions of Oracle, SQL Developer will migrate your stored procs and change the headers to have the out SYS_REFCURSOR to hand the data back to the calling application.

          • 2. Re: SQL Developer Sybase->Oracle:: procedure output - recordset instead of cursor?
            rp0428
            Apparently stored procedures in Sybase return "record sets" which can then be processed by the client receiving the output;

            Those 'record sets' are just cursors - they are processed by a 'client' by fetching the data using the cursor.

            after the migration done through SQL Developer, the equivalent converted Oracle stored procedures appear to be returning "cursors" instead of "record sets"

            Yes - Oracle will return a cursor. That is also what Sybase, DB2 and most other databases return. The only difference would be the way in which a client uses that cursor to actually access the data.

            and these cannot be processed by clients (unless some hard rework is performed at the client level).

            You haven't told us what 'client' you refer to so not sure what you mean by 'hard rework'. Client access to server data is often different depending on the database being used.

             

            Why is that 'rework' a problem? Typically the client code needs to change anyway because of the different backend database being used and the different functionality it provides. There really is no such thing as 'one size fits all' client code that works without change no matter what the database is. That is usually only possible if the code uses a base, common-denominator set of functionality. There are too many differences between database functionality and implementation for 'seamless' database substitition to work without some mods.

             

            Oracle 12c, as another poster mentioned, supports multiple, implicit result sets. but the word 'implicit' is misleading at best since you have to 'explicitly' execute code to return that 'implicit' result set.

             

            Even then what is returned is a CURSOR (or cursors). Now the client has more work to do than before to actually specify WHICH cursor they want to work with.

             

            So the short answer is that you will need to modify your client anyway. Even the basic connections to the database will be done differently.

             

            You can review some of these examples that show how the various Oracle solutions can be used:

            The 12c 'implicit' method

            http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_sql.htm#CIHIICAG

            RETURN_RESULT Procedures

            This procedure returns the result of an executed statement to the client application.

            The result can be retrieved later by the client. Alternatively, it can return the statement result to and be retrieved later by the immediate caller that executes a recursive statement in which this statement result will be returned.

            The caller can be:

            •   A PL/SQL stored procedure executing the recursive statement using DBMS_SQL
            •   A Java stored procedure using JDBC
            •   A.NET stored procedure using ADO.NET
            •   An external procedure using the Oracle Call Interface (OCI)

            CREATE PROCEDURE proc AS
               rc1 sys_refcursor;
               rc2 sys_refcursor;
               BEGIN
              OPEN rc1 FOR SELECT * FROM t1;
               DBMS_SQL.RETURN_RESULT(rc1);
               OPEN rc2 FOR SELECT * FROM t2;
              DBMS_SQL.RETURN_RESULT(rc2);
               END;
               / 

            Oracle base comparing T-SQL and Oracle

            http://www.oracle-base.com/articles/12c/implicit-statement-results-12cr1.php

             

            Standard Oracle returning a CURSOR - shows how to use the CURSOR to fetch the data using Java

            http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

            • 4. Re: SQL Developer Sybase->Oracle:: procedure output - recordset instead of cursor?
              ora_12c

              Oracle 12c implicit result set feature only works from SQL Plus. Sql developer latest version still not supporting this implicit results processed from stored procedure, This is a known bug : IMPLICIT SUPPORT TO DBMS_SQL.RETURN_RESULT which will be addressed in upcoming release of sql developer.