0 Replies Latest reply: Jan 30, 2013 3:18 PM by user480768 RSS

    MS SQL Server system stored procedures can't be migrated into Oracle 11g

    user480768
      During database migration from MS SQL Server 2008 to Oracle 11g R2, if the application stored procedure invokes the MS SQL Server system stored procedures (for example: sp_getapplock, sp_releaseapplock ....), these SQL server system stored procedures can't be transferred. See following migrated Oracle application stored procedure for example:


      create or replace
      PROCEDURE spPwSysID_GetNextID
      (
      v_ID OUT NUMBER,
      iv_SysType IN NVARCHAR2 DEFAULT NULL ,
      iv_Cnt IN NUMBER DEFAULT NULL
      )
      AS
      v_SysType NVARCHAR2(50) := iv_SysType;
      v_Cnt NUMBER(10,0) := iv_Cnt;
      v_result NUMBER(10,0);

      BEGIN

      --SQL Server BEGIN TRANSACTION;
      utils.incrementTrancount;
      v_Systype := UPPER(v_Systype) ;
      IF v_Cnt < 1 THEN
      v_Cnt := 1 ;
      END IF;
      v_result :=sp_getapplock(v_Resource => v_Systype,
      v_LockMode => 'Exclusive') ;
      IF v_result >= 0 THEN

      BEGIN
      SELECT ID

      INTO v_ID
      FROM PWSYSID
      WHERE SysType = v_SysType;
      IF SQL%ROWCOUNT = 1 THEN
      UPDATE PwSysID
      SET ID = ID + v_cnt
      WHERE SysType = v_SysType;
      ELSE

      BEGIN
      INSERT INTO PwSysID
      ( ID, SysType )
      VALUES ( v_cnt + 1, v_SysType );
      v_ID := 1 ;
      END;
      END IF;
      v_result :=sp_releaseapplock(v_Resource => v_Systype) ;
      END;
      ELSE

      BEGIN
      raise_application_error( -20002, 'Lock failed to acquire to generate Cityworks Id.' );
      END;
      END IF;
      utils.commit_transaction;
      END;