Rem
Rem $Header: dbmslogmnrd.sql 28-jun-99.16:01:41 jkundu Exp $
Rem $Header: dbmslogmnrd.sql 28-jun-99.16:01:41 jkundu Exp $
Rem
Rem dbmslogmnrd.sql
Rem
Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      dbmslogmnrd.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      This package contains the LogMnr procedure to create the
Rem      LogMnr dictionary file.
Rem
Rem    INPUTS
Rem      Dictionary File Name - name of the file
Rem      File Location - path to file directory
Rem 
Rem
Rem    NOTES
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jkundu      06/28/99 - bug 798473: error message on invalid filename
Rem    gkulkarn    09/30/98 - Upcase the create dictionary table
Rem    doshaugh    06/17/98 - Improve exception handling for invalid file name
Rem    mcusson     05/12/98 - Add grant execute_catalog_role.
Rem    doshaugh    04/14/98 - Created
Rem
Rem
Rem
Rem  PUBLIC PROCEDURES
Rem
Rem     BUILD (FileName, FileLocation)
Rem
Rem
Rem  PRIVATE FUNCTIONS
Rem
Rem     WRITE_COMMENTS (DictionaryFileHandle)
Rem
Rem     GET_DICT_DATA (DictionaryFileHandle)
Rem
Rem     GET_COLUMN_DESCRIPTION (in_Table_Name)
Rem
Rem     WRITE_CREATE_TABLE (in_Table_Name, in_Column_Count)
Rem
Rem     WRITE_INSERT_INTO (in_Table_Name, in_Column_Count, in_Where_Clause)
Rem
Rem     RECORD_TABLE (in_Table_Name, in_Where_Clause)
Rem
Rem
--
CREATE or REPLACE PACKAGE dbms_logmnr_d AS
--
--    PACKAGE NAME
--      dbms_logmnr_d
--
--    DESCRIPTION
--      This package contains the LogMnr procedure to create the
--      LogMnr dictionary file. The procedure queries the dictionary
--      tables of the current database and creates a text based file
--      containing their contenets. Each table is represented by
--      "psuedo" SQL statements. A description of the columns in a 
--      table is created by a "CREATE_TABLE" line (one statement for
--      table). It contains the name, datatype and length for each 
--      column. A "INSERT_INTO" statment is created for each row in a 
--      selected table. It contains the values for each row. The file
--      is created in preparation of future analysis of databases
--      log files using the LogMnr tool.
--
--    INPUTS
--      dictionary_fileName - name of the dictionary file
--      dictionary_ocation - path to file directory
-- 
--    EXAMPLE
--      Creating a dictionary file as:
--                   /oracle/database/l_dictionary.ora
--
--      SVRMGR> execute dbms_logmnr_d.build('l_dictionary.ora',
--      SVRMGR> '/oracle/database/');
--
--    NOTES
--      The dictionary file should be created after all dictionary
--      changes to a database and prior to the creation of any log
--      files that are to be analyzed.
--
--
PROCEDURE  build
		(dictionary_filename IN VARCHAR2,
		 dictionary_location IN VARCHAR2);
--
--
END dbms_logmnr_d; -- End Definition of package
/
--
--
CREATE or REPLACE PACKAGE BODY dbms_logmnr_d
AS
--
PROCEDURE  build 
		(dictionary_filename IN VARCHAR2,
		 dictionary_location IN VARCHAR2)
IS
--
--
-- Global Variables
--
db_header_result NUMBER := 0;
record_table_result NUMBER := 0;
comment_result NUMBER := 0;
dict_filehandle UTL_FILE.FILE_TYPE;
row_count NUMBER := 0;
rcount NUMBER ;
dest_location VARCHAR2(513);
is_utl_set NUMBER;
--
-- Exception initialization
--
file_not_found EXCEPTION;
utl_file_dir_not_set EXCEPTION;
--
PRAGMA EXCEPTION_INIT(file_not_found, -1309);
PRAGMA EXCEPTION_INIT(utl_file_dir_not_set, -1308);
--
--
-- Global column description record and array
--    The column description record contains the name, datatype, and
--    lenght for a column in a table. The array contains a record for
--    each column in the current table being recorded. In addition the
--    record for each column contains several value fields, one field
--    for each suppored datatype. The filed is used to store the values
--    of a column while recording it in the dictionary file.
--
TYPE col_description  IS RECORD (
  col_name       VARCHAR2(30), 
  col_datatype   VARCHAR2(30), 
  col_datalength NUMBER(22),
  col_value_vr2  VARCHAR2(2000), 
  col_value_num  NUMBER(22),
  col_value_lng  LONG,
  col_value_raw  RAW(2000),
  col_value_mls  MLSLABEL,
  col_value_dat  DATE
  ); 
