This discussion is archived
9 Replies Latest reply: Jul 2, 2013 6:17 AM by BluShadow RSS

how to load data using Utl_file

898862 Newbie
Currently Being Moderated

Hi,

 

I have to read data from a flat file and load it into 2 two tables emp1 and emp2,

My requirement is I want to load first two columns in table emp1(empno,empname,empsal,empdesg) and next two columns into a table called emp2 with columns (eno,ename,esal,edesg) using UTL file. Please help...

  • 1. Re: how to load data using Utl_file
    Karthick_Arp Guru
    Currently Being Moderated

    Why UTL_FILE? Why not External Table?

  • 2. Re: how to load data using Utl_file
    898862 Newbie
    Currently Being Moderated

    I have few vadlidations need to chk and need to do using utl_file . pls help...

  • 3. Re: how to load data using Utl_file
    Hoek Guru
    Currently Being Moderated

    You probably could validate much easier using an external table, but anyway:

    UTL_FILE

  • 4. Re: how to load data using Utl_file
    BluShadow Guru Moderator
    Currently Being Moderated

    I would also opt for the External Table method, as it would make it easier to query the data and apply any restrictive clauses and data manipulations to it.

    Then it would simply be a case of using an INSERT ALL statement to insert the data into multiple tables as you query it from the external table...

     

    (in this example I'm just querying from the emp table but it would be the same querying from an external table)...

     

     

    SQL> create table empnames (empno number, ename varchar2(10), deptno number);

     

    Table created.

     

    SQL> create table empsals (empno number, sal number, comm number);

     

    Table created.

     

    SQL> ed
    Wrote file afiedt.buf

      1  insert all
      2  when 1=1 then
      3    into empnames values (empno, ename, deptno)
      4  when 1=1 then
      5    into empsals values (empno, sal, comm)
      6  select empno, ename, sal, comm, deptno
      7* from emp
    SQL> /

     

    28 rows created.

     

    SQL> select * from empnames;

         EMPNO ENAME          DEPTNO
    ---------- ---------- ----------
          7369 SMITH              20
          7499 ALLEN              30
          7521 WARD               30
          7566 JONES              20
          7654 MARTIN             30
          7698 BLAKE              30
          7782 CLARK              10
          7788 SCOTT              20
          7839 KING               10
          7844 TURNER             30
          7876 ADAMS              20
          7900 JAMES              30
          7902 FORD               20
          7934 MILLER             10

     

    14 rows selected.

     

    SQL> select * from empsals;

         EMPNO        SAL       COMM
    ---------- ---------- ----------
          7369        800
          7499       1600        300
          7521       1250        500
          7566       2975
          7654       1250       1400
          7698       2850
          7782       2450
          7788       3000
          7839       5000
          7844       1500          0
          7876       1100
          7900        950
          7902       3000
          7934       1300

     

    14 rows selected.

  • 5. Re: how to load data using Utl_file
    898862 Newbie
    Currently Being Moderated

    will the below code will work .pls assist

     

    CREATE OR REPLACE PROCEDURE load_data(errbuff varchar2,errcode number)

    AS

    v_line VARCHAR2(2000); -- read from input file

    v_file UTL_FILE.FILE_TYPE; --   file handler

    v_dir VARCHAR2(250);

    v_filename VARCHAR2(50); 

    v_1st_Comma number;

    v_2nd_Comma number;

    v_3rd_Comma number;

    v_4th_Comma number;

    v_5th_Comma number;

    v_empno sample_emp.empno%type;

    v_ename sample_emp.ename%type;

    v_job sample_emp.job%type;

    v_mgr sample_emp.mgr%type;

    v_hiredate sample_emp.hiredate%type;


    BEGIN

    v_dir := '/usr/tmp';

    v_filename := 'sample.dat';

    v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'r');

     

    LOOP

    BEGIN

    UTL_FILE.GET_LINE(v_file, v_line);

    EXCEPTION

    WHEN no_data_found THEN

    exit;

    END;

     

    v_1st_Comma := INSTR(v_line, ',' ,1 , 1);

    v_2nd_Comma := INSTR(v_line, ',' ,1 , 2);

    v_3rd_Comma := INSTR(v_line, ',' ,1 , 3);

    v_4th_Comma := INSTR(v_line, ',' ,1 , 4);

    v_5th_Comma := INSTR(v_line, ',' ,1 , 5);

    v_empno := to_number(SUBSTR(v_line, 1, v_1st_Comma-1));

    v_ename := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1);

    v_job := SUBSTR(v_line, v_2nd_comma+1, v_3rd_Comma-v_2nd_Comma-1);

    v_mgr := to_number(SUBSTR(v_line, v_3rd_comma+1, v_4th_Comma-v_3rd_Comma-1));

    v_hiredate := to_date(SUBSTR(v_line, v_4th_comma+1, v_5th_Comma-v_4th_Comma-1),'DD-MON-YYYY');


    DBMS_OUTPUT.PUT_LINE(v_empno ||' '|| v_ename || ' ' || v_job || ' ' || v_mgr ||' ' || v_hiredate);

    INSERT INTO emp1

    VALUES (v_empno,v_ename);

    INSERT INTO EMP2(v_job,v_mgr,v_hiredate);

    EXCEPTION
            WHEN NO_DATA_FOUND THEN
              EXIT;

    commit;
    END LOOP;

    UTL_FILE.FCLOSE(v_file);

    EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE( 'ERROR -->' ||  SQLERRM);


    end;
    /*

    EXCEPTION
      WHEN utl_file.invalid_mode THEN
        DBMS_OUTPUT.PUT_LINE( 'Invalid Mode ');
      WHEN utl_file.invalid_path THEN
        DBMS_OUTPUT.PUT_LINE( 'Invalid File Location');
      WHEN utl_file.invalid_filehandle THEN
       DBMS_OUTPUT.PUT_LINE( 'Invalid Filehandle');
      WHEN utl_file.invalid_operation THEN
       DBMS_OUTPUT.PUT_LINE(Invalid Operation');
      WHEN utl_file.read_error THEN
       DBMS_OUTPUT.PUT_LINE( 'Read Error');
      WHEN utl_file.file_open THEN
        DBMS_OUTPUT.PUT_LINE( 'File Already Opened');
      WHEN utl_file.invalid_filename THEN
      DBMS_OUTPUT.PUT_LINE('Invalid File Name');
    END;
    */

  • 6. Re: how to load data using Utl_file
    898862 Newbie
    Currently Being Moderated

    Will the below code works.....pls assist

     

    CREATE OR REPLACE PROCEDURE load_data(errbuff varchar2,errcode number)

    AS

    v_line VARCHAR2(2000); -- read from input file

    v_file UTL_FILE.FILE_TYPE; --   file handler

    v_dir VARCHAR2(250);

    v_filename VARCHAR2(50); 

    v_1st_Comma number;

    v_2nd_Comma number;

    v_3rd_Comma number;

    v_4th_Comma number;

    v_5th_Comma number;

    v_empno sample_emp.empno%type;

    v_ename sample_emp.ename%type;

    v_job sample_emp.job%type;

    v_mgr sample_emp.mgr%type;

    v_hiredate sample_emp.hiredate%type;


    BEGIN

    v_dir := '/usr/tmp';

    v_filename := 'sample.dat';

    v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'r');

     

    LOOP

    BEGIN

    UTL_FILE.GET_LINE(v_file, v_line);

    EXCEPTION

    WHEN no_data_found THEN

    exit;

    END;

     

    v_1st_Comma := INSTR(v_line, ',' ,1 , 1);

    v_2nd_Comma := INSTR(v_line, ',' ,1 , 2);

    v_3rd_Comma := INSTR(v_line, ',' ,1 , 3);

    v_4th_Comma := INSTR(v_line, ',' ,1 , 4);

    v_5th_Comma := INSTR(v_line, ',' ,1 , 5);

    v_empno := to_number(SUBSTR(v_line, 1, v_1st_Comma-1));

    v_ename := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1);

    v_job := SUBSTR(v_line, v_2nd_comma+1, v_3rd_Comma-v_2nd_Comma-1);

    v_mgr := to_number(SUBSTR(v_line, v_3rd_comma+1, v_4th_Comma-v_3rd_Comma-1));

    v_hiredate := to_date(SUBSTR(v_line, v_4th_comma+1, v_5th_Comma-v_4th_Comma-1),'DD-MON-YYYY');


    DBMS_OUTPUT.PUT_LINE(v_empno ||' '|| v_ename || ' ' || v_job || ' ' || v_mgr ||' ' || v_hiredate);

    INSERT INTO emp1

    VALUES (v_empno,v_ename);

    INSERT INTO EMP2(v_job,v_mgr,v_hiredate);

    EXCEPTION
            WHEN NO_DATA_FOUND THEN
              EXIT;

    commit;
    END LOOP;

    UTL_FILE.FCLOSE(v_file);

    EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE( 'ERROR -->' ||  SQLERRM);


    end;
    /*

    EXCEPTION
      WHEN utl_file.invalid_mode THEN
        DBMS_OUTPUT.PUT_LINE( 'Invalid Mode ');
      WHEN utl_file.invalid_path THEN
        DBMS_OUTPUT.PUT_LINE( 'Invalid File Location');
      WHEN utl_file.invalid_filehandle THEN
       DBMS_OUTPUT.PUT_LINE( 'Invalid Filehandle');
      WHEN utl_file.invalid_operation THEN
       DBMS_OUTPUT.PUT_LINE(Invalid Operation');
      WHEN utl_file.read_error THEN
       DBMS_OUTPUT.PUT_LINE( 'Read Error');
      WHEN utl_file.file_open THEN
        DBMS_OUTPUT.PUT_LINE( 'File Already Opened');
      WHEN utl_file.invalid_filename THEN
      DBMS_OUTPUT.PUT_LINE('Invalid File Name');
    END;
    */

  • 7. Re: how to load data using Utl_file
    EdStevens Guru
    Currently Being Moderated

    898862 wrote:

     

    Will the below code works.....pls assist

     

    CREATE OR REPLACE PROCEDURE load_data(errbuff varchar2,errcode number)

    AS

    v_line VARCHAR2(2000); -- read from input file

    v_file UTL_FILE.FILE_TYPE; --   file handler

    v_dir VARCHAR2(250);

    v_filename VARCHAR2(50);

    v_1st_Comma number;

    v_2nd_Comma number;

    v_3rd_Comma number;

    v_4th_Comma number;

    v_5th_Comma number;

    v_empno sample_emp.empno%type;

    v_ename sample_emp.ename%type;

    v_job sample_emp.job%type;

    v_mgr sample_emp.mgr%type;

    v_hiredate sample_emp.hiredate%type;


    BEGIN

    v_dir := '/usr/tmp';

    v_filename := 'sample.dat';

    v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'r');

     

    LOOP

    BEGIN

    UTL_FILE.GET_LINE(v_file, v_line);

    EXCEPTION

    WHEN no_data_found THEN

    exit;

    END;

     

    v_1st_Comma := INSTR(v_line, ',' ,1 , 1);

    v_2nd_Comma := INSTR(v_line, ',' ,1 , 2);

    v_3rd_Comma := INSTR(v_line, ',' ,1 , 3);

    v_4th_Comma := INSTR(v_line, ',' ,1 , 4);

    v_5th_Comma := INSTR(v_line, ',' ,1 , 5);

    v_empno := to_number(SUBSTR(v_line, 1, v_1st_Comma-1));

    v_ename := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1);

    v_job := SUBSTR(v_line, v_2nd_comma+1, v_3rd_Comma-v_2nd_Comma-1);

    v_mgr := to_number(SUBSTR(v_line, v_3rd_comma+1, v_4th_Comma-v_3rd_Comma-1));

    v_hiredate := to_date(SUBSTR(v_line, v_4th_comma+1, v_5th_Comma-v_4th_Comma-1),'DD-MON-YYYY');


    DBMS_OUTPUT.PUT_LINE(v_empno ||' '|| v_ename || ' ' || v_job || ' ' || v_mgr ||' ' || v_hiredate);

    INSERT INTO emp1

    VALUES (v_empno,v_ename);

    INSERT INTO EMP2(v_job,v_mgr,v_hiredate);

    EXCEPTION
            WHEN NO_DATA_FOUND THEN
              EXIT;

    commit;
    END LOOP;

    UTL_FILE.FCLOSE(v_file);

    EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE( 'ERROR -->' ||  SQLERRM);


    end;
    /*

    EXCEPTION
      WHEN utl_file.invalid_mode THEN
        DBMS_OUTPUT.PUT_LINE( 'Invalid Mode ');
      WHEN utl_file.invalid_path THEN
        DBMS_OUTPUT.PUT_LINE( 'Invalid File Location');
      WHEN utl_file.invalid_filehandle THEN
       DBMS_OUTPUT.PUT_LINE( 'Invalid Filehandle');
      WHEN utl_file.invalid_operation THEN
       DBMS_OUTPUT.PUT_LINE(Invalid Operation');
      WHEN utl_file.read_error THEN
       DBMS_OUTPUT.PUT_LINE( 'Read Error');
      WHEN utl_file.file_open THEN
        DBMS_OUTPUT.PUT_LINE( 'File Already Opened');
      WHEN utl_file.invalid_filename THEN
      DBMS_OUTPUT.PUT_LINE('Invalid File Name');
    END;
    */

     

    repeating the same question after 4 minutes doesn't get a faster response.

     

    Using utl_file is very probably the worst possible means of accomplishing your task.  Why are you so wedded to that solution?

     

    As for your immediate question of "Will the below code works" .... what does it cost you to try it for yourself and see for yourself if it works?  If you had done that you would already know if "the below code works".

  • 8. Re: how to load data using Utl_file
    Karthick_Arp Guru
    Currently Being Moderated

    UTL_FILE is a very bad choice for what you are trying to do. Unless you have a very good reason (I am sure there is not one) don't use it. Use only external table.

     

    And as far as your code only one thing caught my eye

     

    EXCEPTION
       WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE( 'ERROR -->' ||  SQLERRM);

     

    This is almost and always a BUG. Never use WHEN OTHERS exception without RAISE.

  • 9. Re: how to load data using Utl_file
    BluShadow Guru Moderator
    Currently Being Moderated

    898862 wrote:

     

    will the below code will work .pls assist

     

     

     

    No it won't.  Your execution blocks and exception handling is all over the place.

    There is also nothing in your code that would suggest using UTL_FILE is required in preference to doing it properly using External Tables.

    You're giving yourself a harder task using UTL_FILE as well as slowing down the processing of the file, compared to how External Tables would do it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points