Procedure  

create or replace procedure query_generate_prc(p_list_of_columns in varchar2,p_out out varchar2) is
    cursor columns_cur is
    with main_qry as (select p_list_of_columns as str from dual)
    select type_table_rec(seq_no,tab_column,tab_name)from (select level as seq_no, regexp_substr(str,'[^,]+',1,level) as tab_column,
    substr(regexp_substr(str,'[^,]+',1,level),1,instr(regexp_substr(str,'[^,]+',1,level),'.')-1) tab_name
    from main_qry
    connect by regexp_substr(str,'[^,]+',1,level) is not null);  
    v_nt_tablescolumns  nested_table_rec := nested_table_rec();  
    v_nt_tables         type_table_name := type_table_name();  
    v_search_str        varchar2(4000);
    v_where             varchar2(4000);
    v_flag              number:=0;
    v_key               number:=0; 
    v_request_id        number:=0;
    v_log_seq_id        number:=0;
    PROCEDURE insert_log(p_status VARCHAR2,p_msg VARCHAR2) IS
    BEGIN 
       select query_gen_seq.nextval into v_request_id from dual;
       v_log_seq_id:=v_log_seq_id+1;
       insert into query_generate_log(request_id,seq_id,status,message_tx)
       values(v_request_id,v_log_seq_id,p_status,p_msg);
       commit; 
    END insert_log;
begin 
   insert_log('INFO','p_list_of_columns='||p_list_of_columns);
   open columns_cur;
   fetch columns_cur bulk collect into v_nt_tablescolumns;
   close columns_cur; 
  
   select distinct table_name bulk collect into v_nt_tables
   from table(v_nt_tablescolumns) order by table_name;
  
  
  for i in 1..v_nt_tablescolumns.count loop
    dbms_output.put_line(v_nt_tablescolumns(i).col_seqno||'  '||v_nt_tablescolumns(i).table_col_name||' '||v_nt_tablescolumns(i).table_name);
    select count(*) into v_flag from all_tab_columns where owner='SCOTT'
    and table_name||'.'||column_name=v_nt_tablescolumns(i).table_col_name;
    if v_flag<=0 then
       insert_log('ERROR','Invalid Column :'||v_nt_tablescolumns(i).table_col_name); 
       exit;
    end if;
  end loop; 
  if v_flag<=0 then
    p_out:='ERROR Request ID='||v_request_id;
    return;
  end if;
   
  v_search_str:=null;
  for i in 1..v_nt_tables.count loop
    dbms_output.put_line(v_nt_tables(i));
    if v_search_str is not null then
    v_search_str:=v_search_str||','||v_nt_tables(i);
    else
    v_search_str:=v_nt_tables(i);
    end if;
  end loop; 
  v_search_str:=rtrim(v_search_str,',');
  v_search_str:='CLERK,MANAGER,SALESMAN';
  dbms_output.put_line('v_search_str='||v_search_str);
  
  select join_condition into v_where from query_joins
  where table_name in (v_search_str);
  
  dbms_output.put_line('v_where='||v_where);
  
end query_generate_prc;