TYPE col_desc_array IS VARRAY(513) OF col_description;
--
col_test_desc col_description;
--
col_test_array col_desc_array := col_desc_array(); -- initialize empty varray 
--
--
vr2_replacement VARCHAR2(4000);
--
--
--
-- **************************************************************************
--
-- WRITE_COMMENTS Function
--  This function will write a set of comments into the dictionary file
--  explaining the file format and steps required to convert the dictionary
--  file into a functional PL/SQL script. 
--
--
FUNCTION   write_comments (  
            DictionaryFileHandle IN UTL_FILE.FILE_TYPE ) 
RETURN NUMBER IS
--
--
--
--
BEGIN                             
--
  UTL_FILE.PUTF(dict_filehandle,  '\n'
    || '-- *************** LOGMNR DICTIONARY FILE *************** '|| '\n'
    || '-- ' || '\n'
    || '-- The LogMnr Dictionary file contains the dictionary data from the ' || '\n'
    || '-- target database. The dictionary data is collected from the dictionary' || '\n'
    || '-- tables defined in the target database. The LogMnr_Dictionary package' || '\n'
    || '-- build script queries the dictionary tables and reconstructs the table' || '\n'
    || '-- contents as a set of "SQL like" commands n the dictionary file.' || '\n'
    );
  UTL_FILE.PUTF(dict_filehandle,  '\n'
    || '-- The dictionary file contains commands to create a table, insert into a' || '\n'
    || '-- table and create an index to a table. To protect against an unintentional' || '\n'
    || '-- execution of the dictionary file, these commands are named ' || '\n'
    || '-- ' || '\n'
    || '--      CREATE_TABLE  - create a table (notice the underbar)' || '\n'
    || '--      CREATE_INDEX  - create an index for a  table (notice the underbar)' || '\n'
    || '--      INSERT_INTO   - insert a row into table (again notice the underbar)' || '\n'
    );
  UTL_FILE.PUTF(dict_filehandle,  '\n'
    || '-- The command syntax is identical to the SQL command except for the' || '\n'
    || '-- underbar replacing the space. ' || '\n'
    || '-- ' || '\n'
    );
  UTL_FILE.PUTF(dict_filehandle,  '\n'
    || '-- The dictionary file can be converted to an executable SQL by globally' || '\n'
    || '-- replacing all occurences of the following text strings : ' || '\n'
    || '-- ' || '\n'
    || '--      CREATE_TABLE => CREATE TABLE' || '\n'
    || '--      CREATE_INDEX => CREATE INDEX' || '\n'
    || '--      INSERT_INTO  => INSERT INTO' || '\n'
    || '--      ,, => ,NULL,'
    || '--      (, => (NULL,'
    || '--      ,) => ,NULL)'
    || '-- ' || '\n'
    );
  UTL_FILE.PUTF(dict_filehandle,  '\n'
    || '-- For example, CREATE_TABLE may be easily globally replaced through the' || '\n'
    || '-- following SED request at a system command prompt' || '\n'
    || '-- ' || '\n'
    || '--  unix_box1>> sed -e/CREATE_TABLE/CREATE TABLE/ dictionary.ora > dictionary.sql' || '\n'
    || '-- ' || '\n'
    || '-- ' || '\n'
    || '-- ' || '\n'
    );
--
-- 
  UTL_FILE.NEW_LINE(dict_filehandle,4);
--
--
  UTL_FILE.FFLUSH(dict_filehandle);
--
--
--
  RETURN 0;
--
--
END;
--
--
--
-- **************************************************************************
--
-- GET_DICT_DATA Function
--  This function issues a series of selects to the database to gather
--  data about the database for later validation with the log files. It
--  creates a DICTIONARY_TABLE to represent the table (by writing a
--  CREATE_TABLE line to the file) and a single row for the values ( by 
--  writing a single INSERT_VALUE for the dictionary table)
--  
--
FUNCTION   get_dict_data (  
            DictionaryFileHandle IN UTL_FILE.FILE_TYPE ) 
