A
Code Samples

This appendix contains a sample of an Oracle package used to convert TRACE statements.

OMWB_Emulation Utilities Package

For release 9.2.0.1.0 of the Migration Workbench a user has to be added manually, or the OMWB_emulation references in the generated code should be removed:

REM 
REM Message : Created User :omwb_emulation
REM User : 
CREATE USER omwb_emulation IDENTIFIED BY oracle
;
GRANT CONNECT,RESOURCE TO omwb_emulation
;
CREATE TABLE OMWB_emulation.debug_table(log_date DATE,log_user VARCHAR(100),log_
message
VARCHAR(4000));

CONNECT Omwb_emulation/oracle
REM
REM Message : Created Package : UTILITIES_1
REM User : omwb_emulation
CREATE OR REPLACE PACKAGE utilities AS
  DebugFile   VARCHAR2(20) DEFAULT 'trace.log';
  /* The following variable DebugDir should be edited to
     DEFAULT to a valid UTL_FILE_DIR entry within the
     destination databases init.ora initialization file. */
  DebugDir    VARCHAR2(50); /* DEFAULT ''; */
  DebugOut    INTEGER DEFAULT 3;

  PROCEDURE DEBUG(debug_statement VARCHAR2);
  PROCEDURE DEBUG_TO_TABLE(debug_statement VARCHAR2);
  PROCEDURE DEBUG_TO_DBMS(debug_statement VARCHAR2);
  PROCEDURE DEBUG_TO_FILE(debug_statement VARCHAR2);
  PROCEDURE RESET_DEBUG_TABLE;
  PROCEDURE RESET_DEBUG_FILE;

  FUNCTION HEX (n pls_integer)
    RETURN VARCHAR2;
  FUNCTION MDY (month_in pls_integer,
                day_in   pls_integer,
                year_in  pls_integer)
    RETURN DATE;
  FUNCTION DAY (date_in DATE)
    RETURN INTEGER;
  FUNCTION MONTH (date_in DATE)
    RETURN INTEGER;
  FUNCTION YEAR(date_in DATE)
    RETURN INTEGER;
  FUNCTION WEEKDAY(date_in DATE)
    RETURN INTEGER;
END utilities;

/

REM 
REM Message : Created Package : GLOBALPKG_1
REM User : omwb_emulation
CREATE OR REPLACE PACKAGE globalPkg AUTHID CURRENT_USER AS
/* The following are T/SQL specific global variables. */
  identity  INTEGER;
  trancount INTEGER;
  TYPE RCT1 IS REF CURSOR;/*new weak cursor definition*/
  PROCEDURE incTrancount;
  PROCEDURE decTrancount;
END globalPkg;
/

REM
REM End Packages for omwb_emulation
REM
 
REM
REM Start Stored Procedures for omwb_emulation
REM
 
REM 
REM Message : Created Procedure : UTILITIES
REM User : omwb_emulation
CREATE OR REPLACE PACKAGE BODY utilities AS

PROCEDURE DEBUG (debug_statement IN VARCHAR2) IS
BEGIN
  /* Call the appropriate sub procedure depending on the
     value of the utilities.DebugOut variable.
     This variable should be set within the utilities
     package header. */
  IF(debug_statement IS NULL) THEN
    RETURN;
  END IF;
  IF    (utilities.DebugOut = 1) THEN
    DEBUG_TO_FILE(debug_statement);
  ELSIF (utilities.DebugOut = 2) THEN
    DEBUG_TO_DBMS(debug_statement);
  ELSE
    DEBUG_TO_TABLE(debug_statement);
  END IF;
END DEBUG;

