2

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!

Vikram
  • 8,235
  • 33
  • 47
user1249603
  • 31
  • 1
  • 1
  • 4

2 Answers2

5

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?

Guru
  • 2,331
  • 6
  • 31
  • 48
  • does this code mean that if the file does not exist it will automatically be created? and how to access the list of files in oracle server? I only know SELECT * FROM ALL_DIRECTORIES. This only retrieves the list of directory, I wanna know the files in each directory too (Though I should have ask this in a different thread...) I just want to make sure if the file is created. though I still have the same error.. invalid file operation... sys.utl_file... thank you very much! – user1249603 Mar 06 '12 at 02:59
  • using oracle 10g.Here's my code: `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; /` error is "ORA-29283: invalid file operation | ORA-06512: at SYS.UTL_FILE, line 475" | ORA-29283: invalid file operation | ORA-06512 at SCHEMA_NAME.REFCURSORKIM, line 9 | OR-06512: at line2 – user1249603 Mar 06 '12 at 03:31
  • Do you have access to `'UTL_DIR'`? Can you try writing into `'/tmp'`? – Guru Mar 06 '12 at 04:56
  • I tried writing but I still got the same error... Well I guess I have just to use another method other than UTL_FILE. Thank you very much for your time. if i have more time, i'll study UTL_FILE more. :) God bless! – user1249603 Mar 06 '12 at 06:17
  • Can you share some direction towards creating the file on to AWS S3? – Ak777 Mar 02 '22 at 21:28
0

'UTL_DIR' need to be created in the Directory.

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

Hema
  • 1