UTL_FILE to read a textfile and load in table :

user9974866

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;

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

Alex.Zaballa

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;

Alex.Zaballa

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

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

Alex.Zaballa

Just 3 variables.

vRow is the current line from the file

v1 is the empno

v2 is the emp name

User_JOHIR

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

Please suggest..

Alex.Zaballa

Something like this?

https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql