prc_table_to_csv

PROCEDURE prc_table_to_csv (p_table_name VARCHAR2,
p_sep_char VARCHAR2 DEFAULT '","',
p_filename VARCHAR2 DEFAULT 'Output.txt',
p_file_location VARCHAR2
)
--
IS

CURSOR cur_table_data
IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = p_table_name;

v_line VARCHAR2(20000);
v_sep VARCHAR2(30);

file_handle UTL_FILE.FILE_TYPE;


TYPE TabCurTyp IS REF CURSOR;
v_TabCurTyp TabCurTyp;

BEGIN

v_sep := '||''' || p_sep_char|| '''||';
dbms_output.put_line(v_sep);

FOR cur_table_data_rec IN cur_table_data
LOOP
v_line := v_line || cur_table_data_rec.column_name || v_sep;
END LOOP;

v_line := SUBSTR(v_line, 1, LENGTH(v_line) - (6 + LENGTH(p_sep_char)) );

IF p_sep_char = '","'
THEN
v_line := ''''||'"'||'''||' || v_line || '||''' || '"' || '''';
dbms_output.put_line(v_line);
END IF;

v_line := 'SELECT ' || v_line || ' FROM ' || p_table_name;

/*
v_line := 'SELECT '||
SUBSTR(v_line, 1, LENGTH(v_line) - (6 + LENGTH(p_sep_char)) )||
' FROM ' || p_table_name ;
*/


dbms_output.put_line(v_line);

OPEN v_TabCurTyp FOR v_line;

file_handle := UTL_FILE.FOPEN(p_file_location, p_filename, 'W');

LOOP

FETCH v_TabCurTyp INTO v_line;
EXIT WHEN v_TabCurTyp%NOTFOUND;

UTL_FILE.PUT_LINE(file_handle,v_line);
dbms_output.put_line(v_line);

END LOOP;

CLOSE v_TabCurTyp;
UTL_FILE.FCLOSE(file_handle);

EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN
dbms_output.put_line('file location or name was invalid');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_MODE
THEN
dbms_output.put_line('the open_mode string was invalid');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN
dbms_output.put_line('not a valid file handle');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INVALID_OPERATION
THEN
dbms_output.put_line('File could not be opened as requested');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.READ_ERROR
THEN
dbms_output.put_line('OS error occurred during read');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.WRITE_ERROR
THEN
dbms_output.put_line('OS error occured during write operation');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN UTL_FILE.INTERNAL_ERROR
THEN
dbms_output.put_line('Internal Error');
RAISE_APPLICATION_ERROR(-20000,SQLCODE || ' : ' ||SQLERRM);
WHEN OTHERS
THEN
UTL_FILE.FCLOSE(file_handle);
RAISE;

END prc_table_to_csv;