Home » SQL & PL/SQL » SQL & PL/SQL » Directory Objects
Directory Objects [message #297432] Thu, 31 January 2008 09:25 Go to next message
cough
Messages: 8
Registered: November 2007
Junior Member
Hi,

I'm new here and have searched but i am unable to find any answers. I am wondering if it is possible to delete files out of directory object through oracle, sql?

I have a datapump export job that runs nightly but datapump will not overwrite .dmp files, i only need to keep them for the day so would be happy for them to be overwritten but i now need to find a a way to delete, preferably as part of the datapump job. I appreciate i can do this using find and rm but ideally i'd like to manage it through oracle. Does anyone know if this is possible?

Thanks in advance

Paul
Re: Directory Objects [message #297438 is a reply to message #297432] Thu, 31 January 2008 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I am wondering if it is possible to delete files out of directory object through oracle, sql?
Not as far as I know; except by using Java.
Alternatively open them for write & leave them at 0 length.
Rhetorical question - How do you get the filenames in the directory?

[Updated on: Thu, 31 January 2008 10:16] by Moderator

Report message to a moderator

Re: Directory Objects [message #297441 is a reply to message #297432] Thu, 31 January 2008 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UTL_FILE.FREMOVE

Regards
Michel
Re: Directory Objects [message #297460 is a reply to message #297432] Thu, 31 January 2008 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Michel,
Thanks. Now I learned 2 new things today.
This morning using V11 RMAN I cloned an active database (without any backup being taken) onto the same system & new SID.

Has UTL_FILE.REMOVE "always" existed or in which version was it new?
Re: Directory Objects [message #297477 is a reply to message #297460] Thu, 31 January 2008 22:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
10g.

Regards
Michel
Re: Directory Objects [message #297495 is a reply to message #297432] Fri, 01 February 2008 02:25 Go to previous messageGo to next message
cough
Messages: 8
Registered: November 2007
Junior Member
hi all, thanks for the respones. we don't want to use utl_file as it's unsecure (i believe?) and still present for backwards compatibility. I thought directory objects were the most secure/prefered method to use to manage OS files.

The files get there as they are datapump dump files run from a scheduled OEM job.

i guess i will have to run an oscommand to remove the .dmp files before the next imports will run. that's annoying.

Paul
Re: Directory Objects [message #297500 is a reply to message #297495] Fri, 01 February 2008 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we don't want to use utl_file as it's unsecure

Wrong if you use Oracle directories and don't set utl_file_dir to *

Quote:
and still present for backwards compatibility

Wrong it is enhanced at each version and far from being obsolete

Quote:
I thought directory objects were the most secure/prefered method to use to manage OS files.

Yes and UTL_FILE uses this.

Regards
Michel
Re: Directory Objects [message #298006 is a reply to message #297432] Mon, 04 February 2008 09:31 Go to previous messageGo to next message
cough
Messages: 8
Registered: November 2007
Junior Member
hi, would it be possible to post a few examples of UTL_FILE?
I am trying to rename a file to todays date then delete yesterdays file. Is this possible?

regards,
Paul
Re: Directory Objects [message #298013 is a reply to message #298006] Mon, 04 February 2008 10:04 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Hi
Use the combination of FCOPY and FREMOVE. you can get the basic syntax here
Give it a bash and post back with any problems (and successes Smile )
Re: Directory Objects [message #298691 is a reply to message #297432] Thu, 07 February 2008 03:27 Go to previous messageGo to next message
cough
Messages: 8
Registered: November 2007
Junior Member
Hi,
Ok spent a bit of time trying to get this to work and found an article about manipulating files with UTL_FILE. I have got something which i think will do the job but it won't compile.

Here's the code
CREATE OR REPLACE PROCEDURE remove("1" IN NUMBER) IS
BEGIN
FOR date_offset IN 1 ..
LOOP
date_file := to_char(sysdate -1, 'DDMMYYYY') || '_' || expdat.dmp;
utl_file.fgetattr('Export', date_file, vexists, vfile_length, vblocksize);

IF vexists THEN
utl_file.fremove('Export', date_file);
DBMS_OUTPUT.PUT_LINE(date_file || ' - REMOVED');
END IF;

END LOOP;
END remove;

When trying to compile it through sql developer I get the following error, but i not very good at coding and can't find what i've done wrong.

Error(5,1): PLS-00103: Encountered the symbol "DATE_FILE" when expecting one of the following:
. ( * @ % & - + / at loop mod remainder rem <an exponent (**)> || multiset The symbol "loop" was substituted for "DATE_FILE" to continue.


thanks in advance
Paul
Re: Directory Objects [message #298693 is a reply to message #298691] Thu, 07 February 2008 03:31 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Hi,
Whenever you post code, please enclose that code with code tags:
[code] Your code goes here[/code]

The initial issue that I can see is that you have missed the UPPER BOUND value for your loop:

FOR i IN 1 .. there should be a number here

Re: Directory Objects [message #298694 is a reply to message #297432] Thu, 07 February 2008 03:33 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
what abou it?

FOR date_offset IN 1 ..


regards,
Re: Directory Objects [message #298699 is a reply to message #298691] Thu, 07 February 2008 03:44 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
It also doesn't appear to be doing the 'renaming file' bit from your requirement. (I gave you a bum steer on that one, look at FRENAME instead of FCOPY as I originally said)
Re: Directory Objects [message #298700 is a reply to message #297432] Thu, 07 February 2008 03:48 Go to previous messageGo to next message
cough
Messages: 8
Registered: November 2007
Junior Member
thanks for the quick responses, I've got the frename component that seems to work, heres the code

procedure EXPRENAME IS
Begin
UTL_FILE.FRENAME ('Export','EXPDAT01.DMP','Export', TO_CHAR (SYSDATE,'DDMMYYYY')||
'_'||'EXPDAT.DMP');
END EXPRENAME;


I am still stuck however
Re: Directory Objects [message #298703 is a reply to message #298700] Thu, 07 February 2008 03:53 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I am still stuck however


What with? Did you look at the suggestion from both mshrkshl and I?
Previous Topic: What is the maximum length of a query string in oracle?
Next Topic: More than 4000 characters in PL/QL variable
Goto Forum:
  


Current Time: Sat Dec 10 22:26:30 CST 2016

Total time taken to generate the page: 0.12744 seconds