1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
| CREATE OR REPLACE
PROCEDURE LIAISON_TM(
p_filename IN VARCHAR2,
p_directory IN VARCHAR2,
p_ignore_headerlines IN INTEGER DEFAULT 1,
p_delimiter IN VARCHAR2 DEFAULT ';')
IS
v_filehandle UTL_FILE.file_type;
v_text VARCHAR2(32767);
v_eof BOOLEAN := FALSE;
v_fields DBMS_SQL.varchar2a;
v_field_index INTEGER;
v_length INTEGER;
v_start INTEGER;
v_index INTEGER;
v_enclosed_start INTEGER;
v_enclosed_end INTEGER;
BEGIN
--DBMS_OUTPUT.ENABLE( 1000000 ) ;
v_filehandle := UTL_FILE.fopen('RATING_LOG','LiaisonServicesRateplan.csv', 'r',32767);
IF p_ignore_headerlines > 0
THEN
BEGIN
FOR i IN 1 .. p_ignore_headerlines
LOOP
UTL_FILE.get_line(v_filehandle, v_text);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_eof := TRUE;
END;
END IF;
WHILE NOT v_eof
LOOP
BEGIN
UTL_FILE.get_line(v_filehandle, v_text);
DBMS_OUTPUT.put_line('v_text=' || v_text);
v_fields.DELETE;
v_field_index := 0;
v_length := LENGTH(v_text);
v_start := 1;
v_enclosed_start := INSTR(v_text, ';', 1);
v_enclosed_end := INSTR(v_text, ';', v_enclosed_start + 1);
WHILE (v_start <= v_length)
LOOP
v_index := INSTR(v_text, p_delimiter, v_start);
IF v_enclosed_end != 0
AND v_index > v_enclosed_start
AND v_index < v_enclosed_end
THEN
v_index := INSTR(v_text, p_delimiter, v_enclosed_end);
v_enclosed_start := INSTR(v_text, ';', v_enclosed_end + 1);
IF v_enclosed_start != 0
THEN
v_enclosed_end :=INSTR(v_text, ';', v_enclosed_start + 1);
END IF;
END IF;
IF v_index = 0
THEN
v_fields(v_field_index) :=TRIM(LTRIM(RTRIM(SUBSTR(v_text, v_start), ';'), ';'));
v_start := v_length + LENGTH(p_delimiter);
ELSE
v_fields(v_field_index) :=TRIM(LTRIM(RTRIM(SUBSTR(v_text, v_start, v_index - v_start),';'),';'));
v_start := v_index + LENGTH(p_delimiter);
END IF;
v_field_index := v_field_index + 1;
END LOOP;
INSERT
INTO temp12(vShDes_sn, vShdes_sp, vAcceSsFee, vSubScript, vCsind, vSrvInd, vAdvInd, vSusInd, vPrintSubsCrind)
VALUES (v_fields(0),v_fields(1),v_fields(2),v_fields(3),v_fields(4),v_fields(5),v_fields(6),v_fields(7),v_fields(8));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_eof := TRUE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SQLERRM);
DBMS_OUTPUT.put_line(v_text);
END;
END LOOP;
UTL_FILE.fclose(v_filehandle);
DECLARE
P_FILENAME VARCHAR2(200):= 'LiaisonServicesRateplan.csv';
P_DIRECTORY VARCHAR2(200):= 'RATING_LOG';
P_IGNORE_HEADERLINES NUMBER := 1;
BEGIN
LIAISON_TM( P_FILENAME => P_FILENAME, P_DIRECTORY => P_DIRECTORY, P_IGNORE_HEADERLINES => P_IGNORE_HEADERLINES );
END;
END LIAISON_TM; |
Partager