Read data from a text file and pass that data in a stored procedure input parameter using pl/sql scripts only

Question

I have a text file 'C:\file\Datafile.txt' which contains the below data.

1,Dixon,200
2,Sam,400
3,Peter,200

now I have a SP in oracle which accepts three input parameters (employeeid, employeename, salary).

I want to pass the values in that text file as an input parameter to this Stored Procedure one by one.

Kindly share any pl/sql script if possible to do this activity.

Answer 1

How to enable UTL_FILE package in oracle developer please let me now?

I am thinking of implementing the below solution.

CREATE OR REPLACE PROCEDURE LoadFromFileData(p_FileDir IN VARCHAR2,p_FileName IN VARCHAR2)
AS
    v_FileHandle UTL_FILE.FILE_TYPE;
    v NewLine VARCHAR2(100); -- Input line
    v_Column1 VARCHAR2(100);
    v_Column2 VARCHAR2(100);
    v_Column3 VARCHAR2(100);
    v_FirstConna NUMBER;
    v_SecondComma NUMBER;
    p_TotalInserted NUMBER;

BEGIN
    v_FileHandle := UTL_FILE.FOPEN(p_FileDir ,p_FileName, 'r');
    p_TotalInserted := 1;
    LOOP
    BEGIN
    UTL_FILE.GET_LINE(v_FileHandle, v NewLine);
    EXCEPTION   WHEN NO_DATA_FOUND THEN
    EXIT;
    END;

    v_FirstComma := INSTR(v NewLine, ',', 1, 1);
    v_SecondComma := INSTR(v NewLine, ',', 1, 2);

    v_Column1 := SUBSTR(v NewLine, 1, v_FirstComma - 1);
    v_Column2 := SUBSTR(v RewLine, v_FirstConma + 1, v_SecondComma - v_FirstComma - 1);
    v_Column3 := SUBSTR(v Ne;Line, v_SecondConna + 1);
INSERT INTO LECTURER (ID, First_Name, Last_Nane, Major) VALUES (p_TotalInserted, TO_CHAR(v_Column1), TO_CHAR(v_Column2), TO_CHAR(v_Column3));
p_Totallnserted := p_TotalInserted + 1;
END LOOP;
UTL FILE.FCLOSE(v_FileHandle); COMMIT;
EXCEPTION
WHEN UTL FILE. INVALID OPERATION THEN 
UTL FIEE.FCLOSE(v FileHandle);
COMMIT;
EXCEPTION
DBMS_OUTPUT.put_line('Uncessusful');
END LoadLecturerData;

Answer 2

As you said that file is on the database server and you'd like to use UTL_FILE, then connect as a privileged user (such as SYS, if you don't have any other) and

• create a directory (Oracle object which points to a filesystem directory)
• grant read (and/or write) privileges on it to user(s) who will be using it

Why? Because UTL_FILE (as well as e.g. external tables feature, if you choose to use it) require the directory. SQL*Loader, on the other hand, doesn't - with it, file can reside on your local PC, not on the database server.

SQL> show user
USER is "SYS"
SQL> create directory ext_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory ext_dir to scott;

Grant succeeded.

SQL> grant execute on utl_file to scott;

Grant succeeded.

Now, connect as user which will be doing the job (scott in my case) and - using input file as you specified (c:\temp\datafile.txt) whose content is

1,Dixon,200
2,Sam,400
3,Peter,200

do the following:

SQL> create table test (id number, name varchar2(10), salary number);

Table created.

SQL> declare
  2    l_file  utl_file.file_type;
  3    l_line  varchar2(50);
  4  begin
  5    l_file := utl_file.fopen('EXT_DIR', 'datafile.txt', 'R');
  6
  7    loop
  8      begin
  9        utl_file.get_line(l_file, l_line);
 10
 11        insert into test (id, name, salary) values
 12          (substr(l_line, 1, instr(l_line, ',', 1, 1) - 1),
 13           substr(l_line, instr(l_line, ',', 1, 1) + 1,
 14                          instr(l_line, ',', 1, 2) - instr(l_line, ',', 1, 1) - 1),
 15           substr(l_line, instr(l_line, ',', 1, 2) + 1)
 16          );
 17      exception
 18        when no_data_found then exit;
 19      end;
 20    end loop;
 21
 22    utl_file.fclose(l_file);
 23  end;
 24  /

PL/SQL procedure successfully completed.

Result:

SQL> select * from test;

        ID NAME           SALARY
---------- ---------- ----------
         1 Dixon             200
         2 Sam               400
         3 Peter             200

SQL>

Looks OK to me.