Un amico mi ha chiamato e mi ha chiesto come funziona DBMS_SQL allora ho riscoperto due vecchie procedure e funzioni in cui si utilizza questo package per effettuare lo spool su file di una select oppure di una intera tabella.
Visto che mi piace condividere di seguito il codice:
Dobbiamo creare 3 funzioni ed una procedura di seguito i nomi:
- Funzione IS_NUMBER_CHAR
- Funzione ISDATE
- Funzione DUMP_CSV
- Procedura DUMP_TABLE_TO_CSV
Le prime due sono solo di contorno e vengono utilizzate nella procedura per capire se il contenuto del campo di cui stiamo per fare lo spoo sia Number, Varchar o Date a seconda della tipologia l'output viene scritto in modo diverso.
CREATE OR REPLACE FUNCTION is_number_char(
p_value VARCHAR2)
RETURN NUMBER
-- 0 Alpha
-- 1 Number
-- 3 Errore
AS
n_num NUMBER :=3;
v_num varchar2(30):=NULL;
BEGIN
SELECT
CASE
WHEN INSTR(p_value,'-')>1
THEN 'alpha'
WHEN p_value LIKE '%.%.%'
THEN 'alpha'
WHEN p_value LIKE '-%-%'
THEN 'alpha'
WHEN REGEXP_LIKE(p_value,'^[-0-9.,]*$')
THEN 'numeric'
ELSE 'alpha'
END is_numeric
INTO v_num
FROM dual;
IF v_num='alpha' THEN n_num:=0; elsif v_num='numeric' THEN n_num:=1; END IF;
RETURN n_num;
exception
WHEN OTHERS THEN
n_num:=3;
RETURN n_num;
END;
CREATE OR REPLACE FUNCTION isdate(
p_string IN VARCHAR2,
p_fmt IN VARCHAR2 := 'yyyy-mm-dd hh24:mi:ss' )
RETURN NUMBER
AS
l_date DATE;
BEGIN
l_date :=to_date(p_string,p_fmt);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN 1;
END;
A questo punto vi incollo il codice della procedura per lo spool del contenuto di una tabella, che riceve in input il nome della tabella, la directory di spool ed il nome del file.
CREATE OR REPLACE PROCEDURE dump_table_to_csv(
p_tname IN VARCHAR2,
p_dir IN VARCHAR2,
p_filename IN VARCHAR2 )
IS
l_output utl_file.file_type;
l_theCursor INTEGER DEFAULT dbms_sql.open_cursor;
l_columnValue VARCHAR2(4000);
l_status INTEGER;
l_query VARCHAR2(1000) DEFAULT 'select * from ' || p_tname;
l_colCnt NUMBER := 0;
l_separator VARCHAR2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;
L_QUOTE VARCHAR2(1);
BEGIN
L_OUTPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'w', 32760);
-- Altero il formato data per lo spool
EXECUTE immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );
FOR i IN 1 .. l_colCnt
LOOP
utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );
DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
l_separator := ';';
END LOOP;
utl_file.new_line( l_output );
l_status := dbms_sql.execute(l_theCursor);
WHILE ( dbms_sql.fetch_rows(l_theCursor) > 0 )
LOOP
l_separator := '';
FOR i IN 1 .. l_colCnt
LOOP
DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
-- Verifico la tipologia del contenuto del campo
IF IS_NUMBER_CHAR(l_columnValue) =0 AND ISDATE(l_columnValue) =0 THEN
l_quote := '"';
-- Se di tipo Varchar lo metto tra doppi apici
l_columnValue := REPLACE(l_columnValue,'"','""');
ELSE
L_QUOTE := '';
END IF;
UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || L_QUOTE || L_COLUMNVALUE || L_QUOTE);
l_separator := ';';
END LOOP;
utl_file.new_line( l_output );
END LOOP;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
-- Altero il formato rimettendolo come era prima
EXECUTE immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
EXCEPTION
WHEN OTHERS THEN
EXECUTE immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;
END;
Per lanciarlo possiamo usare:
DECLARE
P_TNAME VARCHAR2(200);
P_DIR VARCHAR2(200);
P_FILENAME VARCHAR2(200);
BEGIN
P_TNAME := 'GAIA_FILE1_STEP1_WORK';
P_DIR := 'DATA_PUMP_DIR';
P_FILENAME := 'GAIA_FILE1_STEP1_WORK.LOg';
DUMP_TABLE_TO_CSV(
P_TNAME => P_TNAME,
P_DIR => P_DIR,
P_FILENAME => P_FILENAME
);
--rollback;
END;
Adesso vediamo l'utilizzo del DBMS_SQL per lo spool fornendo in input una select.
Questa non è opera mia e non ricordo nemmeno più dove abbia recuperato questa funzione di spool, però come si suol dire funziona. Forse da AskTom ma non ricordo, risale ad almeno dieci anni fà.
create or replace function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2,
p_header IN VARCHAR2,
p_endline IN VARCHAR2 DEFAULT chr(13))
-- This routine makes certain assumptions.
-- 1) There must be a query and it can't be greater then 32K.
-- 2) The separator must only be one character in length and can't be
-- a CR, LF, binary 0, or null (easy to change).
-- 3) If the p_dir parameter is null, the p_filename must contain the
-- path and filename (/tmp/output.txt)
-- 4) If the p_header parameter is not null, then insert it into the first
-- row of the output file. If the p_separator parameter is not a comma,
-- the comma's in the header string will be replaced with the new
-- separator. so to add a header use 'NAME,FIRST_NAME,LAST_NAME' and if
-- the separator is a tab, what is put into the file would be
-- 'NAME<tab>FIRST_NAME<tab>LAST_NAME'
-- 5) The value of p_endline will be appended to the end of each line of the
-- output file. It can be used to add a carriage return before the
-- Line Feed is inserted by the NEW_LINE (unix). If the server is running
-- on a windows machine, set this to null since the NEW_LINE will save
-- a CR,LF pair anyway. This can also be used if you needed to put
-- something at the end. For exanple "'|'||CHR(13)" which would put a
-- vertical bar and CR,LF on each line on a unix machine.
--
-- The following are the returned error codes
-- -1 The query is empty
-- -2 The output filename is empty
-- -3 The separator is invalid.
-- -4 The filename only contains the path, no filename specified.
-- -5 The output file can not be opened.
-- -6 The query could not be parsed. It was illegal.
-- 0 The query returned NO records.
-- >0 The number of records returned.
return number
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(20000000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
l_dir VARCHAR2(500);
l_filename VARCHAR2(32);
x_pnt NUMBER(4);
l_header VARCHAR2(200);
begin
-- sanity check the input
IF p_query IS NULL THEN
RETURN(-1);
END IF;
IF p_filename IS NULL THEN
RETURN(-2);
END IF;
-- Do not allow CR, LF,binary 0, or null to be used as a separator.
-- The length of the separator must be 1 if it exists.
IF p_separator IS NULL OR
p_separator IN (chr(13),chr(10),chr(0)) OR
length(p_separator) > 1 THEN
RETURN(-3);
END IF;
-- If the directory parameter is blank, assume that the directory
-- is included in the filename.
IF p_dir IS NOT NULL THEN
l_dir := p_dir;
l_filename := p_filename;
ELSE
x_pnt := instr(p_filename,'/',-1,1);
-- If no path is specified or no filename is specified,
-- the procedure will not work... get out.
IF x_pnt = 0 OR x_pnt = length(p_filename) THEN
RETURN(-4);
END IF;
l_dir := substr(p_filename,1,x_pnt-1);
l_filename := substr(p_filename,x_pnt+1);
END IF;
-- Check to see if the file can be opened. If ANY error is
-- encountered, exit with a count of -1;
BEGIN
l_output := utl_file.fopen( l_dir, l_filename, 'w', 32767 );
EXCEPTION
WHEN OTHERS THEN
RETURN(-5);
END;
-- Check to see if the query can be processed. if ANY error is
-- encountered, close the output file and exit.
BEGIN
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
EXCEPTION
WHEN OTHERS THEN
utl_file.fclose( l_output );
RETURN(-6);
END;
-- If the p_header parameter is not null, then insert the line as
-- the first line in the output file. This is used if the user wants
-- to insert column headings. Make sure to use a comma in your header
-- line and the routine will replace all comma;s with the specified
-- separator.
l_header := NULL;
IF p_header IS NOT NULL THEN
l_header := p_header;
IF p_separator <> ',' THEN
l_header := REPLACE(l_header,',',p_separator);
END IF;
END IF;
-- Loop through all the parameters for the select. To support
-- unknown querys, the assumption is that the query will return
-- all columns as varchar2 columns where the data is correctly
-- formatted for inport. A maximum of 255 columns are supported
-- in the query. Each column can't be greater then 2000
-- characters in length.
for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;
-- This define_column insures that at least one column is defined for the
-- routine.
dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
-- Fire the query.
l_status := dbms_sql.execute(l_theCursor);
-- Loop through all the rows returned by the query. Build up the output file
-- by looping through the defined columns.
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
IF l_cnt = 0 AND l_header IS NOT NULL THEN
utl_file.put(l_output, l_header);
utl_file.put(l_output, p_endline);
UTL_FILE.NEW_LINE (l_output,1);
l_cnt := 1;
END IF;
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.put(l_output, p_endline);
UTL_FILE.NEW_LINE (l_output,1);
l_cnt := l_cnt+1;
end loop;
-- Processing done. close the cursor and output file.
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
-- Return the number of rows built in the csv file.
return l_cnt;
-- If Any error occures outside of the errors checked above, then raise
-- and error and blow out the procedure.
EXCEPTION
WHEN OTHERS THEN
RAISE;
end dump_csv;
Per lanciarla basta eseguire quanto indicato di seguito, ha però qualche problemino nella generazione dell'header. Forse per questo non è mai stata usata.
DECLARE
P_QUERY VARCHAR2(20000);
P_SEPARATOR VARCHAR2(200);
P_DIR VARCHAR2(200);
P_FILENAME VARCHAR2(200);
P_HEADER VARCHAR2(200);
P_ENDLINE VARCHAR2(200);
v_Return NUMBER;
BEGIN
P_QUERY := 'SELECT SOCIETA, SETT_CON, SOCIETA_CONTROPARTE,
IMPORTO, IMPONIBILE, IVA_COSTO, BOLLO,
SCENARIO, CUSTOM1, CUSTOM2, CUSTOM3, CUSTOM4,
FROM GAIA_FILE1_STEP1_WORK';
P_SEPARATOR := ',';
P_DIR := 'DATA_PUMP_DIR';
P_FILENAME := 'GAIA_FILE1_STEP1_WORK.CSV';
P_HEADER := '######';
P_ENDLINE := 'chr(13)';
v_Return := DUMP_CSV(
P_QUERY => P_QUERY,
P_SEPARATOR => P_SEPARATOR,
P_DIR => P_DIR,
P_FILENAME => P_FILENAME,
P_HEADER => P_HEADER,
P_ENDLINE => P_ENDLINE
);
DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
:v_Return := v_Return;
--rollback;
END;
Finito.. a Paole.. vanno bene come esempi?
Dimenticavo altre info le trovate qui:
