Yurttas/PL/DBL/oracle/F/02/ABC/abc-ins.sp

 1CREATE OR REPLACE
 2PROCEDURE abc_ins IS
 3
 4  in_file UTL_FILE.FILE_TYPE;
 5
 6  out_file UTL_FILE.FILE_TYPE;
 7
 8  BUFF VARCHAR2(80);
 9
10  SA TABC.A%TYPE;
11  SB TABC.B%TYPE;
12  SC TABC.C%TYPE;
13
14BEGIN
15
16  in_file := UTL_FILE.FOPEN('/user/scratch4',
17                            'abc-data.txt',
18                            'R');
19
20  out_file := UTL_FILE.FOPEN('/user/scratch4',
21                             'abc-out.txt',
22                             'W');           
23
24  LOOP
25
26    BEGIN
27      UTL_FILE.GET_LINE(in_file, BUFF);
28      UTL_FILE.PUT_LINE(out_file, BUFF);
29    EXCEPTION
30      WHEN NO_DATA_FOUND
31      THEN
32        UTL_FILE.FCLOSE_ALL;
33      EXIT;
34    END;
35
36    SA := SUBSTR(BUFF, 1, 2);
37    SB := SUBSTR(BUFF, 3, 4);
38    SC := SUBSTR(BUFF, 10, 8);
39
40    INSERT INTO TABC
41      VALUES(SA, SB, SC);
42
43  END LOOP;
44
45EXCEPTION
46  WHEN OTHERS
47  THEN
48    UTL_FILE.FCLOSE_ALL;
49    RAISE;
50
51END abc_ins;
52/