OraFAQ Forum: SQL & PL/SQL » oracle external table issue

Home » SQL & PL/SQL » SQL & PL/SQL » oracle external table issue (oracle 11g , pl/sql, sql)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

oracle external table issue [message #663358]

Wed, 31 May 2017 09:08

POGAKU_SANTHOSH
Messages: 39
Registered: April 2017

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

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: oracle external table issue [message #663360 is a reply to message #663359]

Wed, 31 May 2017 09:31

cookiemonster
Messages: 13920
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

Report message to a moderator

Re: oracle external table issue [message #663363 is a reply to message #663360]

Wed, 31 May 2017 09:59

Bill B
Messages: 1971
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

Report message to a moderator

Re: oracle external table issue [message #663364 is a reply to message #663363]

Wed, 31 May 2017 10:13

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: oracle external table issue [message #663452 is a reply to message #663358]

Fri, 02 June 2017 19:03

Barbara Boehmer
Messages: 9090
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')

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 16:17:51 CDT 2024