ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B3AC323B700A2741E0530100007F08E3 DBA Directory Y select dd.directory_name value, dd.directory_path description from dba_directories dd order by dd.directory_path, dd.directory_name US B37FE3F66E03ADADE0530100007F63BB select 'drop table '||:external_table||'; create table '||:external_table||' ( '||z.text1|| ' ) organization external ( default directory '||:default_directory||' access parameters ( records delimited by ''\r\n'' characterset we8mswin1252 skip 1 &write_log nodiscardfile fields terminated by '','' optionally enclosed by ''"'' missing field values are null ( '||z.text2|| ' ) ) location ('''||:file_name||''') );' text from ( select distinct listagg(y.text1,chr(10)) within group (order by y.column_id) over () text1, listagg(y.text2,chr(10)) within group (order by y.column_id) over () text2 from ( select x.column_id, lower(x.column_name||' '||x.data_type)||decode(x.data_type,'VARCHAR2','('||x.data_length||')')||decode(max(x.column_id) over (),x.column_id,null,',') text1, lower(x.column_name)||case when x.data_type='DATE' then ' date "DD-MON-RR HH24:MI:SS"' when x.data_type='VARCHAR2' then ' char('||x.data_length||')' when x.data_type in ('LONG','CLOB') then ' char(60000)' end||decode(max(x.column_id) over (),x.column_id,null,',') text2 from table(xxen_util.sql_columns(:sql_statement)) x ) y ) z where 1=1 Y US DBA External Table Creation Enginatics &write_log 1=1 :default_directory :external_table :file_name :sql_statement 1 10 :default_directory LOV DBA Directory B3AC323B700A2741E0530100007F08E3 Y select dd.directory_name value, dd.directory_path description from dba_directories dd order by dd.directory_path, dd.directory_name XTERN Y US Directory 2 20 1=1 :source_table=:source_table LOV custom Y select at.table_name value, at.owner description from all_tables at where at.owner not like '%SYS%' or at.owner='APPLSYS' order by value Y US Source Table 3 30 :sql_statement Char select 'select * from '||lower(:$flex$.source_table) text from dual where :$flex$.source_table is not null Y US Source SQL 4 40 :file_name Char lower(:$flex$.source_table)||'.csv' Y US Source File Name 5 50 :external_table Char select 'xx_'||lower(replace(regexp_replace(:$flex$.source_table, '([^_]{1})[^_]*','\1'),'_')) from dual Y US External Table Name 6 60 &write_log nobadfile nologfile LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N Y Y US Write Log and Badfile :$flex$.source_table Source Table External Table Name :$flex$.source_table Source Table Source File Name :$flex$.source_table Source Table Source SQL