Home » SQL & PL/SQL » SQL & PL/SQL » How to Delete files from EXT Table Dir through SQL PLUS (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
How to Delete files from EXT Table Dir through SQL PLUS [message #582560] Fri, 19 April 2013 07:42 Go to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Hello Admin,

I am working on an automation process to load EXT Files into an External table and after checking if there is no data in BAD file, creating a native table and indexes out of it.

Is it possible to delete a file from SQL PLUS command within the code to drop a file (bad or log file) from the default file directory defined as

LOCATION (DATA_DIR:'FILE201303.csv')


Thanks a lot.
Re: How to Delete files from EXT Table Dir through SQL PLUS [message #582561 is a reply to message #582560] Fri, 19 April 2013 07:46 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
SQL*Plus runs on the client. If you are using external tables, that happens on the server. Unless this is the same machine, SQL*Plus will have no access to the directory.

You can look at package UTL_FILE for deleting files.

[Updated on: Fri, 19 April 2013 07:48]

Report message to a moderator

Re: How to Delete files from EXT Table Dir through SQL PLUS [message #582562 is a reply to message #582561] Fri, 19 April 2013 07:49 Go to previous messageGo to next message
sulabhagra
Messages: 57
Registered: April 2012
Member
Thanks Joy, so is there any way we can automate the process to drop the files from the server via some SQL script, within PL/SQL or something ?
Re: How to Delete files from EXT Table Dir through SQL PLUS [message #582563 is a reply to message #582562] Fri, 19 April 2013 07:50 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
UTL_FILE package, as Joy Division told you, its FREMOVE procedure (so it is PL/SQL).
Re: How to Delete files from EXT Table Dir through SQL PLUS [message #582567 is a reply to message #582563] Fri, 19 April 2013 08:26 Go to previous message
sulabhagra
Messages: 57
Registered: April 2012
Member
Thanks a lot, will have a look at it.
Previous Topic: what is error in below query
Next Topic: how to show total number of department
Goto Forum:
  


Current Time: Thu Dec 25 22:31:50 CST 2014

Total time taken to generate the page: 0.25496 seconds