PL/SQL UTL_FILE create text file problem 1

[tmorf]

I am trying to create a flat file. I included my pl/sql code below also I'm just a starting to learn pl/sql. I found some examples on this web site and in a oracle pl/sql book but can't fix my problem. The program runs with no errors but does not produce a flat file on my c drive. Any help would be appriciated.

set serveroutput on;

create or replace procedure itt_fixed_file as
----------------Storage Variables ------------------------
n number := 0; error_msg VARCHAR2(300) := SQLERRM;

--------------UTL FILE Variables----------------------
p_file_name VARCHAR2(20); p_file_dir VARCHAR2(70);
v_file_handle UTL_FILE.FILE_TYPE;

----------------------Cursor-------------------------------
CURSOR itt_rec IS
SELECT empl_id
FROM empl;
file_rec itt_rec%ROWTYPE;

----------------Main Program Logic---------------------
begin
DBMS_OUTPUT.ENABLE(50000);

p_file_name := 'outputfile.txt';
p_file_dir := 'c:';
v_file_handle := UTL_FILE.FOPEN(p_file_dir, p_file_name, 'w',1000);

FOR file_rec IN itt_rec
LOOP
UTL_FILE.PUT_LINE(v_file_handle,file_rec.empl_id);
message('loopping');
END LOOP;

UTL_FILE.FCLOSE(v_file_handle);
--

EXCEPTION --Error Handler

WHEN TOO_MANY_ROWS THEN
dbms_output.PUT_LINE('Too Many Rows');
WHEN NO_DATA_FOUND THEN
dbms_output.PUT_LINE('No Data Found');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.invalid_path');
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.invalid_mode');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.invalid_filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20000, 'utl_file.invalid_operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
WHEN utl_file.write_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1,255));

END;
/

 

[dbtoo2001]

Is utl_file_dir set in the init.ora file for the database?

If the parameter initialization file contains only (UNIX example):

UTL_FILE_DIR=/appl/gl/log
UTL_FILE_DIR=/appl/gl/out

 

[tmorf]

Not sure what the init.ora file is or where to find it. I'm just trying to do a select from a database table and create a text file on my pc. I thought defining them in the program would be all I needed.

p_file_name := 'outputfile.txt';
p_file_dir := 'c:';
v_file_handle := UTL_FILE.FOPEN (p_file_dir,p_file_name, 'w',1000);

 

[Beilstwh]

Utl_file ONLY works on the database server. It can NOT write to a client PC.

Bill
Oracle DBA/Developer
New York State, USA

 

[tmorf]

Thank you for this information I did not see this in my Pl/sql book. I was wondering if there is a command I could use to write a test file to my pc. I will be running this program once a week so it would be nice to have it write to my pc.

 

[Beilstwh]

The easiest way is to use spooling. The following script illustrates the technique


set pagesize 0
set linesize 1000
set trimspool on
set termout off
spool c:\outputfile.txt

SELECT empl_id FROM empl order by empl_id;

spool off
set termout on

save the above commands into a file called (for example) c:\doit.sql

Your would then login into sql and type @c:\doit.sql

When the script was done, your file on the client would be created.





Bill
Oracle DBA/Developer
New York State, USA