rem
rem $Header: dbmsgen.sql 19-feb-96.17:50:46 ldoo Exp $ 
rem
Rem  Copyright (c) 1993, 1996, 1997, 1998 by Oracle Corporation 
Rem    NAME
Rem     dbmsgen.sql - Replication code generators.
Rem          
Rem    DESCRIPTION
Rem     Routines to generate shadow tables, triggers, and packages for
Rem     table replication.
Rem     Routines to generate wrappers for replication of standalone procedure
Rem     invocations, and packaged procedure invocations.
Rem     Routines which support generated replication code.  
Rem    
Rem    RETURNS
Rem     None
Rem     
Rem    NOTES
Rem      The procedural option is needed to use this facility.
Rem
Rem      This package is installed by sys (connect internal).
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem     avaradar   12/20/98  - add gowner to ensure_normal_status              
Rem     liwong     05/04/98  - remove version in make_internal_pkg
Rem     liwong     02/11/98  - remove set_from_remote                          
Rem     liwong     12/17/97  - internal packages                               
Rem     jstamos    05/14/97 -  bug 493190: replace choose3 with get_final
Rem     jstamos    05/13/97 -  bug 433036: add more pure functions
Rem     sbalaram   04/27/97 -  add nchar procedures for append site and seq
Rem     jstamos    11/20/96 -  nchar support
Rem     jstamos    09/16/96 -  send on update/delete
Rem     jstamos    08/08/96 -  add LOBs to dbms_reputil2
Rem     ldoo       05/08/96 -  Add support for exp/imp replication triggers
Rem     jstamos    05/07/96 -  minimize update communication
Rem     ldoo       05/09/96 -  New security model
Rem     ldoo       02/13/96 -  Add a new parameter to rep_begin
Rem     ldoo       01/17/96 -  Replace replication_is_on with ugaknt
Rem     hasun      08/17/95 -  Add better quiesce check for all sync objects
Rem     hasun      06/01/95 -  merge changes from branch 1.4.720.3
Rem     hasun      04/20/95 -  Move package spec of dbms_defergen to prvtgen.sq
Rem     hasun      03/13/95 -  Fix checkin problems from last revision
Rem     ldoo       01/24/95 -  Modify trigger generator for Object Groups
Rem     jstamos    12/23/94 -  merge all changes from 7.2
Rem     jstamos    11/11/94 -  merge changes from branch 1.1.710.7
Rem     adowning   10/13/94 -  merge rev 1.1.710.4
Rem     adowning   09/21/94 -  improved comments
Rem     ldoo       08/18/94 -  Changed to use columns in the column group
Rem                            instead of parameter columns in the if
Rem                            ignore_discard_flag then section of user funcs.
Rem     adowning   08/10/94 -  Move dbms_maint_gen to prvt from dbms
Rem     ldoo       07/19/94 -  Took out FLOAT as a valid column datatype.
Rem     ldoo       06/23/94 -  Added automatic conflict resolution.
Rem     ldoo       05/09/94 -  Changed the generated trigger by replacing
Rem                            dbms_defer arg calls with dbms_reputil arg
Rem                            calls.  Hence reduce size and enhance speed.
Rem                            Added arg call procedures in dbms_reputil pkg.
Rem     ldoo       03/02/94 -  The argument$.type for ROWID is 69 not 11.
Rem                         -  Default for generate_wrapper_package.procedure_
Rem                            prefix should be NULL.
Rem                         -  Proper error message for attempt to wrap func.
Rem     ldoo       02/25/94 -  Fixed plsql parser bug workaround.
Rem                         -  Do not validate generate_trigger.package_name.
Rem     ldoo       02/18/94 -  Skip LONG and LONG RAW columns in row/col repl.
Rem                         -  Fixed hanging is_dest_node_provided function.
Rem     ldoo       02/17/94 -  Workaround plsql parser bug by adding () to
Rem                            every ten AND clauses in the generated package.
Rem     ldoo       01/21/94 -  Fixed to support mixed-case object names.
Rem     ldoo       01/18/94 -  Added 2 more in parameters to 
Rem                              generate_wrapper_package.
Rem                            Use array parsing.
Rem                            Removed commit statement.
Rem                            Replaced some functions with shared ones.
Rem     ldoo       12/17/93 -  Fixed bug about having extra ');' for
Rem                              column-level replication.
Rem                            Fixed bug about not preserving user-assigned
Rem                              package_name and trigger_name.  
Rem                            Uppercased 'p', '$rp', 't' and '$rt'.
Rem                            Validated IN parameter values.
Rem                            Defaulted USER if output_table is not prefixed
Rem                              with schema name.
Rem                            Double quoted column names in generated trigger.
Rem                            Modified already_exists() to use dba views. 
Rem                            Loop until generated package/trigger name is
Rem                              unique.
Rem     ldoo       10/18/93 -  Eliminated IN OUT parameters.  Supports Remote-
Rem                            Only, Synchronous, and Mixed Replications. 
Rem     dsdaniel   09/01/93 -  split into multiple packages, merged in dbmsrepu
Rem     ldoo       08/25/93 -  Coded to the 8/20 version of spec.
Rem     bsouder    08/13/93 -  minor beautification, corrected dbms_snapshot
Rem                            call
Rem     celsbern   08/13/93 -  added comments
Rem     ldoo	   08/13/93 -  Creation to RDBMS spec.

