Freeware: Oracle Database Y2K Protection (Part II Rest of Code))

From: <carl_cribbs_at_my-deja.com>
Date: Tue, 22 Jun 1999 21:57:21 GMT
Message-ID: <7kp0rq$ld4$1@nnrp1.deja.com>

If the code I sent is helpful, please let me know so I can impress my management (jicbayr_at_ifx.net)

Attachment Number 3
Y2K_EXISTING_TRIGGER_UPGRADE.SQL SET ECHO OFF
SET LINESIZE 132
SET SERVEROUTPUT On SIZE 1000000
SET FEEDBACK OFF
SET SHOWMODE OFF
SET TERM OFF
SET HEADING OFF
SET LONG 100000
SET ARRAYSIZE 10
--

File : Y2K_EXISTING_TRIGGER_UPGRADE.SQL
Purpose : Each table's trigger will convert date data (for each
date field) to the year range of 1950 through 2049.
Description : PL/SQL code generates each table's trigger.
Author : Carl Cribbs
Date Created : 15-APR-1999
Restrictions : a. Only for existing "BEFORE" triggers that have tables
with date fields.
b. The date conversion function that is called,
"rr_date_conversion," converts:
i. All years with its last two digits less than 50 to
a 21st century date.
ii. All years with its last two digits greater than or
equal to 50 to a 20th century date.
c. Since this was developed with PL/SQL Release 2.1.5.2.0 and
SQL*Plus Release 3.1.3.5.1, each line is prefixed with
a tab. (Since the original code and the Y2K conversion
are made into two separate inner blocks and leading
blanks are suppressed/trimmed, this maintains a
semblance of the original indentation. The logic
can be commented out or prefixed with spacing for
later PL/SQL versions.)
--

--
--

spool y2k_trigger_upgrade_code.sql
--
--

DECLARE
--

List of date columns for a specified table.
Builds the statement (for each date column) that calls the
date conversion function.
--

CURSOR build_date_columns

      (
         table_date_columns
sys.dba_tab_columns.table_name%TYPE
      ) IS
      SELECT
         column_name
      FROM
         sys.dba_tab_columns
      WHERE
         owner      = '<OWNER>'
         AND
         table_name = table_date_columns
         AND
         data_type  = 'DATE';



--------------------------------------------------


------------------------------

List of any columns a SQL" UPDATE" occurs for.
Used to rebuild the "UPDATE OF" clause.
--

CURSOR build_update_columns_string

      (
         table_update_columns
sys.dba_trigger_cols.table_name%TYPE,
         update_of_trigger_name
sys.dba_trigger_cols.trigger_name%TYPE
      ) IS
      SELECT
         column_name
      FROM
         sys.dba_trigger_cols
      WHERE
         table_name    = table_update_columns
         AND
         trigger_name  = update_of_trigger_name
         AND
         trigger_owner = '<OWNER>'
         AND
         table_owner   = trigger_owner
         AND
         column_list  = 'YES';



--------------------------------------------------


------------------------------

List of triggers (for a specified table with at least one date column).
CURSOR ignores triggers with 'RR_' prefix since these were created
exclusively for converting the date fields.
--

CURSOR existing_tiggers

      (
         table_with_date
sys.dba_tab_columns.table_name%TYPE
      ) IS
      SELECT
         table_name,
         owner,
         trigger_name,
         trigger_type,
         triggering_event,
         table_owner,
         referencing_names,
         when_clause,
         status,
         description,
         trigger_body
      FROM
         sys.dba_triggers
      WHERE
         table_name        = table_with_date
         AND
         owner             = '<OWNER>'
         AND
         trigger_type      = 'BEFORE EACH ROW'
         AND
         trigger_name NOT LIKE 'RR_%'
       ORDER BY table_name;



--------------------------------------------------


------------------------------

List of tables that have date column(s) .
--

CURSOR tables_with_dates IS

      SELECT DISTINCT
         table_name
      FROM
         sys.dba_tab_columns
      WHERE
         owner     = '<OWNER>'
         AND
         data_type = 'DATE';



--------------------------------------------------


------------------------------


-----


----- Variables


-----


--------------------------------------------------


------------------------------


-----

