Forum Stats

  • 3,769,974 Users
  • 2,253,040 Discussions
  • 7,875,255 Comments

Discussions

UTL_FILE to read a textfile and load in table :

user9974866
user9974866 Member Posts: 128 Blue Ribbon
edited Oct 18, 2018 10:12AM in SQL and PL/SQL (Portuguese)

Hi ,

i have created a file 'writefile' using below and it is created  successfully.

=================================

declare

  ltype utl_file.file_type;

  ldir  varchar2(100) := 'DATA_PUMP_DIR';

  lfile  varchar2(100) := 'writefile.sql';

  s varchar2(10) := ',';

begin

  ltype := utl_file.fopen(ldir,lfile,'w');

  for i in (select * from emp)

    loop

      utl_file.putf(ltype,i.empid||s||i.sal);

      UTL_FILE.NEW_LINE(ltype);

    end loop;

  utl_file.fclose(ltype);

end;

==================================================

out put <writefile.sql>

184,4200

185,4100

186,3400

....

i have created a new table tmp with empid and first_name columns, could you please let me know how could i read this file and load the data into new table tmp ...

i tried using this but its not working..

=================

declare

f UTL_FILE.FILE_TYPE;

ldir varchar2(100) := 'DATA_PUMP_DIR';

lfile varchar2(100) := 'writefile.sql';

s VARCHAR2(200);

BEGIN

f := UTL_FILE.FOPEN(ldir,lfile,'R');

  IF UTL_FILE.IS_OPEN(f) THEN

    LOOP

      BEGIN

        UTL_FILE.GET_LINE(f,lfile);

        IF lfile IS NULL THEN

          EXIT;

        END IF;

      INSERT INTO tmp

      (empid, first_name)

      VALUES

      ( );

      END;

  END LOOP;

  COMMIT;

  END IF;

END;

===========================================

Carina Mendes

Best Answer

  • Alex.Zaballa
    Alex.Zaballa Member Posts: 33 Bronze Badge
    edited Dec 13, 2015 12:24PM Accepted Answer

    You have to put all these values variables and then insert into oracle db.


    Use substr and instr.

    declare

    f UTL_FILE.FILE_TYPE;

    ldir varchar2(100) := 'DATA_PUMP_DIR';

    lfile varchar2(100) := 'writefile.sql';

    s VARCHAR2(200);

    v_row varchar2(2000);

    v1 number;

    v2 varchar2(50);

    BEGIN

    f := UTL_FILE.FOPEN(ldir,lfile,'R');

      IF UTL_FILE.IS_OPEN(f) THEN

        LOOP

          BEGIN

            UTL_FILE.GET_LINE(f,v_row);

           v1:=substr(v_row,1,1);

           v2:=substr(v_row,3,50);

          INSERT INTO tmp

          (empid, first_name)

          VALUES

          (v1,v2);

        EXCEPTION

          WHEN NO_DATA_FOUND THEN

            EXIT;   

        END;

      END LOOP;

      COMMIT;

      END IF;

    END;

    Other way is to use external table:

    File: /tmp/text.txt

    1,"AAA",100

    2,"BBB",200

    3,"CCC",300

    CREATE OR REPLACE DIRECTORY TEST_DIR AS "/tmp";

    GRANT READ, WRITE ON DIRECTORY TEST_DIR TO public;

    CREATE TABLE ext_table

          (id      NUMBER,

           empname VARCHAR2(20),

           rate    NUMBER)

        ORGANIZATION EXTERNAL

          (TYPE ORACLE_LOADER

           DEFAULT DIRECTORY TEST_DIR

           ACCESS PARAMETERS

             (RECORDS DELIMITED BY NEWLINE

             FIELDS TERMINATED BY ","

             OPTIONALLY ENCLOSED BY '"'

             (id,

              empname,

              rate

             )

            )

          LOCATION ('test.txt')

         );

    select * from ext_table;

Answers

  • Alex.Zaballa
    Alex.Zaballa Member Posts: 33 Bronze Badge
    edited Dec 13, 2015 12:24PM Accepted Answer

    You have to put all these values variables and then insert into oracle db.


    Use substr and instr.

    declare

    f UTL_FILE.FILE_TYPE;

    ldir varchar2(100) := 'DATA_PUMP_DIR';

    lfile varchar2(100) := 'writefile.sql';

    s VARCHAR2(200);

    v_row varchar2(2000);

    v1 number;

    v2 varchar2(50);

    BEGIN

    f := UTL_FILE.FOPEN(ldir,lfile,'R');

      IF UTL_FILE.IS_OPEN(f) THEN

        LOOP

          BEGIN

            UTL_FILE.GET_LINE(f,v_row);

           v1:=substr(v_row,1,1);

           v2:=substr(v_row,3,50);

          INSERT INTO tmp

          (empid, first_name)

          VALUES

          (v1,v2);

        EXCEPTION

          WHEN NO_DATA_FOUND THEN

            EXIT;   

        END;

      END LOOP;

      COMMIT;

      END IF;

    END;

    Other way is to use external table:

    File: /tmp/text.txt

    1,"AAA",100

    2,"BBB",200

    3,"CCC",300

    CREATE OR REPLACE DIRECTORY TEST_DIR AS "/tmp";

    GRANT READ, WRITE ON DIRECTORY TEST_DIR TO public;

    CREATE TABLE ext_table

          (id      NUMBER,

           empname VARCHAR2(20),

           rate    NUMBER)

        ORGANIZATION EXTERNAL

          (TYPE ORACLE_LOADER

           DEFAULT DIRECTORY TEST_DIR

           ACCESS PARAMETERS

             (RECORDS DELIMITED BY NEWLINE

             FIELDS TERMINATED BY ","

             OPTIONALLY ENCLOSED BY '"'

             (id,

              empname,

              rate

             )

            )

          LOCATION ('test.txt')

         );

    select * from ext_table;

  • user9974866
    user9974866 Member Posts: 128 Blue Ribbon
    edited Dec 13, 2015 1:08PM

    Thanks Alex, could you please let me know what is the use of vrow , v1 and v2.

  • Alex.Zaballa
    Alex.Zaballa Member Posts: 33 Bronze Badge
    edited Dec 13, 2015 2:57PM

    Just 3 variables.

    vRow is the current line from the file

    v1 is the empno

    v2 is the emp name

    Carina Mendes
  • User_JOHIR
    User_JOHIR Member Posts: 274 Blue Ribbon
    edited Sep 27, 2018 12:13PM

    Hi Alex, without creating a directory can we parse the XML data to oracle table.

    Please suggest..