3 Replies Latest reply: Feb 8, 2012 5:28 AM by Kgronau-Oracle RSS

    Migrating Sybase code to Oracle 11g

    903623
      Hi All,

      We have to migrate Sybase code to Oracle 11g.
      When we are doing a SELECT .. INTO in Sybase , even if the query is returning more than 1 row , sybase selects 1 of the rows and populates the variables.
      But while migrating this code to oracle, we are getting TOO_MANY_ROWS Exception.
      We need to migrate the code from SYBASE to ORACLE without disturbing the logic.

      Can someone please tell me the logic applied by SYBASE to pick up the record?

      And if someone has faced this issue, then what can be done to resolve this.

      Regards,
      Riddhisha Khamesra
        • 1. Re: Migrating Sybase code to Oracle 11g
          Kgronau-Oracle
          Could you please post a small example of the Sybase code?
          • 2. Re: Migrating Sybase code to Oracle 11g
            903623
            Please look at the SYBASE code snippet below

            create table #tmp (col1 smallint, col2 smallint)

            insert into #tmp values(1,2)

            insert into #tmp values(2,2)
            insert into #tmp values(3,2)

            select col1 from #tmp

            declare
            +@a smallint+
            begin


            select @a=col1
            from #tmp

            select @a

            end


            after running the above code we can see the result is 3.
            But if we do this in oracle it will give TOOMANY ROWS Exception.

            Edited by: Riddhisha on Feb 8, 2012 1:40 AM
            • 3. Re: Migrating Sybase code to Oracle 11g
              Kgronau-Oracle
              The result 3 is the last entered record (not the amount of records..).
              So the first approach to write a dummy procedure that will select all records in Oracle like:
              CREATE OR REPLACE PROCEDURE last_record
              (
              v_arg1 OUT NUMBER
              )
              AS

              BEGIN

              SELECT col1

              INTO v_arg1
              FROM tt_tmp ;
              END;

              and hopefully returns only the last record will fail with:
              SQL> variable outvar number
              SQL> exec last_record (:outvar)
              BEGIN last_record (:outvar); END;

              *
              ERROR at line 1:
              ORA-01422: exact fetch returns more than requested number of rows
              ORA-06512: at "SYSTEM.LAST_RECORD", line 9
              ORA-06512: at line 1



              One possible approach to get the last (and only the last) record that was entered into the table is to use rownum:
              The select might look like "select col1 from (select col1, rownum from tt_tmp order by rownum desc) where rownum=1;"

              So the procedure can be coded as:
              CREATE OR REPLACE PROCEDURE last_record
              (
              v_arg1 OUT NUMBER
              )
              AS

              BEGIN

              SELECT col1

              INTO v_arg1
              from (select col1, rownum from tt_tmp order by rownum desc) where rownum=1;

              END;


              Now calling it in SQL*Plus:
              SQL> variable outvar number
              SQL> exec last_record (:outvar)

              PL/SQL procedure successfully completed.

              SQL> print :outvar

              OUTVAR
              ----------
              7


              The value 7 is the last record I've inserted into my tt_tmp table:
              SQL> select * from tt_tmp;

              COL1 COL2
              ---------- ----------
              1 1
              2 2
              8 8
              7 7