Translaling a Sybase/MSSQL sp to Oracle function

[sybaseguru]

I have seen a number of occasions when a Sybase or MS SQL Server stored procedure needs to be migrated to Oracle. A case in point very common with third part packages is to use a table called next rowid to get the consecutive values for id columns in Sybase or MSSQL. The package makers do this to make the process independent of RDBMS that their customers have. What they normally do is to create a table say called next_rowid with the following basic entries.

CREATE TABLE  next_rowid
 (
  nxr_name        varchar(30)                NOT NULL,
  nxr_next_rowid  integer                    NOT NULL
 )
lock datarows
go
create unique clustered index nxr_name_ind on dbo.next_rowid (nxr_name)
 create unique nonclustered index nxr_id_ind on dbo.next_rowid (nxr_id)
go

So what happens is that application goes to this table via a stored procedure, fetches the next_row_id value "nxr_next_rowid" for a given table_name "nxr_name" and uses that one as the next row ID value in nxr_name table. In doing so the sp opens a transaction and makes sure that the transaction is not comitted until the nxr_next_rowid column has been incremented successfully, locking the row until the transaction is comitted.

The following code shows that

CREATE PROCEDURE
              @table_name    varchar(30),
              @nr            int           = 1
AS
BEGIN
      SET  nocount  ON                  -- performance enhancement
                                        -- check arguments passed
       IF  (ISNULL(@table_name, "") = "")
           BEGIN
                PRINT  "get_next_rowid_sp:  Must supply argument @table_name"
               RETURN  -1
           END
                                        -- check the table exists
       IF  NOT EXISTS (   SELECT  1
                            FROM  next_rowid
                           WHERE  nxr_name = @table_name)
           BEGIN
                PRINT  "get_next_rowid_sp:  '%1!' does not have an entry in 'next_rowid'",
                       @table_name
               RETURN  -101
           END
       IF  (ISNULL(@nr, 0) < 1)         -- make sure value is set correctly
               SELECT  @nr = 1
  DECLARE  @rv  int,
           @tc  int
                                        -- start a transaction to prevent
                                        -- another process from reading an
                                        -- incorrect value
                                        -- determine if we need to open a new
                                        -- transaction or do a savepoint
   SELECT  @tc = @@trancount
       IF  (@tc > 0)
           SAVE TRANSACTION
                       get_next_rowid_sp_trans
     ELSE
           BEGIN TRANSACTION
                       get_next_rowid_sp_trans
   UPDATE  next_rowid
      SET  nxr_next_rowid = nxr_next_rowid + @nr
    WHERE  nxr_name = @table_name
       IF  (@@error != 0)
           BEGIN
                PRINT  &quot;get_next_rowid_sp:  UPDATE failed&quot;
           ROLLBACK TRANSACTION
                       get_next_rowid_sp_trans
               RETURN  -100
           END
   SELECT  @rv = nxr_next_rowid - @nr
     FROM  next_rowid
    WHERE  nxr_name = @table_name
       IF  (@tc = 0)
           COMMIT TRANSACTION
                      get_next_rowid_sp_trans
   RETURN  @rv
END
go

If for one reason or not you do not want to use Oracle sequence and stick to the same methodology then you can create an Oracle function to do the same thing. Bear in mind that you try to avoid "SELECT WITH UPDATE" command in Oracle for locking table:

create or replace function get_next_rowid
(
        i_table_name      IN      next_rowid.nxr_name%TYPE DEFAULT NULL,
        i_nr              IN      NUMBER DEFAULT 1
)
RETURN NUMBER
AS
  e_table_name  EXCEPTION;
  v_table_name  next_rowid.nxr_name%TYPE DEFAULT NULL;
  v_nr  NUMBER := i_nr;
  v_nxr_next_rowid next_rowid.nxr_next_rowid%TYPE DEFAULT NULL;
  e_update EXCEPTION;
BEGIN
  BEGIN         -- Input parameter validation
    -- check for inputs
    IF i_table_name IS NULL
    THEN
      RAISE e_table_name;
    END IF;
    IF i_nr <= 1
    THEN
    --  DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: Warning i_nr = '||i_nr||', set to 1');
      v_nr := 1;
    END IF;
  EXCEPTION
    WHEN e_table_name
    THEN
      DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: Missing/empty parameter, must supply table name');
      RETURN -1;
  END;          -- End input parameter validation
  BEGIN         -- Check that row exists in next_rowid
    SELECT nxr_name
    INTO v_table_name
    FROM next_rowid
    WHERE nxr_name = i_table_name;
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: No record found in next_rowid table for '||i_table_name||'!');
      RETURN -101;
  END;         -- end check that row exists in next_rowid
--
  BEGIN        -- Begin update block
    UPDATE next_rowid
    SET nxr_next_rowid = nxr_next_rowid + v_nr
    WHERE nxr_name = i_table_name
    RETURNING nxr_next_rowid - v_nr INTO v_nxr_next_rowid;
    IF SQL%ROWCOUNT = 0 OR SQLCODE != 0
    THEN
      RAISE e_update;
    END IF;
    COMMIT;
    RETURN v_nxr_next_rowid;
  EXCEPTION
    WHEN e_update
    THEN
      DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: Could not update nxr_next_rowid in next_rowid table for '||i_table_name||'!');
      ROLLBACK;
      RETURN -100;
    WHEN OTHERS
    THEN
      RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM,true);
      RETURN -100;
  END;          -- End update block
--
EXCEPTION
  WHEN OTHERS
  THEN
        DBMS_OUTPUT.PUT('Exception encountered! (');
        DBMS_OUTPUT.PUT_LINE(SQLCODE || '): ' || SQLERRM);
        RETURN -100;
END get_next_rowid;
/

I hope this helps