RETURN NUMBER IS
--
-- database
db_id NUMBER;
db_name VARCHAR2(9) := NULL; 
db_created VARCHAR2(30);
db_resetlogs_change# NUMBER;
db_resetlogs_time VARCHAR2(30);
db_version_time VARCHAR2(30);
db_redo_type_id VARCHAR2(8);
db_redo_release VARCHAR2(60);
db_character_set VARCHAR2(30);
db_version VARCHAR2(64) := NULL; 
db_status VARCHAR2(64) := NULL;
db_dict_created VARCHAR2(30);
maxobj# NUMBER;
--
BEGIN                             
--
      SELECT value
         INTO db_character_set
         FROM sys.nls_database_parameters
         WHERE parameter = 'NLS_CHARACTERSET';  /* get NLS Character Set */ 
--   
      SELECT type_id, release
         INTO db_redo_type_id, db_redo_release
         FROM sys.v$compatibility
         WHERE type_id = 'REDODATA';  /* get Redo data compatibility */ 
--
      SELECT dbid, name,
             to_char(created,'MM/DD/YYYY HH24:MI:SS'),
             resetlogs_change#,
             to_char(resetlogs_time,'MM/DD/YYYY HH24:MI:SS'),
             to_char(version_time,'MM/DD/YYYY HH24:MI:SS')
          INTO db_id, db_name, db_created , db_resetlogs_change#, db_resetlogs_time , db_version_time
          FROM sys.v$database;
--    
      SELECT version, status
          INTO db_version, db_status 
          FROM sys.product_component_version
          WHERE product like 'PL/SQL%';  /* get database version */ 
--
      SELECT count (*)  into rcount FROM sys.all_objects; 
