Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, santhosh.

Asked: December 14, 2016 - 6:14 pm UTC

Last updated: December 15, 2016 - 2:54 am UTC

Version: 11g

Viewed 1000+ times

You Asked

hi tom,

I am using utl_file package to create a sample file in the respective directory and i am using oracle 11g in windows environment.

Query: CREATE OR REPLACE DIRECTORY ORA_DIR AS 'C:\app\santh\product\11.2.0\dbhome_1';

The above query i have used to create a directory for the below procedure to execute:

Procedure:

CREATE OR REPLACE PROCEDURE WRITE_FILE_DIR
AS
f_line varchar2(200);
v_file utl_file.file_type;
f_dir varchar2(200);
fname varchar2(200);
cursor c1 is select * from HR.employees;
begin
dbms_output.put_line('Begin the file to copy');
fname := 'employee.csv';
v_file := utl_file.fopen('ORA_DIR',fname,'W');
dbms_output.put_line('Begin loop');
for cur_rec in c1
loop
utl_file.put_line(v_file,cur_rec.EMPLOYEE_ID||','||cur_rec.FIRST_NAME||','||cur_rec.LAST_NAME||','||cur_rec.EMAIL||','||cur_rec.PHONE_NUMBER);
end loop;
utl_file.fclose(v_file);
commit;
end write_file_dir;


After execution of the above procedure i am unable to get the csv file in the respective directory location which i have created.

and Connor said...

There is nothing wrong with your code - try a different location

SQL> CREATE OR REPLACE DIRECTORY ORA_DIR AS 'C:\temp';

Directory created.

SQL>
SQL>
SQL> declare
  2    f_line varchar2(200);
  3    v_file utl_file.file_type;
  4    f_dir varchar2(200);
  5    fname varchar2(200);
  6    cursor c1 is select * from HR.employees;
  7  begin
  8    dbms_output.put_line('Begin the file to copy');
  9    fname := 'employee.csv';
 10    v_file := utl_file.fopen('ORA_DIR',fname,'W');
 11    dbms_output.put_line('Begin loop');
 12    for cur_rec in c1
 13    loop
 14      utl_file.put_line(v_file,cur_rec.EMPLOYEE_ID||','||cur_rec.FIRST_NAME||','||cur_rec.LAST_NAME||','||cur_rec.EMAIL||','||cur_rec.PHONE_NUMBER);
 15    end loop;
 16    utl_file.fclose(v_file);
 17  end;
 18  /

PL/SQL procedure successfully completed.

SQL> host dir c:\temp\emp*
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of c:\temp

15/12/2016  10:46 AM             4,440 employee.csv
               1 File(s)          4,440 bytes
               0 Dir(s)  29,295,226,880 bytes free

SQL> host cat c:\temp\employee.csv
100,Steven,King,SKING,515.123.4567
101,Neena,Kochhar,NKOCHHAR,515.123.4568
102,Lex,De Haan,LDEHAAN,515.123.4569
103,Alexander,Hunold,AHUNOLD,590.423.4567
104,Bruce,Ernst,BERNST,590.423.4568
105,David,Austin,DAUSTIN,590.423.4569
106,Valli,Pataballa,VPATABAL,590.423.4560
107,Diana,Lorentz,DLORENTZ,590.423.5567
108,Nancy,Greenberg,NGREENBE,515.124.4569
109,Daniel,Faviet,DFAVIET,515.124.4169
110,John,Chen,JCHEN,515.124.4269
...




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library