CREATE OR REPLACE PACKAGE dbms_reputil AS

  ------------
  --  OVERVIEW
  --
  --  This package is referenced only by the generated code.

  ---------------------------
  -- PROCEDURES AND FUNCTIONS
  --

  FUNCTION from_remote
    RETURN BOOLEAN;
  -- in internal packages, we don't want to call PL/SQL to set from_remote,
  -- hence, convert from_remote to a function.
  -- clients are not supposed to assign values to this variable.

  FUNCTION global_name
    RETURN VARCHAR2;

  PROCEDURE set_global_name(gdbname  IN VARCHAR2);
  -- in internal packages, we don't want to call PL/SQL to set from_remote,
  -- hence, convert from_remote to a function.
  -- clients are not supposed to assign values to this variable.
  -- If they do, use set_from_remote(flag) instead.

  PROCEDURE replication_on;
  -- Turn on replication.

  PROCEDURE replication_off;
  -- Turn off replication.

  FUNCTION replication_is_on
    RETURN BOOLEAN;
  -- If false, do not forward/defer the update.

  --
  -- Common procedures and functions shared by Repcat, deferred RPC, etc.
  --
  PROCEDURE canonicalize(name       IN  VARCHAR2,
                         canon_name OUT VARCHAR2,
                         canon_len  IN  NUMBER);
  -- Canonicalize the string passed in as parameter name, determine the
  -- longest prefix that fits in canon_len bytes, and return the result in 
  -- canon_name.  Canonicalization is defined as follows.  If name is NULL,
  -- canon_name becomes NULL.  If name begins and ends with a double quote,
  -- remove both.  Otherwise, convert name to upper case with NLS_UPPER.

  --
  -- Automatic conflict resolution logic.
  --
  PROCEDURE recursion_on;
  -- Keep track of the number of recursion.  

  PROCEDURE recursion_off;
  -- The number of recursion is initialized to zero.

  PROCEDURE rep_begin(site_name IN VARCHAR2 default NULL);
  -- Initialization at the beginning of each rep_delete, rep_insert, and 
  -- rep_update.  It accepts the origin site name and assigns it to the
  -- session variable, ugakos, if ugakos has not been initialized.

  PROCEDURE rep_end;
  -- Clean up at the end of each rep_delete, rep_insert, and rep_update,
  -- including freeing up the memory that has been allocated to the session
  -- variable, ugakos.  

  FUNCTION get_constraint_name(errmsg IN VARCHAR2)
    RETURN VARCHAR2;
  -- Return the name of the uniqueness contraint in the ORA error message. 

  FUNCTION minimum(new                 IN  NUMBER,
                   cur                 IN  NUMBER,
                   ignore_discard_flag OUT BOOLEAN)
    RETURN BOOLEAN;
  -- If new > cur, then ignore_discard_flag is TRUE; otherwise it is FALSE.
  -- Return FALSE if any input parameter is null; otherwise return TRUE.

  FUNCTION minimum(new                 IN  VARCHAR2,
                   cur                 IN  VARCHAR2,
                   ignore_discard_flag OUT BOOLEAN)
    RETURN BOOLEAN;
  -- If new > cur, then ignore_discard_flag is TRUE; otherwise it is FALSE.
  -- Return FALSE if any input parameter is null; otherwise return TRUE.

  FUNCTION minimum(new                 IN  DATE,
                   cur                 IN  DATE,
                   ignore_discard_flag OUT BOOLEAN)
    RETURN BOOLEAN;
  -- If new > cur, then ignore_discard_flag is TRUE; otherwise it is FALSE.
  -- Return FALSE if any input parameter is null; otherwise return TRUE.

  FUNCTION maximum(new                 IN  NUMBER,
                   cur                 IN  NUMBER,
                   ignore_discard_flag OUT BOOLEAN)
    RETURN BOOLEAN;
  -- If new < cur, then ignore_discard_flag is TRUE; otherwise it is FALSE.
  -- Return FALSE if any input parameter is null; otherwise return TRUE.

  FUNCTION maximum(new                 IN  VARCHAR2,
                   cur                 IN  VARCHAR2,
                   ignore_discard_flag OUT BOOLEAN)
    RETURN BOOLEAN;
  -- If new < cur, then ignore_discard_flag is TRUE; otherwise it is FALSE.
  -- Return FALSE if any input parameter is null; otherwise return TRUE.

  FUNCTION maximum(new                 IN  DATE,
                   cur                 IN  DATE,
                   ignore_discard_flag OUT BOOLEAN)
    RETURN BOOLEAN;
  -- If new < cur, then ignore_discard_flag is TRUE; otherwise it is FALSE.
  -- Return FALSE if any input parameter is null; otherwise return TRUE.

  FUNCTION average(new                 IN OUT NUMBER,
                   cur                 IN     NUMBER,
                   ignore_discard_flag OUT    BOOLEAN)
    RETURN BOOLEAN;
  -- Output new as the average of new + old.
  -- Ignore_discard_flag is always FALSE.
  -- Return FALSE if any input parameter is null; otherwise return TRUE.

  FUNCTION additive(old                 IN     NUMBER,
                    new                 IN OUT NUMBER,
                    cur                 IN     NUMBER,
                    ignore_discard_flag OUT    BOOLEAN)
    RETURN BOOLEAN;
  -- Output new as cur + (new - old).  Ignore_discard_flag is always FALSE.
  -- Return FALSE if any input parameter is null; otherwise return TRUE.

  FUNCTION discard(ignore_discard_flag OUT BOOLEAN)
    RETURN BOOLEAN;
  -- Ignore_discard_flag is always TRUE.
  -- Always return TRUE.

  FUNCTION overwrite(ignore_discard_flag OUT BOOLEAN)
    RETURN BOOLEAN;
  -- Ignore_discard_flag is always FALSE.
  -- Always return TRUE.

  FUNCTION append_site_name(new                 IN OUT VARCHAR2,
                            str                 IN     VARCHAR2,
                            max_len             IN     NUMBER,
                            ignore_discard_flag OUT    BOOLEAN)
    RETURN BOOLEAN;

  FUNCTION append_site_name_nc(new                 IN OUT NVARCHAR2,
                               str                 IN     VARCHAR2,
                               max_len             IN     NUMBER,
                               ignore_discard_flag OUT    BOOLEAN)
    RETURN BOOLEAN;
  -- Output new with str appended to it. Ignore_discard_flag is always FALSE.
  -- Return FALSE if any input parameter is null or the length of str plus one
  -- is greater than max_len; otherwise return TRUE.

  FUNCTION append_sequence(new                 IN OUT VARCHAR2,
                           max_len             IN     NUMBER,
                           ignore_discard_flag OUT    BOOLEAN)
    RETURN BOOLEAN;

  FUNCTION append_sequence_nc(new                 IN OUT NVARCHAR2,
                              max_len             IN     NUMBER,
                              ignore_discard_flag OUT    BOOLEAN)
    RETURN BOOLEAN;
  -- Output new with a sequence generated number appended to it.
  -- Ignore_discard_flag is always FALSE.  
  -- Return FALSE if any input parameter is null or the length of the generated
  -- number is greater than max_len; otherwise return TRUE.

  PROCEDURE enter_statistics(sname             IN VARCHAR2,
                             oname             IN VARCHAR2,
                             conflict_type     IN VARCHAR2,
                             reference_name    IN VARCHAR2,
                             method_name       IN VARCHAR2,
                             function_name     IN VARCHAR2,
                             priority_group    IN VARCHAR2,
                             primary_key_value IN VARCHAR2,
                             resolved_date     IN DATE default SYSDATE);
  -- Record that the given conflict has been resolved with the given
  -- resolution.
  -- Input parameters:
  --  sname The name of the schema containing the table to be replicated.
  --  oname The name of the table being replicated.
  --  conflict_type The type of conflict.  Valid values are: `UPDATE',
  --    `UNIQUENESS', and `DELETE'.
  --  reference_name If the conflict type is 'DELETE', enter the replicated
  --    table name here.  If the conflict type is `UPDATE', enter the column
  --    group name here.  If the conflict type is `UNIQUE CONSTRAINT', enter
  --    the unique constraint name here.
  --  method_name The conflict resolution method.
  --  function_name If the method is 'USER FUNCTION', enter the user
  --    resolution function name here.
  --  priority_group If the method is `PRIORITY GROUP', enter the name of
  --    priority group used for resolving the conflict.
  --  primary_key_value The primary key value for the row whose conflict is
  --    being resolved.  
  --  resolved_date The date at which the conflict is resolved.  

  PROCEDURE ensure_normal_status(canon_gname IN VARCHAR2,
                                 canon_gowner IN VARCHAR2 default 'PUBLIC');
  --- Raise exception quiesced_num (-23311) if the status of the object group
  --- is not normal.

  PROCEDURE raw_to_varchar2(r      IN  RAW,
                            offset IN  BINARY_INTEGER,
                            v      OUT VARCHAR2);
  -- Select the "offset" bit in each byte of r, map a 0 to 'N' and a 1 to 'Y',
  -- and put the result in v.  Offset is 1-based and must be between 1 and 8,
  -- inclusive.

  PROCEDURE make_internal_pkg(canon_sname IN VARCHAR2,
                              canon_oname IN VARCHAR2);
  -- Routine that ensures that repcat$_repobject.flag is correct for the given
  -- table with respect to internal pkgs.

  FUNCTION import_rep_trigger_string(arg IN VARCHAR2)
    RETURN VARCHAR2;
  -- Routine in sys.expact$ that generates a PL/SQL string that calls
  -- sync_up_rep.

  PROCEDURE sync_up_rep(canon_sname IN VARCHAR2,
                        canon_oname IN VARCHAR2);
  -- Routine that ensures that sys.tab$.trigflag is correct for the given
  -- table.

END dbms_reputil;
/

DROP PUBLIC SYNONYM dbms_reputil
/
CREATE PUBLIC SYNONYM dbms_reputil FOR dbms_reputil
/
GRANT EXECUTE ON dbms_reputil TO PUBLIC
/

CREATE OR REPLACE PACKAGE dbms_reputil2 AS

  ------------
  --  OVERVIEW
  --
  --  This package is referenced only by the generated code
  --  and needs higher purity than the code in dbms_reputil.

  FUNCTION bit(flag       IN RAW,
               byte       IN NUMBER,
               bit_offset IN NUMBER) RETURN BOOLEAN;
  PRAGMA RESTRICT_REFERENCES(bit, WNPS, RNDS, WNDS);
  -- Test a bit in a byte in a raw.  Byte and bit_offset are 1-based.

  PROCEDURE bis(flag       IN OUT RAW,
                bit_offset IN     NUMBER);
  PRAGMA RESTRICT_REFERENCES(bis, WNPS, RNDS, WNDS);
  -- Set a bit in a one-byte raw.  Bit_offset is 1-based.

  PROCEDURE bic(flag       IN OUT RAW,
                bit_offset IN     NUMBER);
  PRAGMA RESTRICT_REFERENCES(bic, WNPS, RNDS, WNDS);
  -- Clear a bit in a one-byte raw.  Bit_offset is 1-based.

  FUNCTION choose_number(old  IN NUMBER,
                         new  IN NUMBER,
                         flag IN VARCHAR2,
                         byte IN NUMBER) RETURN NUMBER;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_number, WNPS, RNDS, WNDS);

  FUNCTION choose_date(old  IN DATE,
                       new  IN DATE,
                       flag IN VARCHAR2,
                       byte IN NUMBER) RETURN DATE;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_date, WNPS, RNDS, WNDS);

  FUNCTION choose_varchar2(old  IN VARCHAR2,
                           new  IN VARCHAR2,
                           flag IN VARCHAR2,
                           byte IN NUMBER) RETURN VARCHAR2;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_varchar2, WNPS, RNDS, WNDS);

  FUNCTION choose_nvarchar2(old  IN NVARCHAR2,
                            new  IN NVARCHAR2,
                            flag IN VARCHAR2,
                            byte IN NUMBER) RETURN NVARCHAR2;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_nvarchar2, WNPS, RNDS, WNDS);

  FUNCTION choose_char(old  IN CHAR,
                       new  IN CHAR,
                       flag IN VARCHAR2,
                       byte IN NUMBER) RETURN CHAR;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_char, WNPS, RNDS, WNDS);

  FUNCTION choose_nchar(old  IN NCHAR,
                        new  IN NCHAR,
                        flag IN VARCHAR2,
                        byte IN NUMBER) RETURN NCHAR;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_nchar, WNPS, RNDS, WNDS);

  FUNCTION choose_rowid(old  IN ROWID,
                        new  IN ROWID,
                        flag IN VARCHAR2,
                        byte IN NUMBER) RETURN ROWID;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_rowid, WNPS, RNDS, WNDS);

  FUNCTION choose_raw(old  IN RAW,
                      new  IN RAW,
                      flag IN VARCHAR2,
                      byte IN NUMBER) RETURN RAW;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_raw, WNPS, RNDS, WNDS);

  FUNCTION choose_blob(old  IN BLOB,
                       new  IN BLOB,
                       flag IN VARCHAR2,
                       byte IN NUMBER) RETURN BLOB;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_blob, RNPS, WNPS, RNDS, WNDS);

  FUNCTION choose_clob(old  IN CLOB,
                       new  IN CLOB,
                       flag IN VARCHAR2,
                       byte IN NUMBER) RETURN CLOB;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_clob, RNPS, WNPS, RNDS, WNDS);

  FUNCTION choose_nclob(old  IN NCLOB,
                        new  IN NCLOB,
                        flag IN VARCHAR2,
                        byte IN NUMBER) RETURN NCLOB;
  -- If flag is null or substr(flag,byte,1) = 'Y' then return new
  -- else return old.
  PRAGMA RESTRICT_REFERENCES(choose_nclob, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_varchar2_equals_new(column_changed$_varchar2 IN VARCHAR2,
                                   offset                   IN NUMBER,
                                   old                      IN VARCHAR2
                                                               CHARACTER SET
                                                               ANY_CS,
                                   new                      IN VARCHAR2
                                                               CHARACTER SET
                                                               old%CHARSET)
    RETURN VARCHAR2;
  -- If old and new are identical then return 'Y' else return 'N.'
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_varchar2_equals_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_char_equals_new(column_changed$_varchar2 IN VARCHAR2,
                               offset                   IN NUMBER,
                               old                      IN CHAR CHARACTER SET
                                                           ANY_CS,
                               new                      IN CHAR CHARACTER SET
                                                           old%CHARSET)
    RETURN VARCHAR2;
  -- If old and new are identical then return 'Y' else return 'N.'
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_char_equals_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_rowid_equals_new(column_changed$_varchar2 IN VARCHAR2,
                                offset                IN NUMBER,
                                old                   IN ROWID,
                                new                   IN ROWID)
    RETURN VARCHAR2;
  -- If old and new are identical then return 'Y' else return 'N.'
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_rowid_equals_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_date_equals_new(column_changed$_varchar2 IN VARCHAR2,
                               offset                   IN NUMBER,
                               old                      IN DATE,
                               new                      IN DATE)
    RETURN VARCHAR2;
  -- If old and new are identical then return 'Y' else return 'N.'
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_date_equals_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_raw_equals_new(column_changed$_varchar2 IN VARCHAR2,
                              offset                   IN NUMBER,
                              old                      IN RAW,
                              new                      IN RAW)
    RETURN VARCHAR2;
  -- If old and new are identical then return 'Y' else return 'N.'
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_raw_equals_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_number_equals_new(column_changed$_varchar2 IN VARCHAR2,
                                 offset                   IN NUMBER,
                                 old                      IN NUMBER,
                                 new                      IN NUMBER)
    RETURN VARCHAR2;
  -- If old and new are identical then return 'Y' else return 'N.'
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_number_equals_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_clob_equals_new(column_changed$_varchar2 IN VARCHAR2,
                               offset                   IN NUMBER,
                               old                      IN CLOB CHARACTER SET
                                                           ANY_CS,
                               new                      IN CLOB CHARACTER SET
                                                           old%CHARSET)
    RETURN VARCHAR2;
  -- If old and new are identical then return 'Y' else return 'N.'
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_clob_equals_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_blob_equals_new(column_changed$_varchar2 IN VARCHAR2,
                               offset                   IN NUMBER,
                               old                      IN BLOB,
                               new                      IN BLOB)
    RETURN VARCHAR2;
  -- If old and new are identical then return 'Y' else return 'N.'
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_blob_equals_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_varchar2_eq_new(column_changed$_varchar2 IN VARCHAR2,
                               offset                   IN NUMBER,
                               old                      IN VARCHAR2
                                                           CHARACTER SET
                                                           ANY_CS,
                               new                      IN VARCHAR2
                                                           CHARACTER SET
                                                           old%CHARSET)
    RETURN BOOLEAN;
  -- If old and new are identical then return true else return false.
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_varchar2_eq_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_char_eq_new(column_changed$_varchar2 IN VARCHAR2,
                           offset                   IN NUMBER,
                           old                      IN CHAR CHARACTER SET
                                                       ANY_CS,
                           new                      IN CHAR CHARACTER SET
                                                       old%CHARSET)
    RETURN BOOLEAN;
  -- If old and new are identical then return true else return false.
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_char_eq_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_date_eq_new(column_changed$_varchar2 IN VARCHAR2,
                           offset                   IN NUMBER,
                           old                      IN DATE,
                           new                      IN DATE)
    RETURN BOOLEAN;
  -- If old and new are identical then return true else return false.
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_date_eq_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_rowid_eq_new(column_changed$_varchar2 IN VARCHAR2,
                            offset                   IN NUMBER,
                            old                      IN ROWID,
                            new                      IN ROWID)
    RETURN BOOLEAN;
  -- If old and new are identical then return true else return false.
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_rowid_eq_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_raw_eq_new(column_changed$_varchar2 IN VARCHAR2,
                          offset                   IN NUMBER,
                          old                      IN RAW,
                          new                      IN RAW)
    RETURN BOOLEAN;
  -- If old and new are identical then return true else return false.
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_raw_eq_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_number_eq_new(column_changed$_varchar2 IN VARCHAR2,
                             offset                   IN NUMBER,
                             old                      IN NUMBER,
                             new                      IN NUMBER)
    RETURN BOOLEAN;
  -- If old and new are identical then return true else return false.
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_number_eq_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_clob_eq_new(column_changed$_varchar2 IN VARCHAR2,
                           offset                   IN NUMBER,
                           old                      IN CLOB CHARACTER SET
                                                       ANY_CS,
                           new                      IN CLOB CHARACTER SET
                                                       old%CHARSET)
    RETURN BOOLEAN;
  -- If old and new are identical then return true else return false.
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_clob_eq_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_blob_eq_new(column_changed$_varchar2 IN VARCHAR2,
                           offset                   IN NUMBER,
                           old                      IN BLOB,
                           new                      IN BLOB)
    RETURN BOOLEAN;
  -- If old and new are identical then return true else return false.
  -- Use column_changed$_varchar2 if not null.
  -- Otherwise use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_blob_eq_new, RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_varchar2_equals_current(column_sent$_varchar2 IN VARCHAR2,
                                       offset                IN NUMBER,
                                       old                   IN VARCHAR2
                                                                CHARACTER SET
                                                                ANY_CS,
                                       current               IN VARCHAR2
                                                                CHARACTER SET
                                                                old%CHARSET)
    RETURN VARCHAR2;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_varchar2_equals_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_char_equals_current(column_sent$_varchar2 IN VARCHAR2,
                                   offset                IN NUMBER,
                                   old                   IN CHAR CHARACTER SET
                                                            ANY_CS,
                                   current               IN CHAR CHARACTER SET
                                                            old%CHARSET)
    RETURN VARCHAR2;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_char_equals_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_raw_equals_current(column_sent$_varchar2 IN VARCHAR2,
                                  offset                IN NUMBER,
                                  old                   IN RAW,
                                  current               IN RAW)
    RETURN VARCHAR2;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_raw_equals_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_rowid_equals_current(column_sent$_varchar2 IN VARCHAR2,
                                    offset                IN NUMBER,
                                    old                   IN ROWID,
                                    current               IN ROWID)
    RETURN VARCHAR2;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_rowid_equals_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_date_equals_current(column_sent$_varchar2 IN VARCHAR2,
                                   offset                IN NUMBER,
                                   old                   IN DATE,
                                   current               IN DATE)
    RETURN VARCHAR2;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_date_equals_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_number_equals_current(column_sent$_varchar2 IN VARCHAR2,
                                     offset                IN NUMBER,
                                     old                   IN NUMBER,
                                     current               IN NUMBER)
    RETURN VARCHAR2;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_number_equals_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_blob_equals_current(column_sent$_varchar2 IN VARCHAR2,
                                   offset                IN NUMBER,
                                   old                   IN BLOB,
                                   current               IN BLOB)
    RETURN VARCHAR2;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_blob_equals_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_clob_equals_current(column_sent$_varchar2 IN VARCHAR2,
                                   offset                IN NUMBER,
                                   old                   IN CLOB CHARACTER SET
                                                            ANY_CS,
                                   current               IN CLOB CHARACTER SET
                                                            old%CHARSET)
    RETURN VARCHAR2;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_clob_equals_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_varchar2_eq_current(column_sent$_varchar2 IN VARCHAR2,
                                   offset                IN NUMBER,
                                   old                   IN VARCHAR2
                                                            CHARACTER SET
                                                            ANY_CS,
                                   current               IN VARCHAR2
                                                            CHARACTER SET
                                                            old%CHARSET)
    RETURN BOOLEAN;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_varchar2_eq_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_char_eq_current(column_sent$_varchar2 IN VARCHAR2,
                               offset                IN NUMBER,
                               old                   IN CHAR CHARACTER SET
                                                        ANY_CS,
                               current               IN CHAR CHARACTER SET
                                                        old%CHARSET)
    RETURN BOOLEAN;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_char_eq_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_raw_eq_current(column_sent$_varchar2 IN VARCHAR2,
                              offset                IN NUMBER,
                              old                   IN RAW,
                              current               IN RAW)
    RETURN BOOLEAN;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_raw_eq_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_rowid_eq_current(column_sent$_varchar2 IN VARCHAR2,
                                offset                IN NUMBER,
                                old                   IN ROWID,
                                current               IN ROWID)
    RETURN BOOLEAN;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_rowid_eq_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_date_eq_current(column_sent$_varchar2 IN VARCHAR2,
                               offset                IN NUMBER,
                               old                   IN DATE,
                               current               IN DATE)
    RETURN BOOLEAN;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_date_eq_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_number_eq_current(column_sent$_varchar2 IN VARCHAR2,
                                 offset                IN NUMBER,
                                 old                   IN NUMBER,
                                 current               IN NUMBER)
    RETURN BOOLEAN;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_number_eq_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_clob_eq_current(column_sent$_varchar2 IN VARCHAR2,
                               offset                IN NUMBER,
                               old                   IN CLOB CHARACTER SET
                                                        ANY_CS,
                               current               IN CLOB CHARACTER SET
                                                        old%CHARSET)
    RETURN BOOLEAN;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_clob_eq_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION old_blob_eq_current(column_sent$_varchar2 IN VARCHAR2,
                               offset                IN NUMBER,
                               old                   IN BLOB,
                               current               IN BLOB)
    RETURN BOOLEAN;
  -- If column_sent$_varchar2 is NULL, return 'Y.'
  -- If old and current are identical then return 'Y' else return 'N.'
  -- Use equality and null checks.
  PRAGMA RESTRICT_REFERENCES(old_blob_eq_current,
                             RNPS, WNPS, RNDS, WNDS);

  FUNCTION get_final_varchar2(column_changed$_varchar2 IN VARCHAR2,
                              offset                   IN NUMBER, 
                              current                  IN VARCHAR2 CHARACTER
                                                          SET ANY_CS,
                              old                      IN VARCHAR2
                                                          CHARACTER SET
                                                          "CURRENT"%CHARSET,
                              new                      IN VARCHAR2
                                                          CHARACTER SET
                                                          "CURRENT"%CHARSET)
    RETURN VARCHAR2 CHARACTER SET "CURRENT"%CHARSET;
  -- If old and new are identical, then return current else return new.
  -- Use column_changed$_varchar2 if non-null.
  PRAGMA RESTRICT_REFERENCES(get_final_varchar2, RNPS, WNPS, RNDS, WNDS);

  FUNCTION get_final_char(column_changed$_varchar2 IN VARCHAR2,
                          offset                   IN NUMBER, 
                          current                  IN CHAR CHARACTER SET
                                                      ANY_CS,
                          old                      IN CHAR CHARACTER SET
                                                      "CURRENT"%CHARSET,
                          new                      IN CHAR CHARACTER SET
                                                      "CURRENT"%CHARSET)
    RETURN CHAR CHARACTER SET "CURRENT"%CHARSET;
  -- If old and new are identical, then return current else return new.
  -- Use column_changed$_varchar2 if non-null.
  PRAGMA RESTRICT_REFERENCES(get_final_char, RNPS, WNPS, RNDS, WNDS);

  FUNCTION get_final_rowid(column_changed$_varchar2 IN VARCHAR2,
                           offset                   IN NUMBER, 
                           current                  IN ROWID,
                           old                      IN ROWID,
                           new                      IN ROWID)
    RETURN ROWID;
  -- If old and new are identical, then return current else return new.
  -- Use column_changed$_varchar2 if non-null.
  PRAGMA RESTRICT_REFERENCES(get_final_rowid, RNPS, WNPS, RNDS, WNDS);

  FUNCTION get_final_date(column_changed$_varchar2 IN VARCHAR2,
                          offset                   IN NUMBER, 
                          current                  IN DATE,
                          old                      IN DATE,
                          new                      IN DATE)
    RETURN DATE;
  -- If old and new are identical, then return current else return new.
  -- Use column_changed$_varchar2 if non-null.
  PRAGMA RESTRICT_REFERENCES(get_final_date, RNPS, WNPS, RNDS, WNDS);

  FUNCTION get_final_raw(column_changed$_varchar2 IN VARCHAR2,
                         offset                   IN NUMBER, 
                         current                  IN RAW,
                         old                      IN RAW,
                         new                      IN RAW)
    RETURN RAW;
  -- If old and new are identical, then return current else return new.
  -- Use column_changed$_varchar2 if non-null.
  PRAGMA RESTRICT_REFERENCES(get_final_raw, RNPS, WNPS, RNDS, WNDS);

  FUNCTION get_final_number(column_changed$_varchar2 IN VARCHAR2,
                            offset                   IN NUMBER,
                            current                  IN NUMBER,
                            old                      IN NUMBER,
                            new                      IN NUMBER)
    RETURN NUMBER;
  -- If old and new are identical, then return current else return new.
  -- Use column_changed$_varchar2 if non-null.
  PRAGMA RESTRICT_REFERENCES(get_final_number, RNPS, WNPS, RNDS, WNDS);

  FUNCTION get_final_lob(column_changed$_char     IN CHAR,
                         current                  IN CLOB CHARACTER SET
                                                     ANY_CS,
                         new                      IN CLOB CHARACTER SET 
                                                     "CURRENT"%CHARSET)
    RETURN CLOB CHARACTER SET "CURRENT"%CHARSET;
  -- column_changed$_char is non NULL
  -- returns new if and only if column_changed$_char is 'Y'
  PRAGMA RESTRICT_REFERENCES(get_final_lob, RNPS, WNPS, RNDS, WNDS);

  FUNCTION get_final_lob(column_changed$_char     IN CHAR,
                         current                  IN BLOB,
                         new                      IN BLOB)
    RETURN BLOB;
  -- column_changed$_char is non NULL
  -- returns new if and only if column_changed$_char is 'Y'
  PRAGMA RESTRICT_REFERENCES(get_final_lob, RNPS, WNPS, RNDS, WNDS);

  FUNCTION get_final_clob(column_changed$_varchar2 IN VARCHAR2,
                          offset                   IN NUMBER, 
                          current                  IN CLOB CHARACTER SET
                                                      ANY_CS,
                          old                      IN CLOB CHARACTER SET
                                                      "CURRENT"%CHARSET,
                          new                      IN CLOB CHARACTER SET 
                                                      "CURRENT"%CHARSET)
    RETURN CLOB CHARACTER SET "CURRENT"%CHARSET;
  -- If old and new are identical, then return current else return new.
  -- Use column_changed$_varchar2 if non-null.
  PRAGMA RESTRICT_REFERENCES(get_final_clob, RNPS, WNPS, RNDS, WNDS);

  FUNCTION get_final_blob(column_changed$_varchar2 IN VARCHAR2,
                          offset                   IN NUMBER, 
                          current                  IN BLOB,
                          old                      IN BLOB,
                          new                      IN BLOB)
    RETURN BLOB;
  -- If old and new are identical, then return current else return new.
  -- Use column_changed$_varchar2 if non-null.
  PRAGMA RESTRICT_REFERENCES(get_final_blob, RNPS, WNPS, RNDS, WNDS);

END dbms_reputil2;
/

DROP PUBLIC SYNONYM dbms_reputil2
/
CREATE PUBLIC SYNONYM dbms_reputil2 FOR dbms_reputil2
/
GRANT EXECUTE ON dbms_reputil2 TO PUBLIC
/
