Home » SQL & PL/SQL » SQL & PL/SQL » How to read dynamic flat file in PL/SQL (Oracle PL/SQL)
How to read dynamic flat file in PL/SQL [message #389430] Mon, 02 March 2009 01:16 Go to next message
jeeling
Messages: 7
Registered: March 2009
Location: US
Junior Member
Hello everyone,

May I know how to read a dynamic flat file in PL/SQL?
Example: filename_date_time(ABC_020309_152445 - File ABC, dated 2nd of March, 2009, 3:24:45pm)

Regards,
Jeeling
Re: How to read dynamic flat file in PL/SQL [message #389437 is a reply to message #389430] Mon, 02 March 2009 01:32 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: How to read dynamic flat file in PL/SQL [message #389438 is a reply to message #389430] Mon, 02 March 2009 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many answers to this question in Server Utilities and Unix forum.

Regards
Michel
Re: How to read dynamic flat file in PL/SQL [message #389448 is a reply to message #389438] Mon, 02 March 2009 02:32 Go to previous messageGo to next message
jeeling
Messages: 7
Registered: March 2009
Location: US
Junior Member
Hi Michel,

I only manage to find the below link, which the file name concatenate with system date, but without the time. Can you help to pinpoint which link exactly explained how to read dynamic file name in pl/sql?

Thank you in advance.

Regards,
Jeeling

http://www.orafaq.com/forum/m/290855/138412/?srch=dynamic+file+name#msg_290855
Re: How to read dynamic flat file in PL/SQL [message #389451 is a reply to message #389448] Mon, 02 March 2009 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you get the file name you want to read?

Regards
Michel
Re: How to read dynamic flat file in PL/SQL [message #389591 is a reply to message #389451] Mon, 02 March 2009 18:54 Go to previous messageGo to next message
jeeling
Messages: 7
Registered: March 2009
Location: US
Junior Member
Hi,

I append date and time to the file name. Example: rate_25022009_08:24:56 (rate file generated on 25th of Feb, 2009, 8:24:56am)

I am actually wanted to read the file using UTL_FILE function (UTIL_FILE.FOPEN) which will read the exact file name, but the file that I wanted to read is in dynamic file name, every second will changed.

Regards,
Jeeling
Re: How to read dynamic flat file in PL/SQL [message #389592 is a reply to message #389430] Mon, 02 March 2009 19:02 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I wanted to read is in dynamic file name, every second will changed.
So please proceed to do so.
Nobody here is stopping you from pursuing this "unusual design".

What happens if it takes more than 1 second to load the file?


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Mon, 02 March 2009 19:30]

Report message to a moderator

Re: How to read dynamic flat file in PL/SQL [message #389614 is a reply to message #389591] Mon, 02 March 2009 22:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(sysdate,'DDMMYYYY_HH24:MI:SS') curdate from dual
  2  /
CURDATE
-----------------
03032009_05:56:07

1 row selected.

Is this what you want?

Regards
Michel
Re: How to read dynamic flat file in PL/SQL [message #389674 is a reply to message #389614] Tue, 03 March 2009 02:43 Go to previous messageGo to next message
jeeling
Messages: 7
Registered: March 2009
Location: US
Junior Member
Hi Michel,

Thanks for your prompt reply.
What i want is get a dynamic file name in the server.

I am using UTL_FILE.FOPEN to read a file, but unfortunately, the file name that I read is not a static file name whereby the file will named based on the time stamp when it is generated by a thrid party system. For example, for today I want to read a file called rate_25022009_08:24:56.txt (rate file generated on 25th of Feb, 2009, 8:24:56am), but tomorrow,that file maybe named as rate_26022009_16:20:56.txt (rate file generated on 26th of Feb, 2009, 4:20:56pm).

I tried use wild card in my file name, but it doesn't work:

UTL_FILE.FOPEN('/usr/tmp/', 'rate*', 'R');

Thanks and regards,
See Kay



Re: How to read dynamic flat file in PL/SQL [message #389679 is a reply to message #389674] Tue, 03 March 2009 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could PL/SQL know the date?

Regards
Michel
Re: How to read dynamic flat file in PL/SQL [message #389680 is a reply to message #389674] Tue, 03 March 2009 02:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ahh - so what you actually want is a way to read the filenames from a directory - is that correct?
Re: How to read dynamic flat file in PL/SQL [message #389681 is a reply to message #389680] Tue, 03 March 2009 03:05 Go to previous messageGo to next message
jeeling
Messages: 7
Registered: March 2009
Location: US
Junior Member
Hi Michel

I can concatenate the sysdate to the file name, because third party system will generate the file daily.

So if I want to read rate_25022009_08:24:56.txt, I can code something like:

file_name := "rate"||sysdate||timestamp

But the problem is I do not know the timestamp and UTL_FILE.FOPEN cannot use wildcard

Thanks and regards,
See Kay
Re: How to read dynamic flat file in PL/SQL [message #389682 is a reply to message #389680] Tue, 03 March 2009 03:09 Go to previous messageGo to next message
jeeling
Messages: 7
Registered: March 2009
Location: US
Junior Member
Hi JRowbottom,

Yes, I want to find out a way to read a dynamic file names in a directory, which the file name will change based on the system timestamp.

Thanks and regards,
Re: How to read dynamic flat file in PL/SQL [message #389690 is a reply to message #389681] Tue, 03 March 2009 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But the problem is I do not know the timestamp and UTL_FILE.FOPEN cannot use wildcard

Search on AskTom for "dirlist".

Regards
Michel
Re: How to read dynamic flat file in PL/SQL [message #389865 is a reply to message #389690] Tue, 03 March 2009 17:52 Go to previous message
jeeling
Messages: 7
Registered: March 2009
Location: US
Junior Member
Hi Michel,

My problem solved using the Java "dirlist".

Thanks a lot.

Regards,
Jeeling
Previous Topic: Sorting and Sorting in oracle (2 threads merged by bb)
Next Topic: question based on Oracle PL/SQL Programming book code
Goto Forum:
  


Current Time: Sat Dec 03 22:10:37 CST 2016

Total time taken to generate the page: 0.08935 seconds