var_beginning_of_line
The character number (within a trigger's body) of a line's
first character (the beginning of the line). (This plus
var_end_of_line are used to edit each trigger's line.)
var_end_of_line
The character number (within a trigger's body) of a line's
last character (the end of line). (This plus var_beginning_of_line
are used to edit each trigger's line.)
var_length_trigger_string
Number of characters in a trigger's body
var_str_column
Text string of columns that appeared after UPDATE FOR plus
date columns.
var_str_row
Text string of a row of the trigger's original code.
var_trigger_type
Processes/edits the trigger type value.

var_beginning_of_line INTEGER;

var_end_of_line INTEGER;

var_length_trigger_string INTEGER;

var_str_column VARCHAR2(1000);

       var_str_row                 VARCHAR2(132);

       var_trigger_type            VARCHAR2(100);



--------------------------------------------------


------------------------------


--------------------------------------------------


------------------------------

LOOP once for each table with a date
--

BEGIN
   FOR table_loop
   IN tables_with_dates
   LOOP

LOOP once for each trigger to modify (add date protection).

FOR modification_loop IN existing_tiggers ( table_loop.table_name ) LOOP

Trigger command syntax

DBMS_OUTPUT.PUT_LINE ( 'CREATE OR REPLACE TRIGGER '|| modification_loop.owner || '.' || modification_loop.trigger_name );
Since only "BEFORE" triggers types can modify "NEW" data values
and want each row/record to have its date column affected,
only modifying "BEFORE EACH ROW" triggers. (If a "BEFORE EACH ROW"
trigger does not exist for "UPDATE" and/or "INSERT,"
"Y2K_TRIGGER_GENERATOR.SQL" creates the Y2K conversion trigger.)
var_trigger_type := 'BEFORE';

Reconstruct the triggering event (UPDATE and/or INSERT and/or DELETE)

IF modification_loop.triggering_event IS NOT NULL THEN
- Combine trigger type and triggering event
  var_trigger_type := var_trigger_type || ' ' || modification_loop.triggering_event;
- Initialize column list SELECT NULL INTO var_str_column FROM DUAL;
- For an UPDATE with column list ("UPDATE OF"), rebuild the list.
  FOR update_cols IN build_update_columns_string ( modification_loop.table_name, modification_loop.trigger_name ) LOOP var_str_column := var_str_column || update_cols.column_name || ', '; END LOOP;
- If any columns are in the column ("UPDATE OF") list,
- edit column list for proper syntax.
- If no columns in the column list, no action required.
  
  IF var_str_column IS NOT NULL THEN
  - Remove trailing comma from non-null column list
    var_str_column := RTRIM ( var_str_column, ', ');
  - If have a column list, combine trigger type with
  - "UPDATE" changed to "UPDATE OF" and the column list.
    var_trigger_type := REPLACE ( var_trigger_type, 'UPDATE', 'UPDATE OF ' || var_str_column ); END IF; END IF;
Output triggering type concatenated with the triggering event (if any) DBMS_OUTPUT.PUT_LINE ( var_trigger_type );

End of reconstructing the triggering event
Output trigger's owner DBMS_OUTPUT.PUT_LINE ( 'ON ' || modification_loop.table_owner || '.' || modification_loop.table_name );
Output any "REFERENCING" statement (e.g. "NEW AS NEW OLD AS OLD") IF modification_loop.referencing_names IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE ( modification_loop.referencing_names ); END IF;
"FOR EACH ROW" part (only modifying "BEFORE EACH ROW" triggers) DBMS_OUTPUT.PUT_LINE ( 'FOR EACH ROW' );
If there is a "WHEN" clause, recreate it with the logic
NOTE: No formatting of "WHEN" clause. (Displayed/output as retrieved
in sys.dba_triggers.when_clause IF modification_loop.when_clause IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE ( 'WHEN' ); DBMS_OUTPUT.PUT_LINE ( '(' ); DBMS_OUTPUT.PUT_LINE ( modification_loop.when_clause ); DBMS_OUTPUT.PUT_LINE ( ')' ); END IF;

Change header and associated comments

         DBMS_OUTPUT.PUT_LINE ( '/*' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE ( '| Name   |  Date
|   Comment' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (
            '| Cribbs | ' || TO_CHAR( SYSDATE,
'DD-MON-YYYY')
            || ' | Added Y2K protection to date
fields' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE ( '*/' );



--------------------------------------------------


------------------------------


--------


-------- BEGINNING OF TRIGGER'S CODE


--------


--------------------------------------------------


------------------------------





--------------------------------------------------


------------------------------


--------------------------------------------------


------------------------------


--------

Beginning of outer block.
Added to encompass new/added date conversion block and
original code.

DBMS_OUTPUT.PUT_LINE ( '/*' ); -- Outer block comment text DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (
            'Beginning of the new outer block to
encompass the added Y2K' );
         DBMS_OUTPUT.PUT_LINE ( 'conversion code
and the original code.' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE ( '*/' );
         DBMS_OUTPUT.PUT_LINE ( 'BEGIN' );  --
Beginning of outer block
         DBMS_OUTPUT.PUT_LINE ( '/*' );     --
Outer block comment text
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE ( 'Inserted Y2K
conversion code.' );
         DBMS_OUTPUT.PUT_LINE (
            'Convert "NEW" date value to be Y2K
complaint.' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE ( '*/' );



--------------------------------------------------


------------------------------


--------------------------------------------------


------------------------------


--------


-------- BEGINNING OF THE Y2K CONVERSION BLOCK


--------


--------------------------------------------------


------------------------------


--------------------------------------------------


------------------------------



         DBMS_OUTPUT.PUT_LINE ( CHR (9) || 'BEGIN'

); -- Beginning of

conversion block DBMS_OUTPUT.PUT_LINE ( CHR (9) );
Blank line spacing

LOOP for a specified table's date columns.
Each date column has a Y2K compliant function call built for it.

FOR date_cols IN build_date_columns ( modification_loop.table_name )

         LOOP
            DBMS_OUTPUT.PUT_LINE
               (
               CHR (9) || '   IF :NEW.' || LOWER(
date_cols.column_name )
               || ' IS NOT NULL '
               );
            DBMS_OUTPUT.PUT_LINE ( CHR (9) || '
THEN' );
            DBMS_OUTPUT.PUT_LINE
               (
               CHR (9) || '      :NEW.' || LOWER(
date_cols.column_name )
               || ' := ' || 'rr_date_conversion(
:NEW.'
               || LOWER( date_cols.column_name )
|| ' );'
               );
            DBMS_OUTPUT.PUT_LINE ( CHR (9) || '
END IF;' );
            DBMS_OUTPUT.PUT_LINE ( '--' );
         END LOOP;



--------

Termination of the Y2K conversion block
DBMS_OUTPUT.PUT_LINE ( CHR (9) || 'END;' ); DBMS_OUTPUT.PUT_LINE ( '/*' ); DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE ( 'Beginning of
original code.' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE ( '*/' );



--------------------------------------------------


------------------------------


--------------------------------------------------


------------------------------


--------


-------- ORIGINAL TRIGGER CODE


--------


--------------------------------------------------


------------------------------


--------------------------------------------------


------------------------------



         var_beginning_of_line   := 1;
         var_end_of_line         := 0;
         var_length_trigger_string := LENGTH (

modification_loop.trigger_body );

WHILE var_end_of_line <
var_length_trigger_string

LOOP

Find the next end of line (each line terminated with a line feed)
var_end_of_line := INSTR (

modification_loop.trigger_body,

                                      CHR( 10 ),

var_beginning_of_line
                                      );

Extract the line of existing trigger code
var_str_row := SUBSTR (

modification_loop.trigger_body,

var_beginning_of_line,

                                  var_end_of_line
- var_beginning_of_line
                                  );



--------------------------------------------------


------------------------------

SINCE THIS WAS DEVELOPED WITH PL/SQL Release 2.1.5.2.0 and
SQL*Plus: Release 3.1.3.5.1, THE BELOW LOGIC IS REQUIRED
Due to leading blanks being suppressed/trimmed, prefix each line
with a tab- "CHR(9)." (This is to indent inner blocks and to
keep from totally losing the existing indentations.
Id desirable, can replace "CHR(9)" with spacing or remove totally.
Do not tab left justified comments IF SUBSTR( var_str_row, 1, 1 ) != '/' AND SUBSTR( var_str_row, 1, 1 ) != '*' AND SUBSTR( var_str_row, 1, 1 ) != '-' THEN var_str_row := CHR(9) || var_str_row; END IF;

END OF REQUIRED LOGIC FOR PL/SQL Release 2.1.5.2.0 and
SQL*Plus: Release 3.1.3.5.1

Output trigger code and reset or update variables
DBMS_OUTPUT.PUT_LINE ( var_str_row );

SELECT NULL INTO var_str_row FROM DUAL; var_beginning_of_line :=
var_end_of_line + 1;

END LOOP;

TERMINATION OF THE NEW/ADDED OUTER BLOCK

DBMS_OUTPUT.PUT_LINE ( '/*' ); DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (
            'End of the new outer block to
encompass the inserted Y2K' );
         DBMS_OUTPUT.PUT_LINE ( 'date conversion
code and the original code.' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE (

'_________________________________________________
________'
            || '_______________________' );
         DBMS_OUTPUT.PUT_LINE ( '*/' );
         DBMS_OUTPUT.PUT_LINE ( 'END;' );
         DBMS_OUTPUT.PUT_LINE ( '/' );     --
Trigger's terminating slash
      END LOOP;

END LOOP; ROLLBACK; END;
.
/
--
--

SPOOL OFF
--

SET ARRAYSIZE 15
SET LONG 80
SET SERVEROUTPUT OFF
SET FEEDBACK On
SET SHOWMODE On
SET TERM On
SET HEADING On
SET ECHO On

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jun 22 1999 - 16:57:21 CDT