CODEWITHSUNDEEP
oracle-databaseutl-file
user1249603
user1249603

Reputation: 31

Oracle - Create text file using stored procedure

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!

Upvotes: 2

Views: 59375

Answers (2)

Hema
Hema

Reputation: 1

'UTL_DIR' need to be created in the Directory.

Hence also required the required permissions to generate the output file in the desired location.

Upvotes: 0

Guru
Guru

Reputation: 2371

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?

Upvotes: 5

Related Questions