DBA External Table Creation

Description
Categories: Enginatics
Repository: Github Columns: Text ...
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
Parameter Name SQL text Validation
Directory
 
LOV
Source Table
:source_table=:source_table
LOV
Source SQL
 
Source File Name
 
External Table Name
 
Write Log and Badfile
nobadfile
nologfile
LOV Oracle