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
Printable View
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
:D
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 - - - - - - - - - - - - - - - -
;)
Also you can look into metalink Note:119644.1
which has more examples.
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
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.
Will utl_file only write out to the server side??
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.