Home » SQL & PL/SQL » SQL & PL/SQL » problem in opening a file (utl_file) (windows-xp)
problem in opening a file (utl_file) [message #386749] Mon, 16 February 2009 23:03 Go to next message
Usha Shastri
Messages: 2
Registered: February 2009
Location: Bangalore
Junior Member
hi..
i have some problem in opening a file. i hav done with all these steps:
i have replaced initorcl.ora file with init.ora file.
conn sys as sysdba;
create spfile from pfile;
shutdown
in initorcl.ora file i wrote UTL_FILE_DIR='C:\temp\'
startup
create or replace directory my_dir as 'C:\temp\';
grant read,write on directory my_dir to scott;

then i logged in as scott

declare
v_handler utl_file.file_type;
begin
v_handler:=utl_file.fopen(my_dir,'a.txt','W');
utl_file.put_line(v_handler,'good morning');
utl_file.fclose(v_handler);
end;
/

it is giving an error as
erroe at line 3
ORA-06550: line 4 column 29
PLS-00201:indentifier 'my_dir' must be declared
Ora-06550:line 4 column 3
pl/sql:statement ignored

if i put same code with a slight change i.e
v_handler:=utl_file.fopen('my_dir','a.txt','W');
it will give an error saying that invalid directory path.

so please give me the solution.
Re: problem in opening a file (utl_file) [message #386750 is a reply to message #386749] Mon, 16 February 2009 23:04 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: problem in opening a file (utl_file) [message #386780 is a reply to message #386749] Tue, 17 February 2009 00:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The first parameter in UTL_FILE.FOPEN is a string.
Feed it the name of your directory (in upper-case!) as a string:
SQL> conn scott/tiger
Connected.
SQL> create or replace directory my_dir as 'C:\temp\';

Directory created.

SQL> declare
  2  v_handler utl_file.file_type;
  3  begin
  4  v_handler:=utl_file.fopen(my_dir,'a.txt','W');
  5  utl_file.put_line(v_handler,'good morning');
  6  utl_file.fclose(v_handler);
  7  end;
  8  /
v_handler:=utl_file.fopen(my_dir,'a.txt','W');
                          *
ERROR at line 4:
ORA-06550: line 4, column 27:
PLS-00201: identifier 'MY_DIR' must be declared
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored


SQL> declare
  2  v_handler utl_file.file_type;
  3  begin
  4  v_handler:=utl_file.fopen('MY_DIR','a.txt','W');
  5  utl_file.put_line(v_handler,'good morning');
  6  utl_file.fclose(v_handler);
  7  end;
  8  /

PL/SQL procedure successfully completed.


@BlackSwan:
What DDL/DML did you expect to clarify the problem?
The expected result was of course "no error instead of an error"
Re: problem in opening a file (utl_file) [message #386788 is a reply to message #386750] Tue, 17 February 2009 00:36 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Even if DDL and DML are given in this post,he could not have given any solution except posting guide lines.

BlackSwan wrote on Tue, 17 February 2009 10:34
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.


Cheers !!!
Trivendra
Re: problem in opening a file (utl_file) [message #386790 is a reply to message #386780] Tue, 17 February 2009 00:40 Go to previous message
Usha Shastri
Messages: 2
Registered: February 2009
Location: Bangalore
Junior Member
thanks. i got it.
Previous Topic: first_value analytic function
Next Topic: inserting rows
Goto Forum:
  


Current Time: Wed Dec 07 06:37:12 CST 2016

Total time taken to generate the page: 0.10707 seconds