-
ORACLE 9i REL 2 - UTL_FILE_DIR
I AM TRYING TO CREATE A TEXT FILE AS FOLLOWS
AS SYSTEM..
Create or Replace procedure UTLTEST as
f1 utl_file.file_type;
begin
-- Open a file in write mode
f1 := utl_file.fopen('MYFILE','NEWFILE.TXT','W');
-- Write a line to a file
utl_file.put_line(f1,'1. This is a test of UTL_FILE packages');
utl_file.put_line(f1,'2. Oracle has added a new procedures in the package');
utl_file.put_line(f1,'3. We will see all the procedure one by one');
-- Close a file
utl_file.fclose(f1);
exception
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE ('invalid_path'); RAISE;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE ('invalid_mode'); RAISE;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE ('invalid_filehandle'); RAISE;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE ('invalid_operation'); RAISE;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('read_error'); RAISE;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('write_error'); RAISE;
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE ('internal_error'); RAISE;
end;
DIR CREATED AS
CREATE OR REPLACE DIRECTORY MYFILE AS 'C:\APPS\ORDER1';
GRANT READ ON DIRECTORY MYFILE TO ADMIN1;
WHEN I EXECUTE THIS FILE AS ADMIN1
EXEC SYSTEM.utltest
HERE IS THE ERROR MESSAGE
SQL> EXEC SYSTEM.utltest
invalid_operation
BEGIN SYSTEM.utltest; END;
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at "SYSTEM.UTLTEST", line 33
ORA-06512: at line 1
COULD SOME ONE PLEASE HELP ?
-
Hi,
First check whether UTL_FILE_DIR parameter is set or not.
sql>show parameter utl_file_dir
or
sql>select * from v$parameter where name='utl_file_dir';
If you dont get a value, then it is not set.
If a value for UTL_FILE_DIR is not set, then you cannot use the UTL_FILE package.
This being a static parameter (u cant change using ALTER SYSTEM SET.. command), you need to bring down the instance, edit the init.ora file to include the UTL_FILE_DIR parameter and then restart the instance.
Note that you need to sepcify a valid path for the UTL_FILE_DIR parameter, and that that directory has write permissions set.
specify the directory in the following way in the inid.ora file:
UTL_FILE_DIR="/uo2/oradata2/prod/utl_dir/"
If u wish to specify multiple directories, put multiple entries in the init.ora file, line by line, and continuously:
UTL_FILE_DIR="/uo1/oradata1/prod/utl_dir/"
UTL_FILE_DIR="/uo2/oradata1/prod/utl_dir/"
UTL_FILE_DIR="/uo3/oradata2/prod/utl_dir/"
And Last, but not the least: READ THE DOCUMENTATION !!
Regards,
Suhas
"Shoot for the moon, even if u miss, u'll land among the stars !!"
-
Originally posted by suhasd75
Hi,
First check whether UTL_FILE_DIR parameter is set or not.
sql>show parameter utl_file_dir
or
sql>select * from v$parameter where name='utl_file_dir';
If you dont get a value, then it is not set.
If a value for UTL_FILE_DIR is not set, then you cannot use the UTL_FILE package.
And Last, but not the least: READ THE DOCUMENTATION !!
Regards,
Suhas
"Shoot for the moon, even if u miss, u'll land among the stars !!"
You don't need to set this parm in 92, maybe you should read the documentation dude.
Here's a simple example that you should be able to build on to create your own proc
CREATE DIRECTORY test_dir AS 'c:\temp';
CREATE OR REPLACE PROCEDURE test
AS
v_output_file1 utl_file.file_type;
BEGIN
/* Ensure TEST_DIR below is in all caps */
/* Third argument below is W which overwrites file each time, change to A to append */
v_output_file1 := utl_file.fopen('TEST_DIR', 'my_test.csv', 'W');
FOR cursor_emp IN (SELECT empnom,ename, deptno FROM emp)
LOOP
utl_file.put_line(v_output_file1, cursor_emp.empno || ',' || cursor_emp.ename || ',' || cursor_emp.deptno);
END LOOP;
utl_file.fclose_all;
END;
/
I'm stmontgo and I approve of this message
-
oh really????
Do u think i'll give permision to ALL users to CREATE DIRECTORY???
Dont i know this option ...
It's all the more easier to manage.. otherwise, u'll end up having n number of "directories",,,, dude...Default directories are always easier to manage.... believe me
-
Originally posted by suhasd75
oh really????
Do u think i'll give permision to ALL users to CREATE DIRECTORY???
Dont i know this option ...
It's all the more easier to manage.. otherwise, u'll end up having n number of "directories",,,, dude...Default directories are always easier to manage.... believe me
whatever dude, give'em sysdba for all I care
For tinakh25's testcase make sure 'C:\APPS\ORDER1' is a valid path on the server, not your client.
I ran your testcase ok by changing the path to c:\temp below
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string
SQL> CREATE OR REPLACE DIRECTORY MYFILE AS 'C:\TEMP';
Directory created.
SQL>
SQL> Create or Replace procedure UTLTEST as
2 f1 utl_file.file_type;
3 begin
4 f1:= utl_file.fopen('MYFILE','NEWFILE.TXT','W');
5 utl_file.put_line(f1,'1. This is a test of UTL_FILE packages');
6 utl_file.put_line(f1,'2. Oracle has added a new procedures in the package');
7 utl_file.put_line(f1,'3. We will see all the procedure one by one');
8 utl_file.fclose(f1);
9 end;
10 /
Procedure created.
SQL>
SQL> exec utltest;
PL/SQL procedure successfully completed.
I'm stmontgo and I approve of this message
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|