problem with UTIL_FILE

[san1014]

Hi

I have created a procedure using UTL_FILE.

Before executing the procedure i created a directory as

 
connect system/system;
grant execute on sys.utl_file to PUBLIC;
alter system set processes=500 scope=spfile;
grant read, write on directory gams to public;
connect san/san;
create or replace directory gams as 'C:\gamsdir';
 
CREATE OR REPLACE PROCEDURE Empl
IS
    f utl_file.file_type;
begin
  f := utl_file.fopen('GAMS', 'test.txt', 'w');
  utl_file.put_line(f, 'line one: some text');
  utl_file.put_line(f, 'line two: more text');
  utl_file.fclose(f);
end;
/
 
--It gives the errors as
 
/*ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "SAN.EMPL", line 5
ORA-06512: at line 1*/
 
 

How can i create the directory?
How can i create the utl_file_dir and how can i set that directory in init.ora?

what can i do?
Any Help???

Thank you

[amitpatel66]

Run this query:

 
SELECT value FROM v$parameter WHERE name = 'utl_file_dir';
 
 

The output you get from the above query will be the directories that you can use for utl file operation.

If you want to add any otehr directories to the above list, then you need to modify init.ora file and restart the server.

[debasisdas]

try the following sample to create directory and grant read ,write previliges on the same to the user.

create or replace directory dir1 as 'c:\folder1' ;

[san1014]

Run this query:

 
SELECT value FROM v$parameter WHERE name = 'utl_file_dir';
 
 

The output you get from the above query will be the directories that you can use for utl file operation.

If you want to add any otehr directories to the above list, then you need to modify init.ora file and restart the server.

SQL> SELECT VALUE FROM v$parameter WHERE name = 'utl_file_dir';

VALUE
--------------------------------------------------------------------------------

But it is not displaying anything?

Please Help me?
What to do?
Thank u.

[amitpatel66]

SQL> SELECT VALUE FROM v$parameter WHERE name = 'utl_file_dir';

VALUE
--------------------------------------------------------------------------------

But it is not displaying anything?

Please Help me?
What to do?
Thank u.

If you see below from my instance:

 
SQL> SELECT VALUE FROM v$parameter WHERE name = 'utl_file_dir'
  2  /
 
VALUE
--------------------------------------------------------------------------------
/usr/tmp, /usr/tmp, /u01/oracle/visdb/9.2.0/appsutil/outbound/VIS_oraappsdev, /u
sr/tmp
 
 
SQL> 
 
 

the above list of directories are used for utl file operation.
to add any new values, you need to modify the init.ora file and bounce the server.....

[san1014]

If you see below from my instance:

 
SQL> SELECT VALUE FROM v$parameter WHERE name = 'utl_file_dir'
  2  /
 
VALUE
--------------------------------------------------------------------------------
/usr/tmp, /usr/tmp, /u01/oracle/visdb/9.2.0/appsutil/outbound/VIS_oraappsdev, /u
sr/tmp
 
 
SQL> 
 
 

the above list of directories are used for utl file operation.
to add any new values, you need to modify the init.ora file and bounce the server.....

How can i modify the init.ora file and bounce the server?

Please tell me in detail.

Thank u

[amitpatel66]

How can i modify the init.ora file and bounce the server?

Please tell me in detail.

Thank u

The init.ora file is placed in the path "$ORACLE_HOME/dbs" of the databas eserver.

Modify this file and shutdown/restart your database server

This can be done by your DBA team.Ask your DBA team to do this.

[san1014]

The init.ora file is placed in the path "$ORACLE_HOME/dbs" of the databas eserver.

Modify this file and shutdown/restart your database server

This can be done by your DBA team.Ask your DBA team to do this.

I really confused with this.

If you dont mind can you explain in step wise , what can i do to use UTL_FILE?

Please help me.

Thank ypu

[amitpatel66]

I really confused with this.

If you dont mind can you explain in step wise , what can i do to use UTL_FILE?

Please help me.

Thank ypu

Check here if it helps!! and here

[amitpatel66]

I really confused with this.

If you dont mind can you explain in step wise , what can i do to use UTL_FILE?

Please help me.

Thank ypu

Check here if it helps!! and
here