create or replace PROCEDURE "P_DATA_TRANSMIT" 
is
    cursor z is SELECT * FROM V_DATA_TRANSMIT;
    z_var   z%rowtype;
    
    l_string VARCHAR2(22767);
    position NUMBER(15);
    k NUMBER(10);
    sql_length NUMBER(10);
    sql_query VARCHAR2(22767);
    query_string VARCHAR2(4000);
    table_name VARCHAR2(4000);
    new_line VARCHAR2(10);
    
BEGIN
  l_string:=lpad(' ',22000,' ');
  execute immediate 'TRUNCATE TABLE DATA_TRANSMIT DROP STORAGE';
  execute immediate 'commit';
  new_line:=chr(13)||chr(13);
  
  open z; fetch z into z_var;
  while z%found loop
    l_string:=z_var.MOD_SQL;
    sql_query:=l_string;
    
    sql_length:=length(sql_query);
    for i in 1..sql_length loop
      if substr(sql_query,k,4)='FROM' or substr(sql_query,k,4)='JOIN' then
        
        table_name:=substr(sql_query,(k+5),instr(substr(sql_query,(k+5)),' ')-1);
        if length(table_name)>2 then
          query_string:='INSERT INTO DATA_TRANSMIT (TABLE_TO,TABLE_FROM,BATCH_TABLE,CUSTOMER_ID) VALUES (:1,:2,:3,:4)';
          
          BEGIN
            execute immediate query_string using z_var.OBJECT_NAME,table_name,z_var.BATCH_TABLE,z_var.CUSTOMER_ID;
            execute immediate 'commit';
           EXCEPTION
            WHEN OTHERS THEN
            dbms_output.put_line(z_var.CUSTOMER_ID);
          END;
        end if;
      end if;
    end loop;
  fetch z into z_var;
  end loop;
  close z;
  BEGIN
    execute immediate 'INSERT INTO DATA_TRANSMIT (TABLE_TO,TABLE_FROM,BATCH_TABLE) SELECT TABLE_TO,TABLE_FROM,PROC_NAME FROM PROC_TRANSMIT';
    execute immediate 'commit';
  END;
END P_DATA_TRANSMIT;