CREATE OR REPLACE PROCEDURE print_any
     (data  IN sys.anydata)
IS
  tn   VARCHAR2(61);
  str  VARCHAR2(4000);
  chr  CHAR(255);
  num  NUMBER;
  dat  DATE;
  rw   RAW(4000);
  res  NUMBER;
BEGIN
  IF data IS NULL  THEN
    dbms_output.put_line('NULL value');
    
    RETURN;
  END IF;
  
  tn := data.gettypename();
  
  IF tn = 'SYS.VARCHAR2' THEN
    res := data.getvarchar2(str);
    
    dbms_output.put_line(str);
  ELSIF tn = 'SYS.CHAR' THEN
    res := data.getchar(chr);
    
    dbms_output.put_line(chr);
  ELSIF tn = 'SYS.VARCHAR' THEN
    res := data.getvarchar(chr);
    
    dbms_output.put_line(chr);
  ELSIF tn = 'SYS.NUMBER' THEN
    res := data.getnumber(num);
    
    dbms_output.put_line(num);
  ELSIF tn = 'SYS.DATE' THEN
    res := data.getdate(dat);
    
    dbms_output.put_line(dat);
  ELSIF tn = 'SYS.RAW' THEN
    res := data.getraw(rw);
    
    dbms_output.put_line(rawtohex(rw));
  ELSE
    dbms_output.put_line('typename is '
                         ||tn);
  END IF;
END print_any;
/

CREATE OR REPLACE PROCEDURE print_lcr
     (lcr  IN sys.anydata)
IS
  typenm    VARCHAR2(61);
  ddllcr    sys.lcr$_ddl_record;
  proclcr   sys.lcr$_procedure_record;
  rowlcr    sys.lcr$_row_record;
  res       NUMBER;
  newlist   sys.lcr$_row_list;
  oldlist   sys.lcr$_row_list;
  ddl_text  CLOB;
BEGIN
  typenm := lcr.gettypename();
  
  dbms_output.put_line('type name: '
                       ||typenm);
  
  IF (typenm = 'SYS.LCR$_DDL_RECORD') THEN
    res := lcr.getobject(ddllcr);
    
    dbms_output.put_line('source database: '
                         ||ddllcr.get_source_database_name);
    
    dbms_output.put_line('owner: '
                         ||ddllcr.get_object_owner);
    
    dbms_output.put_line('object: '
                         ||ddllcr.get_object_name);
    
    dbms_output.put_line('is tag null: '
                         ||ddllcr.is_null_tag);
    
    dbms_lob.createtemporary(ddl_text,true);
    
    ddllcr.get_ddl_text(ddl_text);
    
    dbms_output.put_line('ddl: '
                         ||ddl_text);
    
    dbms_lob.freetemporary(ddl_text);
  ELSIF (typenm = 'SYS.LCR$_ROW_RECORD') THEN
    res := lcr.getobject(rowlcr);
    
    dbms_output.put_line('source database: '
                         ||rowlcr.get_source_database_name);
    
    dbms_output.put_line('owner: '
                         ||rowlcr.get_object_owner);
    
    dbms_output.put_line('object: '
                         ||rowlcr.get_object_name);
    
    dbms_output.put_line('is tag null: '
                         ||rowlcr.is_null_tag);
    
    dbms_output.put_line('command_type: '
                         ||rowlcr.get_command_type);
    
    oldlist := rowlcr.get_values('OLD');
    
    FOR i IN 1.. oldlist.COUNT LOOP
      IF oldlist(i) IS NOT NULL  THEN
        dbms_output.put_line('old('
                             ||i
                             ||'): '
                             ||oldlist(i).column_name);
        
        print_any(oldlist(i).data);
      END IF;
    END LOOP;
    
    newlist := rowlcr.get_values('NEW');
    
    FOR i IN 1.. newlist.COUNT LOOP
      IF newlist(i) IS NOT NULL  THEN
        dbms_output.put_line('new('
                             ||i
                             ||'): '
                             ||newlist(i).column_name);
        
        print_any(newlist(i).data);
      END IF;
    END LOOP;
  ELSE
    dbms_output.put_line('Non-LCR Message with type '
                         ||typenm);
  END IF;
END print_lcr;
/

CREATE OR REPLACE PROCEDURE print_errors
IS
  CURSOR c IS 
    SELECT   local_transaction_id,
             source_database,
             message_count,
             error_number,
             error_message
    FROM     dba_apply_error
    ORDER BY source_database,
             source_commit_scn;
  i       NUMBER;
  txnid   VARCHAR2(30);
  source  VARCHAR2(128);
  msgcnt  NUMBER;
  errnum  NUMBER := 0;
  errno   NUMBER;
  errmsg  VARCHAR2(128);
  lcr     sys.anydata;
  r       NUMBER;
BEGIN
  FOR r IN c LOOP
    errnum := errnum + 1;
    
    msgcnt := r.message_count;
    
    txnid := r.local_transaction_id;
    
    source := r.source_database;
    
    errmsg := r.error_message;
    
    errno := r.error_number;
    
    dbms_output.put_line('*************************************************');
    
    dbms_output.put_line('----- ERROR #'
                         ||errnum);
    
    dbms_output.put_line('----- Local Transaction ID: '
                         ||txnid);
    
    dbms_output.put_line('----- Source Database: '
                         ||source);
    
    dbms_output.put_line('----Error Number: '
                         ||errno);
    
    dbms_output.put_line('----Message Text: '
                         ||errmsg);
    
    FOR i IN 1.. msgcnt LOOP
      dbms_output.put_line('--message: '
                           ||i);
      
      lcr := dbms_apply_adm.get_error_message(i,txnid);
      
      print_lcr(lcr);
    END LOOP;
  END LOOP;
END print_errors;
/

CREATE OR REPLACE PROCEDURE print_transaction
     (ltxnid  IN VARCHAR2)
IS
  i       NUMBER;
  txnid   VARCHAR2(30);
  source  VARCHAR2(128);
  msgcnt  NUMBER;
  errno   NUMBER;
  errmsg  VARCHAR2(128);
  lcr     sys.anydata;
BEGIN
  SELECT local_transaction_id,
         source_database,
         message_count,
         error_number,
         error_message
  INTO   txnid,
         source,
         msgcnt,
         errno,
         errmsg
  FROM   dba_apply_error
  WHERE  local_transaction_id = ltxnid;
  
  dbms_output.put_line('----- Local Transaction ID: '
                       ||txnid);
  
  dbms_output.put_line('----- Source Database: '
                       ||source);
  
  dbms_output.put_line('----Error Number: '
                       ||errno);
  
  dbms_output.put_line('----Message Text: '
                       ||errmsg);
  
  FOR i IN 1.. msgcnt LOOP
    dbms_output.put_line('--message: '
                         ||i);
    
    lcr := dbms_apply_adm.get_error_message(i,txnid); -- gets the LCR
    
    print_lcr(lcr);
  END LOOP;
END print_transaction;
/