Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE - Invalid Directory or path
UTL_FILE - Invalid Directory or path [message #189130] Wed, 23 August 2006 05:24 Go to next message
uradhakrishna
Messages: 7
Registered: August 2006
Location: India
Junior Member
Hi
here i am creating procedure for inserting values into table from text file. Procedure created, But when i am excuting some errors are coming .
I go thru the errors and found that we need to create the directory by useing CReate Directory UTL_DIR command , But i already created dir "c:\temp" , Y i need to create again , please tell me

=====================================================
CREATE OR REPLACE procedure loaddata
is
f_dir varchar2(20) :='c:\temp' ;
fname varchar2(20) :='RK.TXT' ;
v_filehandle utl_file.file_type;
v_newline varchar2(100);
f_name rk2.ename%type;
f_sal rk2.sal%type;
f_deptno rk2.deptno%type;
f_hdate rk2.hiredate%type;
firstcomma number(2);
seccomma number(2);
thirdcoma number(2);
begin
v_filehandle := UTL_FILE.FOPEN(f_dir , fname , 'R' );
loop
begin
UTL_FILE.GET_LINE(v_filehandle,v_newline);
EXCEPTION
when NO_DATA_FOUND then
exit;
end;
firstcomma := instr(v_newline, ',' , 1 , 1 );
seccomma := instr(v_newline,',',1,2);
thirdcoma := instr(v_newline,',',1,3);
f_name := substr( v_newline,1,firstcomma-1);
f_sal := substr( v_newline,firstcomma+1,seccomma-1);
f_deptno := substr( v_newline,seccomma+1,thirdcoma-1);
f_hdate := substr( v_newline,thirdcoma+1);
insert into emp values (f_name,f_sal,f_deptno,f_hdate);
end loop;
utl_file.fclose(v_filehandle);
commit;
EXCEPTION
when UTL_file.invalid_operation then
utl_file.fclose(v_filehandle);
raise_application_error(-20051,'loadstudent invalid operator');
end;
Re: UTL_FILE - Invalid Directory or path [message #189134 is a reply to message #189130] Wed, 23 August 2006 05:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It is refering to the Oracle DIRECTORY construct, which records the location of an directory path on the serverm and allows control over who has access to this phtsical location.

See Documentation here.
Re: UTL_FILE - Invalid Directory or path [message #189135 is a reply to message #189130] Wed, 23 August 2006 05:34 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You need to create directory object in Oracle first, not just the O/S:

CREATE DIRECTORY TEST1 AS 'c:\temp';
Re: UTL_FILE - Invalid Directory or path [message #189141 is a reply to message #189135] Wed, 23 August 2006 06:29 Go to previous messageGo to next message
uradhakrishna
Messages: 7
Registered: August 2006
Location: India
Junior Member
HI,

I CREATED DIRECTORY "test1" FROM SQL PROMPT , and gives permissions also. Now in my procedure i just changed below statement .
v_filehandle := UTL_FILE.FOPEN('test1' , 'RK.TXT' , 'R' );

But still i am getting errors ,
Have i need to do anything more , I read INIT.ORA file is required , IS it true , IF yes where i can check.
Please reply me if ur free.

Thanks
radha
Re: UTL_FILE - Invalid Directory or path [message #189142 is a reply to message #189141] Wed, 23 August 2006 06:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What errors are you getting exactly. (I get so tired of asking that)

Re: UTL_FILE - Invalid Directory or path [message #189144 is a reply to message #189142] Wed, 23 August 2006 06:48 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
put the directory name in UPPERCASE it is an object and unless you created it with double quotes around its name it will be in UPPERCASE. Check out the all_directories table.

Jim
Re: UTL_FILE - Invalid Directory or path [message #189193 is a reply to message #189130] Wed, 23 August 2006 10:22 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I hope that you are aware that utl_file can ONLY write files on the database server. It can not write to a client.
Re: UTL_FILE - Invalid Directory or path [message #189277 is a reply to message #189193] Thu, 24 August 2006 00:10 Go to previous messageGo to next message
uradhakrishna
Messages: 7
Registered: August 2006
Location: India
Junior Member
Hi sorry i forgotten to tel the errors,

If INIA.ora file is required, from where i can get that file. and where should i copied that.
I want to know exactly when this "UTL_file.invalid_operation" exception raised.


EXCEPTION
when UTL_file.invalid_operation then
utl_file.fclose(v_filehandle);
raise_application_error(-20051,'loadstudent invalid operator');

SQL> exec loaddata ;
BEGIN loaddata ; END;
*
ERROR at line 1:
ORA-20051: loadstudent invalid operator
ORA-06512: at "TEST.LOADDATA", line 35
ORA-06512: at line 1

thnaks in advance

[Updated on: Thu, 24 August 2006 03:54]

Report message to a moderator

Re: UTL_FILE - Invalid_OPERATION [message #189511 is a reply to message #189277] Thu, 24 August 2006 23:54 Go to previous messageGo to next message
uradhakrishna
Messages: 7
Registered: August 2006
Location: India
Junior Member
uradhakrishna wrote on Thu, 24 August 2006 00:10

Hi sorry i forgotten to tel the errors,

If INIA.ora file is required, from where i can get that file. and where should i copied that.
I want to know exactly when this "UTL_file.invalid_operation" exception raised.


EXCEPTION
when UTL_file.invalid_operation then
utl_file.fclose(v_filehandle);
raise_application_error(-20051,'loadstudent invalid operator');

SQL> exec loaddata ;
BEGIN loaddata ; END;
*
ERROR at line 1:
ORA-20051: loadstudent invalid operator
ORA-06512: at "TEST.LOADDATA", line 35
ORA-06512: at line 1

thnaks in advance


Re: UTL_FILE - Invalid_OPERATION [message #189651 is a reply to message #189511] Fri, 25 August 2006 07:37 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Is the "C:\temp" directory on the SAME physical sever as the oracle database?
Re: UTL_FILE - Invalid_OPERATION [message #189652 is a reply to message #189511] Fri, 25 August 2006 07:39 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
and did you change the code to pass the directory object name in uppercase.
Previous Topic: Host String
Next Topic: group by versus distinct
Goto Forum:
  


Current Time: Wed Dec 07 07:09:57 CST 2016

Total time taken to generate the page: 0.13787 seconds