Oracle - Create text file using stored procedure

Question

Need help on creating text file using stored procedure in oracle 10. I'm very new to this so please guide me from the start.

I found code and created it as stored procedure. I run it and it is VALID. But I executed it, I got error of "INVALID FILE NAME... AT SYS.UTL_FILE..."

I first created a directory that grants read, write on directory.

CREATE OR REPLACE PROCEDURE refcursorkim IS
   l_file      utl_file.file_type;
   l_file_name VARCHAR2(60);
BEGIN
l_file := utl_file.fopen('UTL_DIR', l_file_name, 'w');          
   SELECT 'KY' || TO_CHAR(SYSDATE, 'yyyymmdd') || '1.txt' INTO l_file_name  FROM dual;
   utl_file.putf(l_file,l_file_name);
   utl_file.fclose(l_file);
END refcursorkim;

I don't know what I'm missing, since I'm not familiar to this at all. Another thing, is the file automatically being created in the directory specified?

Hope you could help me. Thank you and God bless!

Answer 1

You should open the file with proper file name.

Your code (will error out):

CREATE OR REPLACE PROCEDURE refcursorkim
IS
   l_file        UTL_FILE.file_type;
   l_file_name   VARCHAR2 (60);
BEGIN
   l_file := UTL_FILE.fopen ('UTL_DIR', l_file_name, 'w');

   SELECT 'KY' || TO_CHAR (SYSDATE, 'yyyymmdd') || '1.txt'
     INTO l_file_name
     FROM DUAL;

   UTL_FILE.putf (l_file, l_file_name);
   UTL_FILE.fclose (l_file);
END refcursorkim;

Should be (Corrected):

CREATE OR REPLACE PROCEDURE refcursorkim
IS
   l_file        UTL_FILE.file_type;
   l_file_name   VARCHAR2 (60);
BEGIN

   SELECT 'KY' || TO_CHAR (SYSDATE, 'yyyymmdd') || '1.txt'
     INTO l_file_name
     FROM DUAL;

   l_file := UTL_FILE.fopen ('UTL_DIR', l_file_name, 'w');

   UTL_FILE.putf (l_file, l_file_name);
   UTL_FILE.fclose (l_file);
END refcursorkim;

More here: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#i1004619

Edit 1:

1. A new file will be created if the file did not exist. If exists, it would replace with a new file. If you need preserve the file, open with 'A' append mode.
2. There is no option within UTL_FILE to browse the directory content. However, there are other options in Oracle 11g. Which version of Oracle are you in? (I will also give you an inside secret that will help you to learn Oracle standard library functions quick. Open the UTL_FILE package specification. They provide nice documentation with enough comments on what is done in the procedure call)

What is the error you get? Can you paste the code you are using and what you get? A full run detail?