Home » SQL & PL/SQL » SQL & PL/SQL » dynamic flat file name (merged)
icon4.gif  dynamic flat file name (merged) [message #290855] Tue, 01 January 2008 11:18 Go to next message
gyankr
Messages: 11
Registered: January 2008
Junior Member
Hi,

I am generating a flat file using utl_file package in plsql.I want the file name to have the date as a part of its name (like filename_date).

Can you please let me know if this can be done on the plsql procedure.

Regards,
Gyan
Re: dynamic flat file name [message #290856 is a reply to message #290855] Tue, 01 January 2008 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your problem in using SYSDATE and add it to the file name?

Regards
Michel
Re: dynamic flat file name [message #290916 is a reply to message #290856] Wed, 02 January 2008 03:03 Go to previous messageGo to next message
gyankr
Messages: 11
Registered: January 2008
Junior Member
Yes sire,got it. i used something like this

UTL_FILE.FOPEN('TST','po-'|| to_char(sysdate, 'mm-dd-yyyy'),'W');

Thanks,
Gyan
Re: dynamic flat file name [message #290921 is a reply to message #290916] Wed, 02 January 2008 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback, we appreciate it.

Regards
Michel
icon11.gif  not able to create flat file [message #291757 is a reply to message #290855] Sun, 06 January 2008 11:47 Go to previous messageGo to next message
gyankr
Messages: 11
Registered: January 2008
Junior Member
Hi,
I am trying to create a file dynamically on a unix server using plsql.
When i run the procedure,i am getting the below error.
"ORA-20103: Invalid Operation"
I also created a temporary file (with permissions 777) and yet nothing was written in the file.
Please find the plsql procedure below:

CREATE OR REPLACE procedure schema.WriteProcedure
is
f UTL_FILE.FILE_TYPE;
var_id number(22);
var_intid varchar2(20);

cursor file_cur is select col1,col2 from schema.table;

begin


f := UTL_FILE.FOPEN('/home/gyan/tst','Dynamic-file-'|| to_char(sysdate, 'mm-dd-yyyy'),'W');

open file_cur;
loop
fetch file_cur into var_id,var_intid;
exit when file_cur%notfound;

UTL_FILE.PUT_LINE(f,var_id || '|' || var_intid );

end loop;
close file_cur;
UTL_FILE.FCLOSE(f);
UTL_FILE.FCLOSE_ALL;

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');
WHEN UTL_FILE.READ_ERROR THEN
RAISE_APPLICATION_ERROR(-20104,'Read Error');
WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20105,'Write Error');
WHEN UTL_FILE.INTERNAL_ERROR THEN
RAISE_APPLICATION_ERROR(-20106,'Internal Error');
WHEN VALUE_ERROR THEN
RAISE_APPLICATION_ERROR(-20108,'Value Error');
WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation ');
when others then
dbms_output.put_line('done');

end;
/

Is this a filelock,how to go about debugging?


Regards,
Gyan
Re: not able to create flat file [message #291760 is a reply to message #291757] Sun, 06 January 2008 11:53 Go to previous messageGo to next message
gyankr
Messages: 11
Registered: January 2008
Junior Member
And yes,i have created the (utl) directory and granted read,write access.

Regards,
Gyan
Re: not able to create flat file [message #291761 is a reply to message #291757] Sun, 06 January 2008 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the exception clause the only thing it makes is that it hides the line number where the error raises.

Also
please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Post execution formatted and with line numbers.

Regards
Michel
Re: not able to create flat file [message #292042 is a reply to message #291761] Mon, 07 January 2008 10:08 Go to previous messageGo to next message
gyankr
Messages: 11
Registered: January 2008
Junior Member
Hello everybody,

I removed all the exceptions and looks like i have to login as the "system" user to execute the procedure successfully(even though i ran "grant read,write on utl_directory" to public).Am i missing something?

Regards,
Gyan
Re: not able to create flat file [message #292049 is a reply to message #292042] Mon, 07 January 2008 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Am i missing something?

Yes, to post the execution.

Regards
Michel
Re: dynamic flat file name (merged) [message #292076 is a reply to message #290855] Mon, 07 January 2008 13:11 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Did you define the directory "/home/gyan/tst" on THE DATABASE SERVER? in your pfile?
Previous Topic: Update statement using case with two tables
Next Topic: ORA-01403: no data found
Goto Forum:
  


Current Time: Thu Dec 08 20:08:50 CST 2016

Total time taken to generate the page: 0.14164 seconds