--
      SELECT MAX(obj#) INTO maxobj# FROM sys.obj$;
--
      SELECT to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') INTO db_dict_created FROM DUAL;
--
--
      UTL_FILE.PUT_LINE(dict_filehandle,
          'CREATE_TABLE DICTIONARY_TABLE ('
          || ' DB_NAME VARCHAR2(9),'
          || ' DB_ID NUMBER(20),'
          || ' DB_CREATED VARCHAR2(20),'
          || ' DB_DICT_CREATED VARCHAR2(20),'
          || ' DB_DICT_SCN NUMBER(22),'
          || ' DB_THREAD_MAP NUMBER(22),'
          || ' DB_RESETLOGS_CHANGE# NUMBER(22),'
          || ' DB_RESETLOGS_TIME VARCHAR2(20),'
          || ' DB_VERSION_TIME VARCHAR2(20),'
          || ' DB_REDO_TYPE_ID VARCHAR2(8),'
          || ' DB_REDO_RELEASE VARCHAR2(60),'
          || ' DB_CHARACTER_SET VARCHAR2(30),'
          || ' DB_VERSION VARCHAR2(64),'
          || ' DB_STATUS VARCHAR2(64),'
          || ' DB_DICT_MAXOBJECTS NUMBER(22),'
          || ' DB_DICT_OBJECTCOUNT NUMBER(22));'
          );
        UTL_FILE.NEW_LINE(dict_filehandle,1);
--
--
        UTL_FILE.PUT_LINE(dict_filehandle,
          'INSERT_INTO DICTIONARY_TABLE VALUES ('
          || '''' || db_name || '''' || ','
          || db_id || ','
          || '''' || db_created || '''' || ','
          || '''' || db_dict_created || '''' || ','
          || NULL || ','
          || NULL || ','
          || db_resetlogs_change# || ','
          || '''' || db_resetlogs_time || '''' || ','
          || '''' || db_version_time || '''' || ','
          || '''' || db_redo_type_id || '''' || ','
          || '''' || db_redo_release || '''' || ','
          || '''' || db_character_set || '''' || ',' 
          || '''' || db_version || '''' || ','
          || '''' || db_status || '''' || ','
          || maxobj# || ','
          || rcount || ');'
          );
--
--
        UTL_FILE.NEW_LINE(dict_filehandle,4);
--
      RETURN db_id;
--
END;
--
--
--
--
--
-- **************************************************************************
--
-- GET_COLUMN_DESCRIPTION Function
--  This function will store the name, datatype and length of
--  every column in a table in the column descriptor array. The
--  column array is ordered by column number.
--
--
FUNCTION   get_column_description ( 
                in_Table_Name IN VARCHAR2 ) 
RETURN NUMBER IS
--
--
col_count NUMBER := 1;
i NUMBER := 1;
--
CURSOR obj_cols is
  SELECT col.name, col.length, 
      decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
      2, decode(col.scale, null,
               decode(col.precision#, null, 'NUMBER', 'FLOAT'),
               'NUMBER'),
      8, 'LONG',
      9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
      12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 
      69, 'ROWID',
      96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'),
      105, 'MLSLABEL',
      106, 'MLSLABEL',
      111, o.name,
      112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'),
      113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
      121, o.name,
      122, o.name,
      123, o.name,
      'UNDEFINED')
  FROM sys.col$ col, sys.obj$ o
  WHERE col.obj# = o.obj# and o.name = in_Table_Name
  ORDER BY col.col#;
--
--
--
BEGIN
--
OPEN obj_cols;
--
--
<< objcol_loop >>
LOOP 
--
    col_test_array.EXTEND;
--
    FETCH obj_cols 
        INTO col_test_array(col_count).col_name, 
             col_test_array(col_count).col_datalength, 
             col_test_array(col_count).col_datatype;
        EXIT objcol_loop  WHEN obj_cols%NOTFOUND;

    col_count := col_count + 1;
    EXIT WHEN col_count = 513;
--
END LOOP objcol_loop; 
--
col_count := col_count-1;
--
--
--
CLOSE obj_cols;
--
RETURN col_count;
--
END;
--
--
-- **************************************************************************
--
-- WRITE_CREATE_TABLE Function
--  This function will write an CREATE_TABLE line to the dictionary
--  file. It uses the current contents of the column descriptor array 
--  to format the CREATE_TABLE line.
--
--
FUNCTION   write_create_table ( 
                in_Table_Name IN VARCHAR2, 
                in_Column_Count IN NUMBER ) 
RETURN NUMBER IS
--
i NUMBER := 1;
--
--
BEGIN
--
IF  in_Column_Count = 0 THEN
--	UTL_FILE.PUT(dict_filehandle,'-- Table ' ||  in_Table_Name || ' no columns defined in the database');
	RETURN 0;
END IF;
--
UTL_FILE.PUT(dict_filehandle,
            'CREATE_TABLE '
            || in_Table_Name
            || '_TABLE ('
            );
--
WHILE i < in_Column_Count LOOP
	UTL_FILE.PUT(dict_filehandle,     
       col_test_array(i).col_name 
    || ' '
    || col_test_array(i).col_datatype
    );
--
    IF col_test_array(i).col_datatype != 'DATE' 
       AND col_test_array(i).col_datatype != 'LONG' THEN
	UTL_FILE.PUT(dict_filehandle, '('   
        || col_test_array(i).col_datalength  
        || '), '
        );
    ELSE UTL_FILE.PUT(dict_filehandle, ', ');
    END IF;
    i := i +1; 
END LOOP;
--
-- Write the last value and terminate line
--
UTL_FILE.PUT(dict_filehandle,
       col_test_array(i).col_name 
    || ' '
    || col_test_array(i).col_datatype);
--   
    IF col_test_array(i).col_datatype != 'DATE' 
       AND col_test_array(i).col_datatype != 'LONG' THEN
	UTL_FILE.PUT_LINE(dict_filehandle, '('   
        || col_test_array(i).col_datalength  
        || ') );'
        );
    ELSE UTL_FILE.PUT_LINE(dict_filehandle, ' ); ');
    END IF;
--
--
RETURN i;
--
END;
--
--
-- **************************************************************************
--
-- WRITE_INSERT_INTO Function
--  This function will write an INSERT_INTO line to the dictionary
--  file. The SQL select is created from the current contents of the name 
--  fields in the the column descriptor array. The correct datatype for
--  a column is determined from the current datatype value in the column
--  record. The correct value field is then used for fetching the contents
--  of a row. A INSERT_INTO line is formatted for each row and written
--  to the dictionary file. A where clause may be specified to allow 
--  filtering of rows in a table.
--
--
FUNCTION   write_insert_into (
                in_Table_Name IN VARCHAR2, 
                in_Column_Count IN NUMBER,
                in_Where_Clause IN VARCHAR2 ) 
RETURN NUMBER IS
--
  i NUMBER := 1;
  row_count NUMBER := 0;
  d number;
  colout_c number;
  colout_str VARCHAR2(500);
  column_list VARCHAR2(500);
  in_column_list VARCHAR2(500) := 'NAME';
-- 
  col_varchar_value VARCHAR(30) := 'TEST';
  colname VARCHAR2(30);
--
--
--
BEGIN
--
--
-- Create the column list for the select
i := 1;
--
--
--

WHILE i < in_Column_Count LOOP

        IF col_test_array(i).col_datatype = 'DATE' THEN
           column_list := column_list ||
          'to_char(' || col_test_array(i).col_name || ',' || '''MM/DD/YYYY HH24:MI:SS''' || ')' || ', ';
        ELSE 
             column_list := column_list || col_test_array(i).col_name || ', ';
        END IF;
--        UTL_FILE.PUT_LINE(dict_filehandle, column_list );
	i := i +1; 
END LOOP;
--
-- copy the last one
IF col_test_array(i).col_datatype = 'DATE' THEN
   column_list := column_list || 'to_char(' || col_test_array(i).col_name ||
   ',' || '''dd mm yy hh24 mi ss''' || ')' || ' ';

ELSE 
    column_list := column_list || col_test_array(i).col_name || ' ';

END IF;
--        UTL_FILE.PUT_LINE(dict_filehandle, column_list );
--
--
--
-- Create SQL statement
colout_str  := 'SELECT  ' || column_list  || ' FROM ' || in_Table_Name || ' ' || in_Where_Clause;
--
colout_c := DBMS_SQL.OPEN_CURSOR;
--
--
--
DBMS_SQL.PARSE(colout_c, colout_str, DBMS_SQL.NATIVE);
--
--
--
-- Define columns for the select
i := 1;


WHILE i <= in_Column_Count LOOP
  IF col_test_array(i).col_datatype = 'VARCHAR2' THEN
    DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_vr2, col_test_array(i).col_datalength);
  ELSIF col_test_array(i).col_datatype = 'NUMBER' THEN
    DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_num);
  ELSIF col_test_array(i).col_datatype = 'DATE' THEN
    DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_vr2, 19);
  ELSIF col_test_array(i).col_datatype = 'LONG' THEN
    DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_vr2, col_test_array(i).col_datalength);
  ELSIF col_test_array(i).col_datatype = 'RAW' THEN
    DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_raw, col_test_array(i).col_datalength);
  ELSIF col_test_array(i).col_datatype = 'MLSLABEL' THEN
    DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_mls, col_test_array(i).col_datalength);
  ELSE
    DBMS_OUTPUT.PUT_LINE ( 'UNKNOWN datatype =  ' || col_test_array(i).col_datatype); 
  END IF;
	i := i +1; 
