/*
/*===========================================================================
	CSVファイル読込み ( TEXT_GET3 )
===========================================================================*/
CREATE or REPLACE PROCEDURE TEXT_GET3
	AS
 
	TEXT_BUFFER		VARCHAR2(2000);		-- 読込バッファ
	FILEHANDLE		UTL_FILE.FILE_TYPE;
	TYPE CSV_TBL	IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
	CSV_DATA		CSV_TBL;
 
/*=====================================================
   CSV_DATA_SET
=====================================================*/
PROCEDURE CSV_DATA_SET
	AS
 
	MAX_CNT			NUMBER := 3;
	CNT			BINARY_INTEGER;
	ABORT_CNT		NUMBER;
	POINT_1			NUMBER;
	POINT_2			NUMBER;
	BUF_LEN			NUMBER;
	D1			VARCHAR2(1) := ',';
	FIRST_FLG		BOOLEAN;
 
BEGIN
 
	CNT		:= 1;
	FIRST_FLG	:= TRUE;
	BUF_LEN		:= LENGTH(TEXT_BUFFER);
 
	POINT_1		:= 0;
	LOOP
		EXIT WHEN CNT > MAX_CNT;
		EXIT WHEN POINT_1 > 2000;
		EXIT WHEN POINT_1 > BUF_LEN;
		IF D1 = SUBSTR(TEXT_BUFFER,POINT_1,1) OR FIRST_FLG THEN
			POINT_2 := POINT_1 + 1;
			ABORT_CNT := 0;
			LOOP
				EXIT WHEN
				    D1 = SUBSTR(TEXT_BUFFER,POINT_2,1)
				 or POINT_2 > BUF_LEN;
				EXIT WHEN
				   ABORT_CNT > 100;
				POINT_2 := POINT_2 + 1;
				ABORT_CNT := ABORT_CNT + 1;
			END LOOP;
			IF POINT_1 + 1 = POINT_2 THEN
				/* カンマが連続する場合は NULL がセットされます */
				CSV_DATA(CNT) := NULL;
			ELSE
				CSV_DATA(CNT)
					:= SUBSTR(TEXT_BUFFER,POINT_1+1,POINT_2-POINT_1-1);
			END IF;
			CNT := CNT + 1;
			IF FIRST_FLG THEN
				FIRST_FLG := FALSE;
			END IF;
			POINT_1 := POINT_2 - 1;
		END IF;
		POINT_1 := POINT_1 + 1;
	END LOOP;
 
END CSV_DATA_SET;
 
/*=====================================================
   主処理
=====================================================*/
BEGIN
 
	DBMS_OUTPUT.PUT_LINE( 'TXT_FOPEN使用' );
 
	LOOP
 
		EXIT
		  WHEN
		    not TXT_FOPEN( FILEHANDLE, 'TEXT_DIR', 'list.txt' );
 
		LOOP
			BEGIN
				UTL_FILE.GET_LINE( FILEHANDLE,TEXT_BUFFER );
				EXCEPTION
					WHEN NO_DATA_FOUND THEN
						EXIT;
			END;
 
			CSV_DATA_SET;
			DBMS_OUTPUT.PUT_LINE( TEXT_BUFFER );
			DBMS_OUTPUT.PUT_LINE( 'TOKEN 1 = ' || CSV_DATA(1) );
			DBMS_OUTPUT.PUT_LINE( 'TOKEN 2 = ' || CSV_DATA(2) );
			DBMS_OUTPUT.PUT_LINE( 'TOKEN 3 = ' || CSV_DATA(3) );
 
		END LOOP;
 
		UTL_FILE.FCLOSE( FILEHANDLE );
 
	EXIT;
	END LOOP;
 
END TEXT_GET3;
/

SUBSTRB( RTRIM( LTRIM( CSV_DATA(2),' 　' ), ' 　' ),1 ,50 )

UPDATE 得意先 SET
	"取引先" = NVL(RTRIM(SUBSTRB(CSV_取引先,1,8),' 　'),'        '),
	"全角取引先名A" = RTRIM(SUBSTRB(CSV_全角取引先名A,1,30),' 　'),
	"全角取引先名B" = RTRIM(SUBSTRB(CSV_全角取引先名B,1,20),' 　'),
	"登録日" = TO_DATE(SUBSTRB(RTRIM(LTRIM(CSV_登録日,' 　'),' 　'),1,8),'YYYYMMDD'),
	"更新日" = TO_DATE(SUBSTRB(RTRIM(LTRIM(CSV_更新日,' 　'),' 　'),1,8),'YYYYMMDD')
WHERE
	"取引先" = NVL(RTRIM(SUBSTRB(CSV_取引先,1,8),' 　'),'        ');
IF SQL%NOTFOUND THEN
	INSERT INTO 得意先 (
		"取引先",
		"全角取引先名A",
		"全角取引先名B",
		"登録日",
		"更新日"
	) VALUES (
		NVL(RTRIM(SUBSTRB(CSV_取引先,1,8),' 　'),'        '),
		RTRIM(SUBSTRB(CSV_全角取引先名A,1,30),' 　'),
		RTRIM(SUBSTRB(CSV_全角取引先名B,1,20),' 　'),
		TO_DATE(SUBSTRB(RTRIM(LTRIM(CSV_登録日,' 　'),' 　'),1,8),'YYYYMMDD'),
		TO_DATE(SUBSTRB(RTRIM(LTRIM(CSV_更新日,' 　'),' 　'),1,8),'YYYYMMDD')
);
END IF;