Directory Objects [message #297432] |
Thu, 31 January 2008 09:25  |
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   |
 |
BlackSwan
Messages: 26766 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 #297495 is a reply to message #297432] |
Fri, 01 February 2008 02:25   |
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 #298006 is a reply to message #297432] |
Mon, 04 February 2008 09:31   |
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 #298691 is a reply to message #297432] |
Thu, 07 February 2008 03:27   |
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   |
pablolee
Messages: 2882 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 #298699 is a reply to message #298691] |
Thu, 07 February 2008 03:44   |
pablolee
Messages: 2882 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   |
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
|
|
|
|