utl_file

[draakhs]

Hi guys,

I am using utl_file.fremove to remove some files from the OS side.
To say it simple it just creates 2 temp tables, one with the archivelog listing and one with the datafile listing.

Then I wrote a little sql query that compares the archivelog dates to the datafiles dates en gives a list of the archivelogs that can be deleted.

The problem is with the loop part.
I make a cursor of above query and then make a loop.
The part which gives an error is:

begin
utl_file.fremove('DIRECTORY',...);
end loop;
end;

The DIRECTORY is the directory pointing to the archivelog directory on the OS. The ... is the error. When I use the cursor parameter like 'filename' it searches for the string filename and not for the name coming out of the cursor.
When I do filename without the quotes it says I have a code fault.

So either way he doesn't seem to recognize the file part. Is there any way around it? or am I doing something wrong?
The problem in short is the fact the cursor works but the utl_file part doesn't recognize the filename.

The directory is right, I can do a utl_file fremove without a loop, just single OS file remove with the hard coded filename.
So I have rights etc.

Anyone have any ideas.

[DaPi]

Where is the beginning of the loop? Like: "For x In () Loop"?

I think it should look something like:
For x In (Select filename . . . . ) Loop
utl_file.fremove('DIRECTORY',x.filename);
End Loop;

It would help if you posted code & error msg.

[draakhs]

DaPi,

Thank you very much, I just tried the loop you suggested and it works.
It seems I was trying to do a normal loop with a cursor which doesn't seem to work with it.

I then moved the select in the loop syntax like you showed and it worked.

Greetings,

Draakhs