PROCEDURE DEBUG_TO_TABLE (debug_statement IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO OMWB_emulation.debug_table
  VALUES(SYSDATE,
         USER,
         debug_statement);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20108,'utilities.DEBUG_TO_TABLE : Error raised when 
attempting to insert row into OMWB_Emulation.debug_table table.');
END DEBUG_TO_TABLE;

PROCEDURE DEBUG_TO_DBMS(debug_statement VARCHAR2) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(debug_statement);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.ENABLE(1000000);
    DBMS_OUTPUT.PUT_LINE(debug_statement);
END DEBUG_TO_DBMS;

PROCEDURE DEBUG_TO_FILE(debug_statement VARCHAR2) IS
fileID         UTL_FILE.FILE_TYPE;
BEGIN
  fileID := UTL_FILE.FOPEN(utilities.DebugDir,
                           utilities.DebugFile,
                           'a');
  UTL_FILE.PUT_LINE(fileID,
                    SYSDATE
                    || ' '
                    || USER
                    || ' '
                    || debug_statement);
  UTL_FILE.FCLOSE(fileID);
EXCEPTION
  WHEN UTL_FILE.INVALID_OPERATION THEN
    RAISE_APPLICATION_ERROR(-20100,'utilities.DEBUG_TO_FILE raised : Invalid 
operation.');
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    RAISE_APPLICATION_ERROR(-20101,'utilities.DEBUG_TO_FILE raised : Invalid 
file handle.');
  WHEN UTL_FILE.WRITE_ERROR THEN
    RAISE_APPLICATION_ERROR(-20102,'utilities.DEBUG_TO_FILE raised : Write 
Error.');
  WHEN UTL_FILE.INVALID_PATH THEN
    RAISE_APPLICATION_ERROR(-20103,'utilities.DEBUG_TO_FILE raised : Invalid 
path.');
  WHEN UTL_FILE.INVALID_MODE THEN
    RAISE_APPLICATION_ERROR(-20104,'utilities.DEBUG_TO_FILE raised : Invalid 
mode.');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20105,'utilities.DEBUG_TO_FILE raised : Unhandled 
Exception.');
END DEBUG_TO_FILE;

PROCEDURE RESET_DEBUG_TABLE IS
BEGIN
  DELETE FROM OMWB_Emulation.debug_table;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20107,'utilities.RESET_DEBUG_TABLE : Error raised 
when attempting to clear the OMWB_Emulation.debug_table table.');
END RESET_DEBUG_TABLE;

PROCEDURE RESET_DEBUG_FILE IS
fileID         UTL_FILE.FILE_TYPE;
BEGIN
  fileID := UTL_FILE.FOPEN(utilities.DebugDir,
                           utilities.DebugFile,
                           'w');
  UTL_FILE.PUT_LINE(fileid,
                    'Log file creation :'
                    || SYSDATE);
  UTL_FILE.FCLOSE(fileID);
EXCEPTION
  WHEN UTL_FILE.INVALID_OPERATION THEN
    RAISE_APPLICATION_ERROR(-20100,'utilities.RESET_DEBUG_FILE  raised : Invalid 
operation.');
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    RAISE_APPLICATION_ERROR(-20101,'utilities.RESET_DEBUG_FILE  raised : Invalid 
file handle.');
  WHEN UTL_FILE.WRITE_ERROR THEN
    RAISE_APPLICATION_ERROR(-20102,'utilities.RESET_DEBUG_FILE  raised : Write 
Error.');
  WHEN UTL_FILE.INVALID_PATH THEN
    RAISE_APPLICATION_ERROR(-20103,'utilities.RESET_DEBUG_FILE  raised : Invalid 
path.');
  WHEN UTL_FILE.INVALID_MODE THEN
    RAISE_APPLICATION_ERROR(-20104,'utilities.RESET_DEBUG_FILE  raised : Invalid 
mode.');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20105,'utilities.RESET_DEBUG_FILE  raised : 
Unhandled Exception.');
END RESET_DEBUG_FILE;

FUNCTION HEX(n pls_integer)
RETURN VARCHAR2 IS
BEGIN
  IF n > 0 THEN
    RETURN HEX (TRUNC (n / 16)) || SUBSTR ('0123456789ABCDEF', MOD (n, 16) + 1, 
1);
  ELSE
    RETURN NULL;
  END IF;
END HEX;

FUNCTION MDY(month_in pls_integer,
             day_in   pls_integer,
             year_in  pls_integer)
RETURN DATE IS
bad_day   EXCEPTION;
bad_month EXCEPTION;
bad_year  EXCEPTION;
BEGIN
  IF month_in < 0 OR month_in > 12 THEN
    RAISE bad_month;
  END IF;
  IF day_in < 0 OR day_in > 31 THEN
    RAISE bad_day;
  END IF;
  IF year_in < 999 THEN
    RAISE bad_year;
  END IF;
  RETURN TO_DATE(TO_CHAR(month_in)
                 || '-'
                 || TO_CHAR(day_in)
                 || '-'
                 || TO_CHAR(year_in),
                 'MM-DD-YYYY');
EXCEPTION
  WHEN bad_day THEN
    RETURN NULL;
  WHEN bad_year THEN
    RETURN NULL;
  WHEN bad_month THEN
    RETURN NULL;
END MDY;

FUNCTION DAY(date_in DATE)
RETURN INTEGER IS
BEGIN
  IF date_in IS NULL THEN
    RETURN NULL;
  END IF;
  RETURN TO_NUMBER(TO_CHAR(date_in,'DD'));
END DAY;

FUNCTION MONTH(date_in DATE)
RETURN INTEGER IS
BEGIN
  IF date_in IS NULL THEN
    RETURN NULL;
  END IF;
  RETURN TO_NUMBER(TO_CHAR(date_in,'MM'));
END MONTH;

FUNCTION YEAR(date_in DATE)
RETURN INTEGER IS
BEGIN
  IF date_in IS NULL THEN
    RETURN NULL;
  END IF;
  RETURN TO_NUMBER(TO_CHAR(date_in,'YYYY'));
END YEAR;

FUNCTION WEEKDAY(date_in DATE)
RETURN INTEGER IS
BEGIN
  IF date_in IS NULL THEN
    RETURN NULL;
  END IF;
  RETURN TO_NUMBER(TO_CHAR(date_in,'D'));
END WEEKDAY;

END utilities;

/
REM 
REM Message : Created Procedure : SHELL
REM User : omwb_emulation
CREATE OR REPLACE PROCEDURE SHELL(os_command VARCHAR)
AUTHID CURRENT_USER AS
BEGIN
/* This is a dummy stored procedure added by the migration
   workbench. Please see the Migration Workbench users
   guide for information on how to configure this procedure
   for use. */
   NULL;
END SHELL;
/

REM 
REM Message : Created Procedure : GLOBALPKG
REM User : omwb_emulation
CREATE OR REPLACE PACKAGE BODY globalPkg AS
/* This is a dummy package body added by the migration
   workbench in order to emulate T/SQL specific global variables. */
PROCEDURE incTrancount IS
BEGIN
  trancount := trancount + 1;
END incTrancount;
PROCEDURE decTrancount IS
BEGIN
  trancount := trancount - 1;
END decTrancount;
END globalPkg;
/

REM 
REM Message : Created Procedure : DDL_MANAGER
REM User : omwb_emulation
CREATE OR REPLACE PROCEDURE DDL_Manager(ddl_statement VARCHAR)
AUTHID CURRENT_USER IS
BEGIN
  EXECUTE IMMEDIATE ddl_statement;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END DDL_Manager;
/

REM
REM End Stored Procedures for omwb_emulation
REM
 
GRANT EXECUTE ON utilities TO public;
 
 GRANT SELECT, INSERT ON Omwb_emulation.debug_table TO PUBLIC;
 
GRANT EXECUTE ON SHELL TO public;
 
GRANT EXECUTE ON globalPkg TO public;
 
GRANT EXECUTE ON DDL_Manager TO public;