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!
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:
'A'
append mode.What is the error you get? Can you paste the code you are using and what you get? A full run detail?