| Urgent: UTL_FILE PL/SQL Reference Package [message #369951] |
Thu, 16 November 2000 09:34  |
Shanthi Ramayanapu
Messages: 22
Registered: October 2000
|
Junior Member |
|
|
Hi,
Did anyone use UTL_FILE PL/SQL reference packages provided by Oracle. I am trying to use it for the first time and the online manual has only the syntax , but does not have any examples demonstrating how to use it.
I am trying to store the out of my PL/SQL Procedure into files based on the conditions.
Shanthi
|
|
|
|
| Re: Urgent: UTL_FILE PL/SQL Reference Package [message #369952 is a reply to message #369951] |
Fri, 17 November 2000 04:35  |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi Shanti,
I have illustrated the usage of UTL_FILE with an example. This should help you. But before that bear it in mind the path where your files will be searched or created will be mentioned in your init.ora file. Please check the path. You can change the path to your own path by editing the init.ora file.
assuming that you want to write something to a file using a PL/SQL block:
DECLARE
outfile_handle UTL_FILE.FILE_TYPE;
v_test VARCHAR2(1000) ;
BEGIN
-- TO begin with we have to open the file in
-- whatever mode you want by mentioning its path
-- and file name....
-- Here it is A meaning Append mode
outfile_handle := UTL_FILE.FOPEN('FILE PATH',
'file_name','A');
v_test := 'This is a Test ' ;
-- To write a line into the file
UTL_FILE.PUT_LINE(outfile_handle, v_test) ;
-- To close the file
UTL_FILE.FCLOSE (outfile_handle) ;
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION
THEN
DBMS_OUTPUT.PUT_LINE('** File Exception **');
END ;
/
This is a simple example to start with. Hope this helps you.
Good Luck!
Babu
|
|
|
|
|
|
|
|
| Re: Urgent: UTL_FILE PL/SQL Reference Package [message #369956 is a reply to message #369954] |
Fri, 17 November 2000 10:43 |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi,
I agree with NetoMan, check the path in your init.ora file and use the same when you write the path name in your pl/sql script. If you are using Unix check "\" is "/" in unix to separate directories... Bear that in mind and try again. Also your file should have write access privilege if you are trying to open the file in write or append mode, change the file attributes so that it can be written by any other user as well.
Apart from these two I can't see why it is not working.
Good Luck!
Babu
|
|
|
|
| Re: Urgent: UTL_FILE PL/SQL Reference Package [message #369957 is a reply to message #369956] |
Fri, 17 November 2000 12:05 |
Shanthi Ramayanapu
Messages: 22
Registered: October 2000
|
Junior Member |
|
|
Hey all,
Thanks for all your responses. I found the solution and here it is
1. My UTL_FILE_DIR path is specified correctly in init.ora
2. I do have all sorts of permissions on this specified path.
3. I also have permissions to execute UTL_FILE package
only this missing and which is not mentioned in any oracle documentation is
(had to go to metalink and search in problem solution to find the answer)
In order to use UTL_FILE reference package functions,
"ORACLE" should have access to write to the directory specified in init.ora UTL_FILE_DIR, not the oracle_user who is executing the command.
These files are created/modified/read by "ORACLE" not as the oracle_user who is executing the procedure.
Very bad design. Now I fixed the problem by giving "ORACLE" all permission on UTL_FILE_DIR, but since these files are created by "ORACLE" I can only read them. To either execute or modify I need to again request DBA to change the file permissions on these files.
I hope oracle will fix this design soon.
Shanthi
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