END LOOP;
--
--
--
-- Execute the SQL code
d := DBMS_SQL.EXECUTE(colout_c);
--
--
<< fetchrow_loop >>
LOOP
    IF DBMS_SQL.FETCH_ROWS(colout_c) = 0 THEN
--       DBMS_OUTPUT.PUT_LINE( ' All rows in TABLE: ' || in_Table_Name || ' recorded                 total rows = ' || row_count);
       EXIT;
    END IF;

	UTL_FILE.PUT(dict_filehandle, 'INSERT_INTO ' || in_Table_Name || '_TABLE VALUES (' );
--
-- Get values for all columns selected
    i := 1;
    WHILE i < in_Column_Count LOOP
--
      IF col_test_array(i).col_datatype = 'VARCHAR2' THEN
        DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_vr2);
        vr2_replacement := REPLACE(col_test_array(i).col_value_vr2, '''', '''''');
      	UTL_FILE.PUT(dict_filehandle, '''' ||vr2_replacement || ''',');
--
      ELSIF col_test_array(i).col_datatype = 'NUMBER' THEN
        DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_num);
      	UTL_FILE.PUT(dict_filehandle, col_test_array(i).col_value_num || ',');
--
      ELSIF col_test_array(i).col_datatype = 'DATE' THEN
        DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_vr2);
        IF(col_test_array(i).col_value_vr2 is NULL) THEN
         	UTL_FILE.PUT(dict_filehandle, ',');
        ELSE
       	  UTL_FILE.PUT(dict_filehandle, 'to_date(''' || col_test_array(i).col_value_vr2 || ''', ''MM/DD/YYYY HH24:MI:SS''),');
        END IF;

--
      ELSIF col_test_array(i).col_datatype = 'LONG' THEN
        DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_lng);
      	UTL_FILE.PUT(dict_filehandle, '''' ||col_test_array(i).col_value_lng || ''',');
--
      ELSIF col_test_array(i).col_datatype = 'RAW' THEN
        DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_raw);
      	UTL_FILE.PUT(dict_filehandle, col_test_array(i).col_value_raw || ',');
