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; |
