CREATE OR REPLACE PACKAGE UNLOAD
IS
/*********************************************************************/
-- ファンクション名 SQL_TO_CSV
-- パラメータ ：(I  ) data_source      テーブル、ビュー、クエリー
--            ：(I  ) date_format      日付書式（以下すべて任意）
--            ：(I  ) timestamp_format タイムスタンプ書式（9i）
--            ：(I  ) set_col_sep      カラム間の区切り文字(,)
--            ：(I  ) set_enclose      カラム引用符文字(")
--            ：(I  ) set_alt_quote    データ中の引用符の代替文字("")
--            ：(I  ) trim_char        CHAR 型のRTRIM あり(1) なし(0)
--            ：(I  ) enclose_num      数値型の引用符 あり(1) なし(0)
--            ：(I  ) ignore_lob       LOB 型はスキップする(1) しない(0)
--            ：(I  ) over_4k          結果文字列をCLOBに する(1) しない(0)
-- 戻り値     ：CSV を格納した REF カーソル
-- Copyright  ：ILS
/*********************************************************************/
-- constatnt
nTRUE	CONSTANT BINARY_INTEGER := 1;
nFALSE	CONSTANT BINARY_INTEGER := 0;
-- type
SUBTYPE BOOLEAN# IS BINARY_INTEGER RANGE 0..1;  -- 0: FALSE / 1: TRUE
TYPE VARCHAR2_ARRAY IS TABLE OF VARCHAR2(4000);
TYPE CLOB_ARRAY IS TABLE OF CLOB;
--
--
FUNCTION SQL_TO_CSV(
	data_source       IN VARCHAR2,
	date_format       IN VARCHAR2 := NULL,
	timestamp_format  IN VARCHAR2 := NULL,
	set_col_sep       IN VARCHAR2 := ',',
	set_enclose       IN VARCHAR2 := '',
	set_alt_quote     IN VARCHAR2 := '""',
	trim_char         IN BOOLEAN# := 1,
	enclose_num       IN BOOLEAN# := 0,
	ignore_lob        IN BOOLEAN# := 1,
	over_4k           IN BOOLEAN# := 0
)
RETURN SYS_REFCURSOR;
--
FUNCTION SQL_TO_REGULAR_SQL(
	data_source       IN VARCHAR2,
	date_format       IN VARCHAR2 := NULL,
	timestamp_format  IN VARCHAR2 := NULL,
	set_col_sep       IN VARCHAR2 := ',',
	set_enclose       IN VARCHAR2 := '',
	set_alt_quote     IN VARCHAR2 := '""',
	trim_char         IN BOOLEAN# := 1,
	enclose_num       IN BOOLEAN# := 0,
	ignore_lob        IN BOOLEAN# := 1,
	over_4k           IN BOOLEAN# := 0
)
RETURN VARCHAR2;
--
FUNCTION GET_CSV(
	query      IN VARCHAR2,
	fetch_size IN POSITIVEN := 15
)
RETURN VARCHAR2_ARRAY PIPELINED;
--
FUNCTION GET_CSV_CLOB(
	query      IN VARCHAR2,
	fetch_size IN POSITIVEN := 1
)
RETURN CLOB_ARRAY PIPELINED;
--
FUNCTION EXPORT_TO_FILE(
	query	  IN VARCHAR2,
	file_name IN VARCHAR2,
	dir_name  IN VARCHAR2 := 'DATA_PUMP_DIR'
)
RETURN NUMBER;
--
FUNCTION EXPORT_TO_FILE(
	cDataSource  IN SYS_REFCURSOR,
	file_name IN VARCHAR2,
	dir_name  IN VARCHAR2 := 'DATA_PUMP_DIR'
)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY UNLOAD
IS
-- Copyright  ： ILS
-- constant
-- --
SINGLE_QUOTE		CONSTANT VARCHAR2(2000) := '''';   -- '
-- -- datatypes DBMS_SQL
TYP_VARCHAR2		CONSTANT INTEGER := 1;
TYP_NUMBER		CONSTANT INTEGER := 2;
TYP_LONG		CONSTANT INTEGER := 8;
TYP_DATE		CONSTANT INTEGER := 12;
TYP_BINARY_FLOAT	CONSTANT INTEGER := 21;
TYP_BINARY_DOUBLE	CONSTANT INTEGER := 22;
TYP_2BINARY_FLOAT	CONSTANT INTEGER := 100;
TYP_2BINARY_DOUBLE	CONSTANT INTEGER := 101;
TYP_RAW			CONSTANT INTEGER := 23;
TYP_LONG_RAW		CONSTANT INTEGER := 24;
TYP_CHAR		CONSTANT INTEGER := 96;
TYP_CLOB		CONSTANT INTEGER := 112;
TYP_NCLOB		CONSTANT INTEGER := 112;
TYP_BLOB		CONSTANT INTEGER := 113;
TYP_BFILE		CONSTANT INTEGER := 114;
TYP_TIMESTAMP		CONSTANT INTEGER := 180;
TYP_TIMESTAMP_TZ	CONSTANT INTEGER := 181;
TYP_INTERVAL_YM		CONSTANT INTEGER := 182;
TYP_INTERVAL_DS		CONSTANT INTEGER := 183;
-- variables
--
-- private sub program
--
-- quote value
FUNCTION QUOTE_VALUE2(
	val IN VARCHAR2,
	q1 IN VARCHAR2,
	q2 IN VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
	RETURN q1 || val || q2;
END;
FUNCTION QUOTE_VALUE(
	val IN VARCHAR2,
	q IN VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
	RETURN QUOTE_VALUE2(val, q, q);
END;
--
-- enclose sql columns
FUNCTION SQLENCLOSE_COLUMN2(
	col IN VARCHAR2,
	pre_word IN VARCHAR2 := NULL,
	post_word IN VARCHAR2 := NULL
)
RETURN VARCHAR2
IS
	pre_sql	 VARCHAR2(2000);
	post_sql VARCHAR2(2000);
BEGIN
	pre_sql := pre_word;
	post_sql := post_word;
	IF (pre_sql IS NOT NULL) THEN
		pre_sql := pre_sql || '||';
	END IF;
	IF (post_sql IS NOT NULL) THEN
		post_sql := '||' || post_sql;
	END IF;
	RETURN QUOTE_VALUE2(col, pre_sql, post_sql);
END;
FUNCTION SQLENCLOSE_COLUMN(
	col IN VARCHAR2,
	enc IN VARCHAR2 := NULL
)
RETURN VARCHAR2
IS
BEGIN
	RETURN SQLENCLOSE_COLUMN2(col, enc, enc);
END;
--
-- add(concat) column
PROCEDURE ADD_COL(
	cols  IN OUT NOCOPY VARCHAR2,
	col   IN VARCHAR2,
	sep   IN VARCHAR2,          -- column separator string
	quote IN VARCHAR2 := NULL   -- quote string
)
IS
BEGIN
	IF (col IS NULL) THEN
		RETURN;
	END IF;
	IF (cols IS NULL) THEN
		cols := SQLENCLOSE_COLUMN(col, quote);
	ELSE
		cols := cols || QUOTE_VALUE(sep, '||') || SQLENCLOSE_COLUMN(col, quote);
	END IF;
END;
--
-- table/view/query table data
FUNCTION GET_DESC_TAB(
	query IN VARCHAR2
)
RETURN DBMS_SQL.DESC_TAB2
IS
	stmt	VARCHAR2(32767);
	cur	NUMBER;
	cnt 	INTEGER;
	tab 	DBMS_SQL.DESC_TAB2;
BEGIN
	stmt := 'select * from (' || query || ')';
	cur := DBMS_SQL.OPEN_CURSOR;
	DBMS_SQL.PARSE(cur, stmt, DBMS_SQL.NATIVE);
	DBMS_SQL.DESCRIBE_COLUMNS2(cur, cnt, tab);
	DBMS_SQL.CLOSE_CURSOR(cur);
	RETURN tab;
EXCEPTION
  WHEN OTHERS THEN
	RAISE;
END;
--
-- datetime nls_parameter
FUNCTION GET_DATETIME_FORMAT(
	param IN VARCHAR2
)
RETURN VARCHAR2
IS
	format_spec VARCHAR2(30);
BEGIN
	SELECT VALUE INTO format_spec
	FROM V$NLS_PARAMETERS
	WHERE PARAMETER= param ;
	RETURN format_spec;
EXCEPTION
  WHEN OTHERS THEN
	RETURN 'YYYY-MM-DD HH24:MI:SS';
END;
PROCEDURE CHECK_DATE_FORMAT(
	format_spec IN VARCHAR2
)
IS
	fmtStr VARCHAR2(100);
BEGIN
	fmtStr := TO_CHAR(SYSTIMESTAMP,format_spec);
	RETURN;
EXCEPTION
  WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(format_spec);
	RAISE;
END;
--
FUNCTION REG_SQL_TO_CSV(
	query       IN VARCHAR2
)
RETURN SYS_REFCURSOR
IS
	-- return cursor
	cDataSource	SYS_REFCURSOR;
	-- meta data
	tDescTab	DBMS_SQL.DESC_TAB2;
BEGIN
	tDesctab := GET_DESC_TAB(query);
	OPEN cDataSource FOR query;
	RETURN cDataSource;
EXCEPTION
  WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(query);
	RAISE;
END;
--
-- public sub program
--
FUNCTION SQL_TO_REGULAR_SQL(
	data_source       IN VARCHAR2,
	date_format       IN VARCHAR2 := NULL,
	timestamp_format  IN VARCHAR2 := NULL,
	set_col_sep       IN VARCHAR2 := ',',
	set_enclose       IN VARCHAR2 := '',
	set_alt_quote     IN VARCHAR2 := '""',
	trim_char         IN BOOLEAN# := 1,
	enclose_num       IN BOOLEAN# := 0,
	ignore_lob        IN BOOLEAN# := 1,
	over_4k           IN BOOLEAN# := 0
)
RETURN VARCHAR2
IS
	-- return cursor
	cDataSource	SYS_REFCURSOR;
	-- meta data
	tDescTab	DBMS_SQL.DESC_TAB2;
	tDescRec	DBMS_SQL.DESC_REC2;
	colType		NUMBER;
	colID		BINARY_INTEGER;
	colName		VARCHAR2(1999);
	-- format
	fmtDate		VARCHAR2(2000);
	fmtTime		VARCHAR2(2000);
	encloseChar	VARCHAR2(30);
	enclosing	BOOLEAN#;
	isCLOB		BOOLEAN#;
	separateChar	VARCHAR2(30);
	-- sql
	expr		VARCHAR2(1999);
	selectList	VARCHAR2(32767);
	query		VARCHAR2(32767);
BEGIN
	-- <check parameters>
	encloseChar := set_enclose;
	IF (encloseChar IS NOT NULL) THEN
		encloseChar :=  QUOTE_VALUE(encloseChar, SINGLE_QUOTE);
	END IF;
	separateChar := set_col_sep;
	IF (separateChar IS NOT NULL) THEN
		separateChar :=  QUOTE_VALUE(separateChar, SINGLE_QUOTE);
	END IF;
	IF (date_format IS NULL) THEN
		fmtDate := GET_DATETIME_FORMAT('NLS_DATE_FORMAT');
	ELSE
		fmtDate := date_format;
	END IF;
	CHECK_DATE_FORMAT(fmtDate);
	fmtDate := QUOTE_VALUE(fmtDate, SINGLE_QUOTE);
	IF (timestamp_format IS NULL) THEN
		fmtTime := GET_DATETIME_FORMAT('NLS_TIMESTAMP_FORMAT');
	ELSE
		fmtTime := timestamp_format;
	END IF;
	CHECK_DATE_FORMAT(fmtTime);
	fmtTime := QUOTE_VALUE(fmtTime, SINGLE_QUOTE);
	isCLOB := over_4k;
	tDesctab := GET_DESC_TAB(data_source);
	-- </check parameters>
	-- <create sql statement>
	colID := tDescTab.first;
	selectList := '';
	WHILE (colID IS NOT NULL)
	LOOP
		tDescRec := tDescTab(colID);
		colType := tDescRec.col_type;
		colName := tDescRec.col_name;
		enclosing := nTRUE;
		CASE
		WHEN (colType = TYP_CHAR) THEN
			IF (trim_char = nTRUE) THEN
				expr := 'rtrim(' || colName || ')';
			ELSE
				expr := colName;
			END IF;
		WHEN (colType = TYP_VARCHAR2) THEN
			expr := colName;
		WHEN (colType = TYP_NUMBER) THEN
			enclosing := enclose_num;
			expr := 'to_char(' || colName || ')';
		WHEN (colType IN (TYP_BINARY_FLOAT, TYP_BINARY_DOUBLE,
                                  TYP_2BINARY_FLOAT, TYP_2BINARY_DOUBLE)) THEN
			enclosing := enclose_num;
			expr := 'to_char(' || colName || ')';
		WHEN (colType = TYP_DATE) THEN
			expr := 'to_char(' || colName || ',' || fmtDate || ')';
		WHEN (colType = TYP_TIMESTAMP) THEN
			expr := 'to_char(' || colName || ',' || fmtTime || ')';
		WHEN  (colType = TYP_RAW) THEN
			expr := 'rawtohex(' || colName || ')';
		WHEN  (colType = TYP_CLOB) THEN
			IF (ignore_lob = nTRUE) THEN
				expr := '';
				enclosing := nFALSE;
			ELSE
				isCLOB := nTRUE;
				expr := colName;
			END IF;
		WHEN  (colType = TYP_BLOB) THEN
			expr := '';
			IF (ignore_lob = nTRUE) THEN
				enclosing := nFALSE;
			END IF;
		ELSE
			expr := 'to_char(' || colName || ')';
		END CASE;
		IF (enclosing = nTRUE) THEN
			IF (set_enclose <> set_alt_quote AND set_enclose || set_alt_quote IS NOT NULL) THEN
				expr := 'replace(' || expr || ',''' || set_enclose || ''',''' || set_alt_quote || ''')';
			END IF;
			ADD_COL(selectList, expr, separateChar, encloseChar);
		ELSE
			ADD_COL(selectList, expr, separateChar, '');
		END IF;
		colID := tDescTab.next(colID);
	END LOOP;
	IF (isCLOB = nTRUE) THEN
		selectList := 'to_clob('''')||' || selectList;
	END IF;
	query := 'select ' || selectList || ' as csv_text from (' || data_source || ')';
	-- </create sql statement>
	-- check sql statement
	tDesctab := GET_DESC_TAB(query);
	RETURN query;
EXCEPTION
  WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(query);
	RAISE;
END;
--
FUNCTION SQL_TO_CSV(
	data_source       IN VARCHAR2,
	date_format       IN VARCHAR2 := NULL,
	timestamp_format  IN VARCHAR2 := NULL,
	set_col_sep       IN VARCHAR2 := ',',
	set_enclose       IN VARCHAR2 := '',
	set_alt_quote     IN VARCHAR2 := '""',
	trim_char         IN BOOLEAN# := 1,
	enclose_num       IN BOOLEAN# := 0,
	ignore_lob        IN BOOLEAN# := 1,
	over_4k           IN BOOLEAN# := 0
)
RETURN SYS_REFCURSOR
IS
	query	VARCHAR2(32767);
BEGIN
	query := SQL_TO_REGULAR_SQL(
		data_source,
		date_format,
		timestamp_format,
		set_col_sep,
		set_enclose,
		set_alt_quote,
		trim_char,
		enclose_num,
		ignore_lob,
		over_4k
	);
	RETURN REG_SQL_TO_CSV(query);
END;
--
FUNCTION GET_CSV(
	query      IN VARCHAR2,
	fetch_size IN POSITIVEN := 15
)
RETURN VARCHAR2_ARRAY PIPELINED
IS
	BULK_SIZE	CONSTANT PLS_INTEGER := 15;
	vRows		VARCHAR2_ARRAY;
	vCur		SYS_REFCURSOR;
BEGIN
	vCur := SQL_TO_CSV(data_source => query, ignore_lob => nTRUE, over_4k => nFALSE);
	LOOP
		FETCH vCur BULK COLLECT INTO vRows LIMIT fetch_size;
		EXIT WHEN vRows.COUNT = 0;
		FOR i in 1 .. vRows.COUNT LOOP
			PIPE ROW(vRows(i));
		END LOOP;
	END LOOP;
	CLOSE vCur;
	RETURN;
END;
--
FUNCTION GET_CSV_CLOB(
	query      IN VARCHAR2,
	fetch_size IN POSITIVEN := 1
)
RETURN CLOB_ARRAY PIPELINED
IS
	vRows		CLOB_ARRAY;
	vCur		SYS_REFCURSOR;
BEGIN
	vCur := SQL_TO_CSV(data_source => query, ignore_lob => nFALSE, over_4k => nTRUE);
	LOOP
		FETCH vCur BULK COLLECT INTO vRows LIMIT fetch_size;
		EXIT WHEN vRows.COUNT = 0;
		FOR i in 1 .. vRows.COUNT LOOP
			PIPE ROW(vRows(i));
		END LOOP;
	END LOOP;
	CLOSE vCur;
	RETURN;
END;
--
FUNCTION EXPORT_TO_FILE(
	cDataSource  IN  SYS_REFCURSOR,
	file_name    IN  VARCHAR2,
--	dir_name     IN  VARCHAR2 := 'DATA_PUMP_DIR'
	dir_name     IN  VARCHAR2
)
RETURN NUMBER
IS
	FETCH_SIZE	CONSTANT PLS_INTEGER := 100;
	data_rows		VARCHAR2_ARRAY;
	file_handle	UTL_FILE.FILE_TYPE;
	row_count	NUMBER := 0;
BEGIN
	file_handle := UTL_FILE.FOPEN(dir_name ,file_name, 'w', 32767);
	LOOP
		FETCH cDataSource BULK COLLECT INTO data_rows LIMIT FETCH_SIZE;
		EXIT WHEN data_rows.COUNT = 0;
		FOR i in 1 .. data_rows.COUNT LOOP
			UTL_FILE.PUT_LINE(file_handle, data_rows(i));
		END LOOP;
		row_count := row_count + data_rows.COUNT;
	END LOOP;
	UTL_FILE.FCLOSE(file_handle);
	RETURN row_count;
EXCEPTION
  WHEN OTHERS THEN
	UTL_FILE.FCLOSE_ALL;
	RAISE;
END;
--
FUNCTION EXPORT_TO_FILE(
	query	  IN VARCHAR2,
	file_name IN VARCHAR2,
	dir_name  IN VARCHAR2 := 'DATA_PUMP_DIR'
)
RETURN NUMBER
IS
BEGIN
	RETURN EXPORT_TO_FILE(SQL_TO_CSV(query), file_name, dir_name);
EXCEPTION
  WHEN OTHERS THEN
	RAISE;
END;
--
BEGIN
	NULL;
END;
/
quit;