--
      ELSIF col_test_array(i).col_datatype = 'MLSLABEL' THEN
        DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_mls);
      	UTL_FILE.PUT(dict_filehandle, col_test_array(i).col_value_mls || ',');
--
      ELSE
        DBMS_OUTPUT.PUT_LINE ( 'UNKNOWN datatype =  ' || col_test_array(i).col_datatype); 
        DBMS_OUTPUT.PUT_LINE ( 'UNKNOWN Name =  ' || col_test_array(i).col_name ); 
--
      END IF;
--
	  i := i +1; 
--
    END LOOP;
--
--


    IF col_test_array(i).col_datatype = 'VARCHAR2' THEN
      DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_vr2);
      UTL_FILE.PUT_LINE(dict_filehandle, '''' || col_test_array(i).col_value_vr2 || ''');');
--
    ELSIF col_test_array(i).col_datatype = 'NUMBER' THEN
      DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_num);
      UTL_FILE.PUT_LINE(dict_filehandle, col_test_array(i).col_value_num || ');');
--
    ELSIF col_test_array(i).col_datatype = 'DATE' THEN
        DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_vr2);

        IF(col_test_array(i).col_value_vr2 is NULL) THEN
         	UTL_FILE.PUT_LINE(dict_filehandle, ' );');
        ELSE
       	  UTL_FILE.PUT_LINE(dict_filehandle, 'to_date(''' || col_test_array(i).col_value_vr2 || ''', ''MM/DD/YYYY HH24:MI:SS''));');
        END IF;
--
--      DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_dat);
--      UTL_FILE.PUT_LINE(dict_filehandle, '''' ||col_test_array(i).col_value_dat || ''');');
--
    ELSIF col_test_array(i).col_datatype = 'LONG' THEN
      DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_lng);
      UTL_FILE.PUT_LINE(dict_filehandle, '''' ||col_test_array(i).col_value_lng || ''');');
--
    ELSIF col_test_array(i).col_datatype = 'RAW' THEN
      DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_raw);
      UTL_FILE.PUT_LINE(dict_filehandle, col_test_array(i).col_value_raw || ');');
--
    ELSIF col_test_array(i).col_datatype = 'MLSLABEL' THEN
      DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_mls);
      UTL_FILE.PUT_LINE(dict_filehandle, col_test_array(i).col_value_mls || ');');
--
    ELSE
      DBMS_OUTPUT.PUT_LINE ( 'UNKNOWN datatype =  ' || col_test_array(i).col_datatype); 
      DBMS_OUTPUT.PUT_LINE ( 'UNKNOWN Name =  ' || col_test_array(i).col_name ); 
--
    END IF;
--
--
	row_count := row_count +1;

--
END LOOP fetchrow_loop;
--
--
--
DBMS_SQL.CLOSE_CURSOR(colout_c);
--
--
--
--

RETURN i;
--
EXCEPTION
-- Create Exception to simply display error code and message
    WHEN OTHERS THEN
--          DBMS_OUTPUT.PUT_LINE
--        ('ERROR ' || to_char(SQLCODE) || SQLERRM);
--          NULL;
--
     DBMS_OUTPUT.PUT_LINE(  '  TABLE: ' || in_Table_Name || '  ' || SQLERRM );
RETURN 0;
--
END;
--
--
-- **************************************************************************
--
-- RECORD_TABLE Function
--  This function issues calls to the get_column_description, 
--  write_correct_table, and write_insert_into functions for
--  a given table name.
--
--
FUNCTION   record_table ( 
                in_Table_Name IN VARCHAR2, 
                in_Where_Clause IN VARCHAR2 ) 
RETURN NUMBER IS
--
--
insert_into_result NUMBER := 0;
create_table_result NUMBER := 0;
--
--
col_count NUMBER := 0;
i NUMBER := 1;
--
--
--
--
--
BEGIN 
--
--
col_count := get_column_description(in_Table_Name);
--
--
create_table_result := write_create_table( in_Table_Name, col_count);
--
--
UTL_FILE.NEW_LINE(dict_filehandle,1);
--
--
insert_into_result := write_insert_into( in_Table_Name, col_count, in_Where_Clause);
--
--
UTL_FILE.NEW_LINE(dict_filehandle,4);
--
IF create_table_result  != 0  THEN
	DBMS_OUTPUT.PUT_LINE(  '  TABLE: ' || in_Table_Name || ' recorded in LogMnr Dictionary File' );
