Home » SQL & PL/SQL » SQL & PL/SQL » oracle external table issue (oracle 11g , pl/sql, sql)
oracle external table issue [message #663358] Wed, 31 May 2017 09:08 Go to next message
POGAKU_SANTHOSH
Messages: 18
Registered: April 2017
Junior Member
I have an ORACLE EXTERNAL TABLE which loads the files data from a path . But the file name which is present in the path will be modified for every 5 minutes only the time stamp of the file will be changed in an regular intervals. Sample file name : 'DATA_31052017032525.txt'.

--The highlighted path will be changed in regular intervals.

How can i handle this scenario in the ORACLE EXTARNAL TABLE SCRIPT instead of modifying the file name in the script of the oet table for every 5 minutes.

[Updated on: Wed, 31 May 2017 09:10]

Report message to a moderator

Re: oracle external table issue [message #663359 is a reply to message #663358] Wed, 31 May 2017 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

What is OS name & version of the database server system?
Re: oracle external table issue [message #663360 is a reply to message #663359] Wed, 31 May 2017 09:31 Go to previous messageGo to next message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use alter table <external table name> location (location name)
See the docs: https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm#ADMIN11706
Re: oracle external table issue [message #663363 is a reply to message #663360] Wed, 31 May 2017 09:59 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
see the following link from asktom. While oracle can't get a file list Java in the database can. In your routine you can simply get a current file list and use an execute immediate to do an alter command like cookiemonster suggested and read in your table

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584
Re: oracle external table issue [message #663364 is a reply to message #663363] Wed, 31 May 2017 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
I would use a file system soft link such that the PL/SQL always accesses the same "filename"
The script that deposits the file into the directory changes the softlink to point to the new file name.
Re: oracle external table issue [message #663452 is a reply to message #663358] Fri, 02 June 2017 19:03 Go to previous message
Barbara Boehmer
Messages: 8779
Registered: November 2002
Location: California, USA
Senior Member
If you upgrade to Oracle 12c, then you can use wildcards like * in the location, like:

location('DATA_*.txt')

Previous Topic: Sequence generation for Parallel Processing
Next Topic: dbms_output.put_line for dynamic sql query
Goto Forum:
  


Current Time: Wed Dec 13 05:22:54 CST 2017

Total time taken to generate the page: 0.01772 seconds