10 Comments

  1. mahaboob
    May 21, 2011 @ 11:28 am

    hi

  2. vimal
    July 20, 2011 @ 4:13 pm

    Hi, the program is show warning pls verify and send to me

  3. jey anand
    July 26, 2011 @ 12:37 pm

    The code works and helps me a lot.

  4. lucky
    August 5, 2011 @ 9:03 pm

    hii pls can you give us a example for pulling data from a table on to a output file

  5. MAHESH REDDY
    December 21, 2011 @ 11:02 pm

    Hi Sir
    Pls give the sample code for write(OUT BOUND).

  6. Ahmed El-Demasy
    May 5, 2012 @ 3:08 pm

    DECLARE
    v_line VARCHAR2 (2000); — Data line read from input file
    v_file UTL_FILE.file_type; — Data file handle
    v_dir VARCHAR2 (250); — Directory containing the data file
    v_filename VARCHAR2 (50); — Data filename
    v_1st_comma NUMBER;
    v_2nd_comma NUMBER;
    v_3rd_comma NUMBER;
    v_4th_comma NUMBER;
    v_5th_comma NUMBER;
    v_empno scott.emp.empno%TYPE;
    v_ename scott.emp.ename%TYPE;
    v_job scott.emp.job%TYPE;
    v_mgr scott.emp.mgr%TYPE;
    v_hiredate scott.emp.hiredate%TYPE;
    –v_sal sample_emp.sal%type;
    BEGIN
    v_dir := ‘D:Test’;
    v_filename := ‘domssa_sample.dat’;
    v_file := UTL_FILE.fopen (v_dir, v_filename, ‘r’);

    — ——————————————————–

    — Loop over the file, reading in each line. GET_LINE will

    — raise NO_DATA_FOUND when it is done, so we use that as

    — the exit condition for the loop.

    — ——————————————————–
    LOOP
    BEGIN
    UTL_FILE.get_line (v_file, v_line);
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    EXIT;
    END;

    — ———————————————————-

    — Each field in the input record is delimited by commas. We

    — need to find the locations of the two commas in the line,

    — and use these locations to get the fields from v_line.

    — ———————————————————-
    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’
    );
    — v_sal := to_number(SUBSTR(v_line, v_5th_comma+1),’99999′);
    DBMS_OUTPUT.put_line ( v_empno
    || ‘ ‘
    || v_ename
    || ‘ ‘
    || v_job
    || ‘ ‘
    || v_mgr
    || ‘ ‘
    || v_hiredate
    );

    — ——————————————

    — Insert the new record into the DEPT table.

    — ——————————————
    — INSERT INTO sample_emp
    — VALUES (v_empno, v_ename, v_job, v_mgr, v_hiredate);
    END LOOP;

    UTL_FILE.fclose (v_file);
    — COMMIT;
    END;

    ———–

    ORA-29280: invalid directory path
    ORA-06512: at “SYS.UTL_FILE”, line 29
    ORA-06512: at “SYS.UTL_FILE”, line 448
    ORA-06512: at line 20

  7. Archana
    May 18, 2012 @ 1:00 pm

    Hi,

    Can anybody tell me if we can write data in a formatted way in excel with the help utl_file.

    Regards

  8. kprk
    July 9, 2012 @ 7:11 pm

    You might have created directory using create directory.

  9. Thamizhan
    October 9, 2012 @ 1:03 am

    Please use Unix Directory where DB is installed for above code. If you face any issue, please contact your DBA for help.

  10. Muthukumar
    March 12, 2013 @ 2:33 pm

    Hello all,

    I completed whatever mentioned above and completed successfully.

    But I checked the table sample_emp, no rows exists..

    Please can you explain why?

    Thanks and Regards,
    Muthu

Back to Top