You can generate external table based on the view you used for spool.
For the same p_view_name you do it once and then even if p_timestamp changes for new extracts you can alter existing eternal table:
alter table ext_table location ('new_filename');
If p_view_name changes then you have to re-run table generation script.
Solution I offer will only work if every column in a data file has fixed length across all rows.
First you have to generate a "column list view" - flattened structure containing all declared column names and lengths of the source view.
/* Execute:
sqlplus scott/tiger@orcl1 @gen_column_list_view_ddl.sql
*/
SET SQLBLANKLINES ON
SET PAGESIZE 0 FEEDBACK OFF
SET TRIMSPOOL ON LINE 32000
SET WRAP ON
SET NEWPAGE NONE
SET PAGESIZE 0
SET SPACE 0 LONG 1000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
set termout off
spool GENERATE_EXTERNAL_TABLE/GENERATED/v_column_list.sql
PROMPT set sqlblanklines on
select '
create or replace view v_column_list
as select ' from dual
union all
select ''''||column_name||''' '||column_name||', length('||column_name||') "'||column_name||'_len"'||(case cid when max_cid then '' else ',' end) len
from
(select column_id cid, max(column_id) over() max_cid,column_name
from all_tab_columns
where table_name=:source_view order by column_id)
union all
select '
from '||:source_view||'
where rownum <2' from dual;
PROMPT /
--PROMPT exit;;
spool off
Because you extract without delimiter it means it's fixed length and actual data size is the same for all rows for a given column.
Second step is "column length view" generation. Because declared data length for a given column can differ from actual data length we have to create another view to fix it.
/* Execute:
sqlplus scott/tiger@orcl1 @gen_col_len_view_ddl.sql
*/
SET SQLBLANKLINES ON HEAD OFF
SET PAGESIZE 0 FEEDBACK OFF
SET TRIMSPOOL ON LINE 32000
SET WRAP ON
SET NEWPAGE NONE
SET PAGESIZE 0
SET SPACE 0 LONG 1000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
set termout off
spool GENERATE_EXTERNAL_TABLE/GENERATED/v_col_len.sql
PROMPT set sqlblanklines on
select '
create or replace view v_col_len as
select rn1,col_name,col_len
from( select rownum rn1,value col_name
from (select * from v_column_list
unpivot ( value
for value_type in (' str
from dual
union all
select '"'||column_name||'"'||(case cid when max_cid then '' else ',' end) len
from
(select column_id cid, max(column_id) over() max_cid,column_name
from all_tab_columns
where table_name=:source_view
order by column_id)
union all
select ')
))),
(select rownum rn2, value col_len
from ( select * from v_column_list
unpivot ( value for value_type in ('
from dual
union all
select '"'||column_name||'_len"'||(case cid when max_cid then '' else ',' end) len
from
(select column_id cid, max(column_id) over() max_cid,column_name
from all_tab_columns
where table_name=:source_view
order by column_id)
union all
select ')
)))
where rn1=rn2 ' from dual;
PROMPT /
--PROMPT exit;;
spool off
Now that we know where one column ends and another starts we can generate external table DDL
/* Execute:
sqlplus scott/tiger@orcl1 @gen_external_table_ddl.sql
*/
COLUMN str format a900
set termout on
PROMPT Generating ext table DDL
set termout off
SET SQLBLANKLINES ON
SET PAGESIZE 0
SET TRIMSPOOL ON LINE 32000
SET WRAP ON
SET NEWPAGE NONE
SET PAGESIZE 0
SET SPACE 0
SET LINESIZE 16000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
set timing off
set time off
set headsep off
set termout off
spool GENERATE_EXTERNAL_TABLE/GENERATED/fixed_width_external_table.sql
PROMPT set sqlblanklines on
PROMPT set termout on
PROMPT PROMPT Creating external table
WITH c as (select * from V_COL_LEN)
select 'DROP TABLE '||:ext_table_name||';' str from dual
union all
select '
set termout off
CREATE TABLE '||:ext_table_name||'
( '
str
from dual union all
select col_name||' CHAR('||col_len||')'||(case rn1 when max_rn then '' else ',' end) str from
(
select col_name,col_len, rn1, max(rn1) over() max_rn
from c)
union all
select ' )
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY TEST_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
READSIZE 1048576
FIELDS LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
('
from dual
union all
select col_name||' ('||(CUMTOT-col_len+1)||':'||CUMTOT||') CHAR('||col_len||')'||(case rn1 when max_rn then '' else ',' end) str from (
select col_name,col_len,
SUM(col_len) OVER (ORDER BY rn1) CUMTOT, rn1, max(rn1) over() max_rn
from c)
union all
select ')
)
location
(
'''||:dump_file_name||'''
)
)
'
from dual
/
PROMPT /
--PROMPT exit;;
spool off
To wrap it up you can create standalone script executing all 3 steps.
var dump_file_name varchar2(128);
BEGIN
select 'c_'||export_date||'.dmp' into :dump_file_name
from (select
to_char(sd,'MM')||to_char(sd,'YY') export_date
from (SELECT systimestamp sd FROM DUAL));
END;
/
print :dump_file_name
var ext_table_name varchar2(32);
BEGIN
select 'fixed_width_external_table' into :ext_table_name from dual;
END;
/
print :ext_table_name
var source_view varchar2(32);
BEGIN
select 'V_TEST' into :source_view from dual;
END;
/
print :source_view
PROMPT Generating column list view (V_COLUMN_LIST) based on source view
@GENERATE_EXTERNAL_TABLE/gen_column_list_view_ddl.sql
@GENERATE_EXTERNAL_TABLE/GENERATED/v_column_list.sql
PROMPT Generating column lengths view (V_COL_LEN) based on source view
@GENERATE_EXTERNAL_TABLE/gen_col_len_view_ddl.sql
@GENERATE_EXTERNAL_TABLE/GENERATED/v_col_len.sql
PROMPT Generating external table DDL based on V_COL_LEN
@GENERATE_EXTERNAL_TABLE/gen_external_table_ddl.sql
@GENERATE_EXTERNAL_TABLE/GENERATED/fixed_width_external_table.sql
SET PAGESIZE 99
SET ECHO on
SET FEEDBACK on
SET VERIFY OFF
SET HEADING on
set timing on
set time on
set headsep on
set termout on
var exported_cnt varchar2(32);
BEGIN
EXECUTE IMMEDIATE 'select /*+PARALLEL (t,auto)*/ count(*) rows_exported from '||:ext_table_name||' t' INTO :exported_cnt;
END;
/
print :exported_cnt
This script will create external table DDL from existing view or heap table name in Oracle.
I originally wrote if for regular to external table conversion in Oracle.
