-- --------------------------------------------------------------------------------------------------- -- -- Script: data_dump.sql -- -- Author: Adrian Billington -- www.oracle-developer.net -- -- Description: A standalone procedure to dump the results of a query to delimited flat-file. This -- utility supports Oracle 8i upwards. -- -- Note that the dynamic code that is built to perform the data dump can optionally be -- written to a separate file. -- -- Usage: Usage is quite simple. A dynamic query is passed in as a parameter. As this uses -- DBMS_SQL to parse the SQL, all expressions must have an alias. -- -- a) Dump the contents of a table -- ---------------------------- -- -- BEGIN -- data_dump( query_in => 'SELECT * FROM table_name', -- file_in => 'table_name.csv', -- directory_in => 'LOG_DIR', -- delimiter_in => ',' ); -- END; -- / -- -- b) Use an expression in the query -- ------------------------------ -- -- BEGIN -- data_dump( query_in => 'SELECT ''LITERAL'' AS alias_name FROM table_name', -- file_in => 'table_name.csv', -- directory_in => 'LOG_DIR', -- delimiter_in => ',' ); -- END; -- / -- -- See list of parameters for the various other options available. -- -- --------------------------------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE data_dump ( query_in IN VARCHAR2, file_in IN VARCHAR2, directory_in IN VARCHAR2, nls_date_fmt_in IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS', write_action_in IN VARCHAR2 DEFAULT 'W', array_size_in IN PLS_INTEGER DEFAULT 1000, delimiter_in IN VARCHAR2 DEFAULT NULL, dump_code_in IN BOOLEAN DEFAULT FALSE) AUTHID CURRENT_USER IS v_fh UTL_FILE.FILE_TYPE; v_ch BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR; v_sql VARCHAR2(32767) := query_in; v_dir VARCHAR2(512) := directory_in; v_outfile VARCHAR2(128) := file_in; v_sqlfile VARCHAR2(128) := file_in||'.sql'; v_arr_size PLS_INTEGER := array_size_in; v_col_cnt PLS_INTEGER := 0; v_delimiter VARCHAR2(1) := NULL; v_write_action VARCHAR2(1) := write_action_in; v_nls_date_fmt VARCHAR2(30) := nls_date_fmt_in; v_dummy NUMBER; v_type VARCHAR2(8); t_describe DBMS_SQL.DESC_TAB; t_plsql DBMS_SQL.VARCHAR2S; /* Procedure to output code for debug and assign plsql variable... */ PROCEDURE put ( string_in IN VARCHAR2 ) IS BEGIN IF dump_code_in THEN UTL_FILE.PUT_LINE(v_fh,string_in); END IF; t_plsql(t_plsql.COUNT + 1) := string_in; EXCEPTION WHEN OTHERS THEN RAISE; END put; BEGIN /* Open the file that the dynamic PL/SQL will be written to for debug... */ IF dump_code_in THEN v_fh := UTL_FILE.FOPEN(v_dir, v_sqlfile, 'W'); END IF; /* Parse the query that will be used to fetch all the data to be written out... */ DBMS_SQL.PARSE(v_ch, v_sql, DBMS_SQL.NATIVE); /* Now describe the dynamic SQL to analyze the number of columns in the query... */ DBMS_SQL.DESCRIBE_COLUMNS(v_ch, v_col_cnt, t_describe); /* Now begin the dynamic PL/SQL... */ put('DECLARE'); put(' v_fh UTL_FILE.FILE_TYPE;'); put(' CURSOR cur_sql IS'); put(' '||REPLACE(v_sql,'"','''''')||';'); /* Now loop through the describe table to declare arrays in the dynamic PL/SQL... */ FOR i IN t_describe.FIRST .. t_describe.LAST LOOP IF t_describe(i).col_type = 2 THEN v_type := 'NUMBER'; ELSIF t_describe(i).col_type = 12 THEN v_type := 'DATE'; ELSE v_type := 'VARCHAR2'; END IF; put(' '||t_describe(i).col_name||' DBMS_SQL.'||v_type||'_TABLE;'); END LOOP; /* Syntax to set the date format to preserve time in the output, open the out file and start to collect... */ put('BEGIN'); put(' EXECUTE IMMEDIATE ''ALTER SESSION SET NLS_DATE_FORMAT = '''''||v_nls_date_fmt||''''''';'); put(' v_fh := UTL_FILE.FOPEN('''||v_dir||''','''||v_outfile||''','''||v_write_action||''');'); put(' OPEN cur_sql;'); put(' LOOP'); put(' FETCH cur_sql'); IF t_describe.COUNT > 1 THEN put(' BULK COLLECT INTO '||t_describe(t_describe.FIRST).col_name||','); /* Add all other arrays into the fetch list except the last... */ FOR i IN t_describe.FIRST + 1 .. t_describe.LAST - 1 LOOP put(' '||t_describe(i).col_name||','); END LOOP; /* Add in the last array and limit... */ put(' '||t_describe(t_describe.LAST).col_name||' LIMIT '||v_arr_size||';'); ELSE /* Just output the one collection and LIMIT... */ put(' BULK COLLECT INTO '||t_describe(t_describe.FIRST).col_name||' LIMIT '||v_arr_size||';'); END IF; /* Now add syntax to loop though the fetched array and write out the values to file... */ put(' IF '||t_describe(t_describe.FIRST).col_name||'.COUNT > 0 THEN'); put(' FOR i IN '||t_describe(t_describe.FIRST).col_name||'.FIRST .. '|| t_describe(t_describe.FIRST).col_name||'.LAST LOOP'); FOR i IN t_describe.FIRST .. t_describe.LAST LOOP put(' UTL_FILE.PUT(v_fh,'''||v_delimiter||''' ||'||t_describe(i).col_name||'(i));'); v_delimiter := NVL(delimiter_in,','); END LOOP; /* Add a new line marker into the file and move on to next record... */ put(' UTL_FILE.NEW_LINE(v_fh);'); put(' END LOOP;'); /* Complete the IF statement... */ put(' END IF;'); /* Add in an EXIT condition and complete the loop syntax... */ put(' EXIT WHEN cur_sql%NOTFOUND;'); put(' END LOOP;'); put(' CLOSE cur_sql;'); put(' UTL_FILE.FCLOSE(v_fh);'); /* Add in some exception handling... */ put('EXCEPTION'); put(' WHEN UTL_FILE.INVALID_PATH THEN'); put(' DBMS_OUTPUT.PUT_LINE(''Error - invalid path.'');'); put(' RAISE;'); put(' WHEN UTL_FILE.INVALID_MODE THEN'); put(' DBMS_OUTPUT.PUT_LINE(''Error - invalid mode.'');'); put(' RAISE;'); put(' WHEN UTL_FILE.INVALID_OPERATION THEN'); put(' DBMS_OUTPUT.PUT_LINE(''Error - invalid operation.'');'); put(' RAISE;'); put(' WHEN UTL_FILE.INVALID_FILEHANDLE THEN'); put(' DBMS_OUTPUT.PUT_LINE(''Error - invalid filehandle.'');'); put(' RAISE;'); put(' WHEN UTL_FILE.WRITE_ERROR THEN'); put(' DBMS_OUTPUT.PUT_LINE(''Error - write error.'');'); put(' RAISE;'); put(' WHEN UTL_FILE.READ_ERROR THEN'); put(' DBMS_OUTPUT.PUT_LINE(''Error - read error.'');'); put(' RAISE;'); put(' WHEN UTL_FILE.INTERNAL_ERROR THEN'); put(' DBMS_OUTPUT.PUT_LINE(''Error - internal error.'');'); put(' RAISE;'); put(' WHEN OTHERS THEN'); put(' DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,255));'); put(' RAISE;'); put('END;'); /* Now close the cursor and sql file... */ DBMS_SQL.CLOSE_CURSOR(v_ch); IF dump_code_in THEN UTL_FILE.FCLOSE(v_fh); END IF; /* * Execute the t_plsql collection to dump the data. Use DBMS_SQL as we have a collection * of syntax... */ v_ch := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_ch, t_plsql, t_plsql.FIRST, t_plsql.LAST, TRUE, DBMS_SQL.NATIVE); v_dummy := DBMS_SQL.EXECUTE(v_ch); DBMS_SQL.CLOSE_CURSOR(v_ch); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE('Error - invalid path.'); RAISE; WHEN UTL_FILE.INVALID_MODE THEN DBMS_OUTPUT.PUT_LINE('Error - invalid mode.'); RAISE; WHEN UTL_FILE.INVALID_OPERATION THEN DBMS_OUTPUT.PUT_LINE('Error - invalid operation.'); RAISE; WHEN UTL_FILE.INVALID_FILEHANDLE THEN DBMS_OUTPUT.PUT_LINE('Error - invalid filehandle.'); RAISE; WHEN UTL_FILE.WRITE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error - write error.'); RAISE; WHEN UTL_FILE.READ_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error - read error.'); RAISE; WHEN UTL_FILE.INTERNAL_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error - internal error.'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,255)); RAISE; END; / CREATE OR REPLACE PUBLIC SYNONYM data_dump FOR data_dump; GRANT EXECUTE ON data_dump TO PUBLIC;