END IF;
--

RETURN create_table_result ;
--
--
END;
--
--
-- **************************************************************************
--
-- BUILD Procedure
--  This public procedure coordinates the creation of the LogMnr
--  dictionary file. function issues calls to the get_column_description, 
--  write_correct_table, and write_insert_into functions for
--  a given table name.
--
--
BEGIN 
--
--  select value INTO dest_location from v$system_parameter where name = 'user_dump_dest';
--
  DBMS_OUTPUT.PUT_LINE ('LogMnr Dictionary Procedure started  ' );
-- 
-- Check to see if UTL_FILE_DIR is set
--
select count(*) into is_utl_set from v$parameter where name = 'utl_file_dir'
and value is not NULL;
  IF is_utl_set = 0 then
    raise utl_file_dir_not_set;
  END IF;
--
--
--  open file
--
  BEGIN
    dict_filehandle := UTL_FILE.FOPEN(dictionary_location, dictionary_filename,'w');
  EXCEPTION
    -- Create Exception to simply display error code and message
    -- WHEN utl_file.INVALID_PATH THEN
    --    RAISE file_not_found;
    --    NULL;
    WHEN OTHERS THEN
        RAISE file_not_found;
--
  END;
--
  DBMS_OUTPUT.PUT_LINE ('LogMnr Dictionary File Opened' );
--
-- write coments to file
  comment_result := write_comments(dict_filehandle);
--
-- ensure a consistent read of the dictionary tables
  SET TRANSACTION READ ONLY;
--
-- create the dictionary table
  db_header_result  := get_dict_data(dict_filehandle);
--
-- create the remaining table
--
  record_table_result := record_table('OBJ$', 
							'WHERE type# != 4 AND type# != 5 AND type# != 7 AND type# != 8 
							AND type# != 9 AND type# != 10 AND type# != 22');
--
  record_table_result := record_table('TAB$', '');
--
  record_table_result := record_table('COL$', '');
--
  record_table_result := record_table('SEG$', '');
--
  record_table_result := record_table('UNDO$', '');
--
  record_table_result := record_table('UGROUP$', '');
--
  record_table_result := record_table('TS$', '');
--
  record_table_result := record_table('CLU$', '');
--
  record_table_result := record_table('IND$', '');
--
  record_table_result := record_table('ICOL$', '');
--
  record_table_result := record_table('LOB$', '');
--
  record_table_result := record_table('USER$', '');
--
  record_table_result := record_table('FILE$', '');
--
  record_table_result := record_table('PARTOBJ$', '');
--
  record_table_result := record_table('PARTCOL$', '');
--
  record_table_result := record_table('TABPART$', '');
--
  record_table_result := record_table('INDPART$', '');
--
  record_table_result := record_table('SUBPARTCOL$', '');
--
  record_table_result := record_table('TABSUBPART$', '');
--
  record_table_result := record_table('INDSUBPART$', '');
--
  record_table_result := record_table('TABCOMPART$', '');
--
  record_table_result := record_table('INDCOMPART$', '');
--
  DBMS_OUTPUT.PUT_LINE ('Procedure executed successfully - LogMnr Dictionary Created' );
--
-- ensure buffer is flushed
  UTL_FILE.FFLUSH(dict_filehandle);
--
-- commit read transaction
-- NEED TO ADD EXCEPTION HANDLING IN THE EVENT OF FAILURE
  COMMIT;
--
-- close file
  UTL_FILE.FCLOSE(dict_filehandle);
--
--
  EXCEPTION
    -- Create Exception to simply display error code and message
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE
      ('ERROR ' || SQLCODE || '  ' || SQLERRM);
        NULL;
    COMMIT;
    RAISE;
--
    UTL_FILE.FCLOSE(dict_filehandle);
--    DBMS_OUTPUT.PUT_LINE('Processed Rows = ' || row_count || 'total =' || rcount);
--
  END build;
--
END dbms_logmnr_d; 
/

show errors
grant execute on dbms_logmnr_d to execute_catalog_role
/
