I wrote a procedure to extract view contents to a file system in Oracle. Now client want's to test the contents of the file. How do I do it in generic way in Oracle?
Procedure:
CREATE OR REPLACE PROCEDURE PRC_EXTRACT_VIEW (p_view_name VARCHAR2, p_timestamp VARCHAR2)
AUTHID CURRENT_USER
IS
/* Run:
set serveroutput on timing on
begin PRC_EXTRACT_VIEW('V_TEST'); end;
*/
p_query VARCHAR2(512) :='SELECT * FROM '||p_view_name;
p_separator VARCHAR2(8) := '';
p_dir_obj VARCHAR2(126) :='TEST_DIR';
p_filename VARCHAR2(128) :=p_view_name||'_'||p_timestamp||'.csv';
l_output utl_file.file_type;
l_cnt NUMBER := 0;
l_theCursor INTEGER DEFAULT dbms_sql.open_cursor;
l_columnValue VARCHAR2(2000):='';
l_status INTEGER;
l_separator VARCHAR2(10) DEFAULT '';
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
v_sql dbms_sql.varchar2a;
v_sql_1 varchar2(32767);
pn varchar2(32):='PRC_EXTRACT_VIEW';
v_ErrMsg varchar2(2000);
v_Errcode varchar2(60);
v_etl_btch_id number;
v_filename varchar2(256):=p_filename;
BEGIN
l_output := utl_file.fopen( p_dir_obj, v_filename, 'w',32767 );
DBMS_SQL.PARSE(l_theCursor, p_query, DBMS_SQL.NATIVE);
FOR i IN 1 .. 255
LOOP
BEGIN
dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
EXCEPTION
WHEN OTHERS THEN
IF ( SQLCODE = -1007 ) THEN
EXIT;
ELSE
raise;
END IF;
END;
END LOOP;
dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
d := DBMS_SQL.EXECUTE(l_theCursor);
DBMS_SQL.DESCRIBE_COLUMNS(l_theCursor, col_cnt, rec_tab);
LOOP
EXIT
WHEN ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
FOR i IN 1 .. col_cnt
LOOP
dbms_sql.column_value( l_theCursor, i, l_columnValue );
--dbms_output.put_line(l_columnValue);
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := p_separator;
END LOOP;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
END LOOP;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
/*--TODO
EXCEPTION
WHEN OTHERS THEN
*/
END PRC_EXTRACT_VIEW;
/
Client is tipsy about what went into a file. Is there any way to read contents of dump file back in the same or new session? (for test purposes)
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.