Document ID:        33518.1
Subject:            Script to Print Out Arguments of Transactions in Deferror
Author:             TOSCROFT
Modified:           31 Jan 96 


rem - This procedure will print out the arguments to a deferred
rem - transactions which have failed to propagate to the remote site for rem - 
some reason and have so been written to the table deferror.
rem
rem - The person compiling and running this procedure should have select
rem - permission on deferror and defcall.

create or replace procedure print_err as

      argno         int;
      ptype         int;
 
      numb_arg      number;
      vchar2_arg    varchar2(2000);
      rowid_arg     rowid;
      date_arg      date;
      raw_arg       raw(2000); 
      char_arg      char(2000);

      cursor xa is  select  deferred_tran_id, error_msg
                    from    sys.deferror;

      cursor ca (tran_id varchar2) is
  
                    select  argcount, callno, schemaname, packagename, 
                            deferred_tran_db, procname 
                    from    sys.defcall
                    where   deferred_tran_id = tran_id;

begin
      dbms_output.enable (16000);

      -- get all transactions in error

      for xa_rec in xa loop

          -- process details of each transaction 

          for ca_rec in ca (xa_rec.deferred_tran_id) loop

              -- output general xa details

              dbms_output.put_line ('.');
              dbms_output.put_line ('transaction  ' || xa_rec.deferred_tran_id);
              dbms_output.put_line ('initiated at ' || ca_rec.deferred_tran_db);
              dbms_output.put_line ('.');
              dbms_output.put_line ('schema       ' || ca_rec.schemaname);
              dbms_output.put_line ('package      ' || ca_rec.packagename);
              dbms_output.put_line ('procedure    ' || ca_rec.procname);
              dbms_output.put_line ('.');
              dbms_output.put_line ('parameters:  ');

              -- loop through the arguments

              for argno in 1 .. ca_rec.argcount loop

                  ptype := dbms_defer_query.get_arg_type (
                                                ca_rec.callno, 
                                                ca_rec.deferred_tran_db, 
                                                argno);

                  if ptype = 1 then
                      vchar2_arg := dbms_defer_query.get_varchar2_arg (
                                                       ca_rec.callno, 
                                                       ca_rec.deferred_tran_db, 
                                                       argno);
 
                      dbms_output.put_line ('.            ' || vchar2_arg);
                  elsif ptype = 2 then
                      numb_arg := dbms_defer_query.get_number_arg (
                                                       ca_rec.callno, 
                                                       ca_rec.deferred_tran_db, 
                                                       argno);

                      dbms_output.put_line ('.            ' || numb_arg);
                  elsif ptype = 11 then
                      rowid_arg := dbms_defer_query.get_rowid_arg (
                                                       ca_rec.callno, 
                                                       ca_rec.deferred_tran_db, 
                                                       argno);
 
                      dbms_output.put_line ('.            ' || rowid_arg);
                  elsif ptype = 12 then
                      date_arg := dbms_defer_query.get_date_arg (
                                                       ca_rec.callno, 
                                                       ca_rec.deferred_tran_db, 
                                                       argno);
 
                      dbms_output.put_line ('.            ' || date_arg);
                  elsif ptype = 23 then
                      raw_arg := dbms_defer_query.get_raw_arg (
                                                       ca_rec.callno, 
                                                       ca_rec.deferred_tran_db, 
                                                       argno);
 
                      dbms_output.put_line ('.            ' || raw_arg);
                  elsif ptype = 96 then
                      char_arg := dbms_defer_query.get_char_arg (
                                                       ca_rec.callno, 
                                                       ca_rec.deferred_tran_db, 
                                                       argno);
 
                      dbms_output.put_line ('.            ' || char_arg);
                  end if;
              end loop;
          end loop;

          dbms_output.put_line ('.');
          dbms_output.put_line (xa_rec.error_msg); 
          dbms_output.put_line ('.');
          dbms_output.put ('------------------------------------------');
          dbms_output.put_line ('-------------------------------------');
      end loop; 
end;
/