Results 1 to 9 of 9

Thread: UTL_FILE Package

  1. #1
    Join Date
    Mar 2004
    Posts
    4

    UTL_FILE Package

    Hi,

    I am looking for some code. Pls help me if you can. I want to write PL/SQL procedures(I have to..recommended) instead of SQL Loader.

    Retrieving data from Flat Files (CSV) and inserting into Oracle 9i Data Base using UTL_FILE Package.

    Example Data : 123, abc

    This data should be inserted in oracle data base table as seperate fields (code, name)


    Thanks
    Last edited by susheela; 03-08-2004 at 03:49 PM.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Can you use SQL Loader?. It is much faster and is built for data loading.

  3. #3
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152
    ** As of 8.0.6, you can have a maximum of 50 files open simultaneously.
    ** As of 9.0.2, UTL_FILE allows file system access for directories
    ** created as database objects. See the CREATE DIRECTORY command.
    ** Directory object names are case sensitive and must match exactly
    ** the NAME string in ALL_DIRECTORIES. The LOCATION parameter may be
    ** either a directory string from the UTL_FILE_DIR init.ora parameter
    ** or a directory object name.

    1.
    --example with utl_file
    DECLARE
    f Utl_File.file_type;
    buffer VARCHAR2(4000);
    BEGIN
    f := Utl_File.fopen('DIRECTORY_DIR','pokus.txt','r');
    IF Utl_File.is_open(f) THEN
    Dbms_Output.put_line('is open');
    BEGIN
    WHILE 1=1 LOOP
    Utl_File.get_line(f,buffer);
    -- now you have to parse the buffer and insert in your table
    Dbms_Output.put_line(buffer);
    END LOOP;
    EXCEPTION
    WHEN No_Data_Found THEN
    Dbms_Output.put_line('End of file');
    WHEN Others THEN
    Dbms_Output.put_line(SQLERRM);
    END;
    ELSE
    Dbms_Output.put_line('is close');
    END IF;
    Utl_File.fclose(f);
    Dbms_Output.put_line('end proc');
    EXCEPTION
    WHEN Others THEN NULL;
    Dbms_Output.put_line(SQLERRM);
    END;

    2.
    -- example with linked textfile
    /*
    create table scott.test (val varchar2(4000))
    organization external
    (
    type oracle_loader
    default directory DIRECTORY_DIR
    access parameters (records delimited by newline fields terminated by ';')
    location ('pokus.txt')
    );
    */
    -- now i can use only select from linked table to get values from my text file..
    SELECT * FROM scott.test;
    You Have To Be Happy With What You Have To Be Happy With (KC)

  4. #4
    Join Date
    Mar 2004
    Posts
    4
    THANKS !!!!

  5. #5
    Join Date
    Mar 2004
    Posts
    4

    Using Arrays in PL/SQL

    Hi,

    I got values using UTL_FILE..

    There are list of values in the line..

    i.e.. 123,abc,eee,..

    now how to put all these values in array variables and insert in oracle db..

    It will be helpful if you reply to this too...


    Thanks

  6. #6
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152
    here you are...
    ---------------------
    DECLARE
    v_comma NUMBER;
    v_start NUMBER := 1;
    v_aline VARCHAR2(100);
    v_empno NUMBER;
    v_ename VARCHAR2(10);
    v_sal NUMBER;
    BEGIN
    v_aline := '1,John,100';
    -- parse the input line to extract column info
    v_comma := INSTR(v_aline, ',' , v_start, 1);
    v_empno := To_Number(SUBSTR(v_aline, v_start, v_comma - v_start));
    v_start := v_comma + 1;
    v_comma := INSTR(v_aline, ',' , v_start, 1);
    v_ename := Trim(SUBSTR(v_aline, v_start, v_comma - v_start));
    v_start := v_comma + 1;
    v_sal := To_Number(SUBSTR(v_aline, v_start, LENGTH(v_aline)));
    -- print values
    Dbms_Output.put_line('VALUES ARE ...'||v_empno||'*'||v_ename||'*'||v_sal);
    --INSERT INTO SalariedEmployee (empno,ename,sal) VALUES (v_empno,v_ename,v_sal);
    END;
    ---------------------
    But, I find it easier to use external table, where you can just select values ...
    create table external_table
    ( empno number, ename varchar2(10), sal number )
    organization external
    (
    type oracle_loader
    default directory DIRECTORY_DIR
    access parameters (records delimited by newline fields terminated by ',')
    location ('pokus.txt')
    );
    insert into SalariedEmployee (empno,ename,sal) select empno,ename,sal from external_table;
    Last edited by YuckFou; 03-10-2004 at 04:05 AM.
    You Have To Be Happy With What You Have To Be Happy With (KC)

  7. #7
    Join Date
    Dec 2006
    Posts
    2
    HAI
    i am looking for some code. Pls help me if you can. I want to write PL/SQL procedures using utl_file i must transfer the os files from one location to another

  8. #8
    Join Date
    Dec 2006
    Posts
    1
    Hi,
    There are two predifned procedures in UTL_FILE Package to move the files from one location to another.

    utl_file.fcopy (
    src_location in varchar2 ,
    src_filename in varchar2 ,
    dest_location in varchar2 ,
    dest_filename in varchar2 ,
    start_line in binary_integer default,
    end_line in binary_integer default
    );

    procedure frename (
    src_location in varchar2 ,
    src_filename in varchar2 ,
    dest_location in varchar2 ,
    dest_filename in varchar2 ,
    overwrite in boolean default
    );

    These two procedures are different with each other.
    FCOPY : It will keep the source file as it is and it creates one more file in destination folder (COPY and PASTE Job).

    FRENAME : It will not keep the original file (CUT and PASTE job)

  9. #9
    Join Date
    Dec 2006
    Posts
    2
    thank u very much for u r reply

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •