|
|
|
|
In response to a post by David Curley asking for a "procedure to print any cursor passed to it via DBMS_OUTPUT and, write out a comma-delimited file", I suggested an alternative print_csv procedure, that would do the same thing for any query passed to it, rather than any cursor passed to it, and David's reply indicated that it met his needs.
The initial version of the print_csv procedure that I posted was a modification of a dump_csv function by Tom Kyte that uses utl_file to actually output the results of any query to a file. I simply modified it to use dbms_output instead of utl_file and changed it from a function to a procedure.
Following some suggestions from James Padfield and Solomon Yakobson, I made some additional modifications regarding the handling of the date format, and exception section.
Below is the final version of the procedure, followed by a simple demonstration of its usage, using the Oracle emp and dept demo tables.
CREATE OR REPLACE PROCEDURE print_csv
(p_query IN VARCHAR2,
p_delimiter IN VARCHAR2 DEFAULT ',',
p_date_format IN VARCHAR2 DEFAULT NULL)
IS
c_1
INTEGER
DEFAULT DBMS_SQL.OPEN_CURSOR;
v_column_value VARCHAR2 (2000);
v_column_count NUMBER
DEFAULT 0;
v_status
INTEGER;
v_delimiter VARCHAR2 (10)
DEFAULT '';
v_output
VARCHAR2 (2000) := NULL;
v_count
NUMBER
DEFAULT 0;
v_orig_date_format VARCHAR2 (100) := NULL;
BEGIN
IF p_date_format IS NOT NULL
THEN
SELECT value
INTO v_orig_date_format
FROM v$nls_parameters
WHERE parameter = 'NLS_DATE_FORMAT';
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = '''
|| p_date_format || '''';
END IF;
DBMS_SQL.PARSE (c_1, p_query, DBMS_SQL.NATIVE);
FOR i IN 1 .. 255
LOOP
BEGIN
DBMS_SQL.DEFINE_COLUMN (c_1, i, v_column_value,
2000);
v_column_count := i;
EXCEPTION
WHEN OTHERS THEN
IF (sqlcode = -1007)
THEN
EXIT;
ELSE
RAISE;
END IF;
END;
END LOOP;
DBMS_SQL.DEFINE_COLUMN (c_1, 1, v_column_value, 2000);
v_status := DBMS_SQL.EXECUTE (c_1);
LOOP
EXIT WHEN (DBMS_SQL.FETCH_ROWS (c_1) <= 0);
v_delimiter := '';
v_output := NULL;
FOR i IN 1 .. v_column_count
LOOP
DBMS_SQL.COLUMN_VALUE (c_1, i, v_column_value);
v_output := v_output || v_delimiter ||
v_column_value;
v_delimiter := p_delimiter;
END LOOP;
DBMS_OUTPUT.PUT_LINE (v_output);
v_count := v_count + 1;
END LOOP;
DBMS_SQL.CLOSE_CURSOR (c_1);
IF p_date_format IS NOT NULL
AND v_orig_date_format IS NOT NULL
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = '''
|| v_orig_date_format || '''';
END IF;
EXCEPTION
WHEN OTHERS THEN
IF p_date_format IS NOT NULL
AND v_orig_date_format IS NOT NULL
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET
NLS_DATE_FORMAT = '''
|| v_orig_date_format || '''';
END IF;
RAISE;
END print_csv;
/
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE print_csv ('SELECT e.ename,e.hiredate,d.loc FROM emp e,dept
d
WHERE e.deptno=d.deptno', ',', 'DD-MON-YYYY')
SMITH,17-DEC-1980,DALLAS
ALLEN,20-FEB-1981,CHICAGO
WARD,22-FEB-1981,CHICAGO
JONES,02-APR-1981,DALLAS
MARTIN,28-SEP-1981,CHICAGO
BLAKE,01-MAY-1981,CHICAGO
CLARK,09-JUN-1981,NEW YORK
SCOTT,09-DEC-1982,DALLAS
KING,17-NOV-1981,NEW YORK
TURNER,08-SEP-1981,CHICAGO
ADAMS,12-JAN-1983,DALLAS
JAMES,03-DEC-1981,CHICAGO
FORD,03-DEC-1981,DALLAS
MILLER,23-JAN-1982,NEW YORK
PL/SQL procedure successfully completed.
