469,271 Members | 1,727 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

problem with UTIL_FILE

37
Hi

I have created a procedure using UTL_FILE.

Before executing the procedure i created a directory as

Expand|Select|Wrap|Line Numbers
  1.  
  2. connect system/system;
  3. grant execute on sys.utl_file to PUBLIC;
  4. alter system set processes=500 scope=spfile;
  5. grant read, write on directory gams to public;
  6. connect san/san;
  7. create or replace directory gams as 'C:\gamsdir';
  8.  
  9. CREATE OR REPLACE PROCEDURE Empl
  10. IS
  11.     f utl_file.file_type;
  12. begin
  13.   f := utl_file.fopen('GAMS', 'test.txt', 'w');
  14.   utl_file.put_line(f, 'line one: some text');
  15.   utl_file.put_line(f, 'line two: more text');
  16.   utl_file.fclose(f);
  17. end;
  18. /
  19.  
  20. --It gives the errors as
  21.  
  22. /*ERROR at line 1:
  23. ORA-29283: invalid file operation
  24. ORA-06512: at "SYS.UTL_FILE", line 475
  25. ORA-29283: invalid file operation
  26. ORA-06512: at "SAN.EMPL", line 5
  27. ORA-06512: at line 1*/
  28.  
  29.  
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
Jan 4 '08 #1
9 8300
amitpatel66
2,367 Expert 2GB
Run this query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT value FROM v$parameter WHERE name = 'utl_file_dir';
  3.  
  4.  
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.
Jan 4 '08 #2
debasisdas
8,127 Expert 4TB
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' ;
Jan 4 '08 #3
san1014
37
Run this query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT value FROM v$parameter WHERE name = 'utl_file_dir';
  3.  
  4.  
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.
Jan 4 '08 #4
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SELECT VALUE FROM v$parameter WHERE name = 'utl_file_dir'
  3.   2  /
  4.  
  5. VALUE
  6. --------------------------------------------------------------------------------
  7. /usr/tmp, /usr/tmp, /u01/oracle/visdb/9.2.0/appsutil/outbound/VIS_oraappsdev, /u
  8. sr/tmp
  9.  
  10.  
  11. SQL> 
  12.  
  13.  
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.....
Jan 4 '08 #5
san1014
37
If you see below from my instance:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SQL> SELECT VALUE FROM v$parameter WHERE name = 'utl_file_dir'
  3.   2  /
  4.  
  5. VALUE
  6. --------------------------------------------------------------------------------
  7. /usr/tmp, /usr/tmp, /u01/oracle/visdb/9.2.0/appsutil/outbound/VIS_oraappsdev, /u
  8. sr/tmp
  9.  
  10.  
  11. SQL> 
  12.  
  13.  
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
Jan 4 '08 #6
amitpatel66
2,367 Expert 2GB
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.
Jan 4 '08 #7
san1014
37
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
Jan 4 '08 #8
amitpatel66
2,367 Expert 2GB
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
Jan 4 '08 #9
amitpatel66
2,367 Expert 2GB
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
Jan 4 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Bruce Davis | last post: by
11 posts views Thread by Kostatus | last post: by
9 posts views Thread by Sudesh Sawant | last post: by
117 posts views Thread by Peter Olcott | last post: by
17 posts views Thread by Jon Slaughter | last post: by
28 posts views Thread by Jon Davis | last post: by
6 posts views Thread by Ammar | last post: by
2 posts views Thread by Mike Collins | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.