beginner in oracle trying to read text file

Question

I am a beginner in oracle.

I have been trying to read text file and insert its data in utl_file but unable to do so as I do not understand the following things:

Comma1 := INSTR(f_line, ',' ,1 , 1);
Comma2 := INSTR(f_line, ',' ,1 , 2);
Comma3 := INSTR(f_line, ',' ,1 , 3);
Comma4 := INSTR(f_line, ',' ,1 , 4);
Comma5 := INSTR(f_line, ',' ,1 , 5);
f_empno := to_number(SUBSTR(f_line, 1, Comma1-1));
f_ename := SUBSTR(f_line, Comma1+1, Comma2-Comma1-1);
f_job := SUBSTR(f_line, comma2+1, Comma3-Comma2-1);
f_mgr := to_number(SUBSTR(f_line, comma3+1, Comma4-Comma3-1));
f_hiredate := to_date(SUBSTR(f_line, comma4+1, Comma5-Comma4-1),'dd-mon-yyyy');
f_sal := to_number(SUBSTR(f_line, comma5+1),'99999');
dbms_output.put_line(f_empno ||' '|| f_ename || ' ' || f_job || ' ' || f_mgr || ' ' || f_hiredate || ' ' || f_sal);
insert into emp12 VALUES (f_empno,f_ename,f_job,f_mgr,f_hiredate,f_sal);

You're question is unclear. What don't you understand? Please be a little more specific in your question. — Yaron Idan, Oct 11 '15 at 07:46
The code you've shown calls several Oracle functions (`INSTR`, `TO_NUMBER`, `SUBSTR`, `TO_DATE`) to extract data from the variable `f_line`. The Oracle documentation for the functions [can be found here](http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm#i88893). Best of luck. — Bob Jarvis - Слава Україні, Oct 11 '15 at 12:16
Looks like you're trying to read a CSV file. There are multiple solutions out there for doing this without having to hand-code the CSV-decoding part yourself. One of them is to use an External Table. Another is to use the appropriate package in the Alexandria PL/SQL Library. — Jeffrey Kemp, Oct 12 '15 at 05:07
@YaronIdan actually i want to create a procedure which will read data from text file and insert it into a table using a procedure. If you could share an example then, it would be a great help. — Abhinav, Oct 12 '15 at 14:52
@JeffreyKemp actually i want to create a procedure which will read data from text file and insert it into a table using a procedure. If you could share an example then, it would be a great help. — Abhinav, Oct 12 '15 at 14:55

Yaron Idan · Answer 1 · 2015-10-13T13:21:18.873

This is a nice simple example for the code to read data from a text file, and its much more simple than what you posted originally - http://nimishgarg.blogspot.co.il/2013/04/load-csv-file-in-oracle-using-plsql.html

Here are the relevant pieces of code from the link attached - Run all of these commands in order to read the file EMP_DEPT.CSV from directory e:\mycsv\ into table emp_dept on user scott (change each of these parameters to fit your design)

// 1. First, create a directory object so oracle can access the file's location

create or replace directory MYCSV as 'e:\mycsv\';

// 2. Grant the user reading from the file privileges on that directory

grant read, write on directory MYCSV to scott;

// 3. Create a table in the same structure as the rows in the file

CREATE TABLE EMP_DEPT
  (
  EMPNO NUMBER(4),
  ENAME VARCHAR2(10),
  SAL NUMBER(7,2),
  DNAME VARCHAR2(14)
  );

// 4. Run this procedure after making the following changes - 
// a. Change the variables to match the columns in your destination table
// b. Change the UTL.FILE.FOPEN command to match the directory and file name in your case.
// c. Change every REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1); to match the variable you want to assign.
// 5. Go for it.
 DECLARE
    F UTL_FILE.FILE_TYPE;
    V_LINE VARCHAR2 (1000);
    V_EMPNO NUMBER(4);
    V_ENAME VARCHAR2(10);
    V_SAL NUMBER(7,2);
    V_DNAME VARCHAR2(14);
  BEGIN
    F := UTL_FILE.FOPEN ('MYCSV', 'EMP_DEPT.CSV', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
          UTL_FILE.GET_LINE(F, V_LINE, 1000);
          IF V_LINE IS NULL THEN
            EXIT;
          END IF;
          V_EMPNO := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);
          V_ENAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
          V_SAL := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
          V_DNAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);
          INSERT INTO EMP_DEPT VALUES(V_EMPNO, V_ENAME, V_SAL, V_DNAME);
          COMMIT;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
  END;
  /

Good luck.

@YaronIdan I want to make a procedure, not an anonymous block. Please tell me how to make a procedure which will read data from text file and insert it into a table — Abhinav, Oct 13 '15 at 14:52
Just change the DECLARE statement that opens the anonymous block to "CREATE OR REPLACE PROCEDURE IS" — Yaron Idan, Oct 13 '15 at 14:54

beginner in oracle trying to read text file

1 Answers1