CREATE OR REPLACE TRIGGER trig_table1
   BEFORE INSERT OR UPDATE
   ON TABLE1
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
     v_exists    NUMBER ;

   
BEGIN

   IF UPDATING AND :new.flag = 'Y' THEN

      IF UPDATING ( 'column1' ) THEN
         log_procedure (:new.lnum, 'column1', :old.column1
                           , :new.column1);
      END IF;

END;

---************-------------

CREATE OR REPLACE PROCEDURE log_procedure (
   inloannum        IN     number
 , inchangetype     IN     varchar2(100)
 , inprevvalue      IN     log_table.prev_value%TYPE
 , innewvalue       IN     log_table.new_value%TYPE
 , inchangedate     IN     log_table.change_date%TYPE := SYSDATE
)
IS

   --
   v_date        log_table.change_date%TYPE   := inchangedate;
   v_count       NUMBER                             := 0;
   v_errc        NUMBER;
   v_errm        VARCHAR2 (255);
    v_proc_name   job_status.NAME%TYPE;
BEGIN
 
   IF NVL (inprevvalue, '*') != NVL (innewvalue, '*')
   THEN
      INSERT INTO log_table
                  (lnum, change_type, change_date, prev_value
                 , new_value, processor
                  )
           VALUES (inloannum, inchangetype, v_date, inprevvalue
                 , innewvalue, USER
                  );
   END IF;

EXCEPTION
   WHEN OTHERS
   THEN
      --v_errm := SUBSTR (SQLERRM, 1, 50);
           RAISE;
END log_procedure;
/

PROCEDURE p_get (myrec        IN OUT master_c%ROWTYPE,
                 out_flag        OUT VARCHAR2,
                 p_arm_code      OUT VARCHAR2)
IS
   v_dummy_date   DATE;

   v_oappr        table1.oappr%TYPE;
   v_osale        table1.osale%TYPE;
   v_odate        table1.odate%TYPE;                                  
   v_temp_oltv    table1.oltv%TYPE;
   v_obal         table1.obal%TYPE;
   v_denom        NUMBER := 0;
BEGIN
   SELECT ROUND (col1, 5),                       
          ROUND (mybal, 2),                                  
          oltv,
          DECODE (NVL (a_term, 0), 0, myterm, a_term)         
     INTO myrec.col1,                          
          v_obal,                                       
          v_temp_oltv,
          myrec.oterm
     FROM table1
    WHERE lnum = myrec.lnum;

   p_arm_code := NULL;
   out_flag := NULL;

   myrec.oltv := v_temp_oltv;

   IF v_temp_oltv < 0.65
   THEN
      myrec.oltv := v_temp_oltv * 10;
   END IF;

   IF v_temp_oltv < 0.01
   THEN                                           
      myrec.oltv := v_temp_oltv * 100;
   END IF;

END;

set time on
set serverout on size 1000000
set array 100
set maxdata 60000
declare

    l_query       varchar2(4000);
    l_file_name   varchar2(100);
    l_directory   varchar2(20) := 'DIR_NAME';
    
procedure out_to_csv( p_query     in varchar2,
                          p_dir       in varchar2,
                          p_filename  in varchar2,
                          p_separator in varchar2)
  is
      l_output        utl_file.file_type;
      l_theCursor     integer default dbms_sql.open_cursor;
      l_columnValue   varchar2(4000);
      l_status        integer;
      l_query         varchar2(32767) := p_query;
      l_colCnt        number := 0;
      l_separator     varchar2(1);
      l_descTbl       dbms_sql.desc_tab;
  begin
      l_output := utl_file.fopen( p_dir, p_filename, 'W', 32767 );
      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
  
      for i in 1 .. l_colCnt loop
          utl_file.put( l_output, l_separator || lower(l_descTbl(i).col_name)  );
          dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
          l_separator := nvl(p_separator,',');
      end loop;
      utl_file.new_line( l_output );
  
      l_status := dbms_sql.execute(l_theCursor);
  
      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
          l_separator := '';
          for i in 1 .. l_colCnt loop
              dbms_sql.column_value( l_theCursor, i, l_columnValue );
              utl_file.put( l_output, l_separator || l_columnValue );
              l_separator :=  nvl(p_separator,',');
          end loop;
          utl_file.new_line( l_output );
      end loop;
      dbms_sql.close_cursor(l_theCursor);
      utl_file.fclose( l_output );
--  exception
--      when others then raise;
  end p_query_to_csv;

begin
  
     for curs_ir2 in(select table_name from dba_tables where table_name like 'TAB_NAME%')
     loop
     
	 l_query := 'select * from '||curs_ir2.table_name;
	 
	 select lower(curs_ir2.table_name||'.txt')
	 into l_file_name
	 from dual;
	 
	 out_to_csv( l_query ,l_directory, l_file_name,'|');    
     end loop;
  
end;
/