How do I write to a local file with PL/SQL?

[mrpaulwass]

Is there a way I can write to a local file (such as C:\test.txt) within a PL/SQL procedure?

Can someone post an example if it can be done?

Thanks,

P

[LKBrwn_DBA]

From Oracle metalink Note:123862.1

- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -
DECLARE
fHandle UTL_FILE.FILE_TYPE;
vText varchar2(10);
BEGIN
fHandle := UTL_FILE.FOPEN('c:\','utlfile.txt','w');
vText := 'TEST';
UTL_FILE.PUTF(fHandle,vText);
UTL_FILE.FCLOSE(fHandle);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
WHEN UTL_FILE.INVALID_OPERATION then
RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');
WHEN UTL_FILE.INVALID_FILEHANDLE then
RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');
WHEN UTL_FILE.WRITE_ERROR then
RAISE_APPLICATION_ERROR(-20104,'Write Error');
WHEN UTL_FILE.READ_ERROR then
RAISE_APPLICATION_ERROR(-20105,'Read Error');
WHEN UTL_FILE.INTERNAL_ERROR then
RAISE_APPLICATION_ERROR(-20106,'Internal Error');
WHEN OTHERS THEN
UTL_FILE.FCLOSE(fHandle);
END;
/

- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

[LKBrwn_DBA]

Also you can look into metalink Note:119644.1
which has more examples.

[mrpaulwass]

Thank you all..

Now can someone post a similar example using the text_io package? Or is text_io for Oracle forms only??

I want to write to a local file from PL/SQL? (ie c:\test.txt).

What's the best way to do this?

Thanks again.

Paul

[shibha]

As far as I know text_io is available through Forms only. utl_file will write on the special server folders only and will never write on locla machine. Syntax for text_io is exactly the same as UTL file except that destination folder can be anywhere on client and need not be specified in the system parameters.

[mrpaulwass]

Will utl_file only write out to the server side??

[shibha]

yes, at least for the version I work on. I work on 7.3.2 and I faced this problem. I did see a clever solution where they had to geenrate file on server but it was through forms only. They opened the server fiel for reading using utl_file and wrote it line by line using text_io.