Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE - Invalid_Operation
UTL_FILE - Invalid_Operation [message #189514] Fri, 25 August 2006 00:11 Go to next message
uradhakrishna
Messages: 7
Registered: August 2006
Location: India
Junior Member
Hi friends

I AM USEING BELOW EXCEPTION IN MY PROCEDURE , PROCEDURE CREATED.
WHEN I AM EXECUTING FOLLOWING ERRORS ARE COMING .

WHAT COULD BE THE REASON , Y ALWAYS CURSOR GOES TO EXCEPTION,
WHEN EXACTLY UTL_file.invalid_operation WILL FIRE.

pLEASE TEL ME IF UR FREE.


ERROR at line 1:
ORA-20051: loadstudent invalid operator
ORA-06512: at "TEST.LOADDATA", line 37
ORA-06512: at line 1
================================================


EXCEPTION
when UTL_file.invalid_operation then
utl_file.fclose(v_filehandle);
raise_application_error(-20051,'loadstudent invalid operator');
end;
Re: UTL_FILE - Invalid_Operation [message #189539 is a reply to message #189514] Fri, 25 August 2006 01:15 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
from oracle documentation;

AMD-00113: PROCEDURE string, invalid operation 
Cause: UTL_FILE.INVALID_OPERATION: An error occurred when accessing a file.
Action: Check that the file being accessed is not already opened by another program, then retry.
Re: UTL_FILE - Invalid_Operation [message #189546 is a reply to message #189539] Fri, 25 August 2006 01:30 Go to previous messageGo to next message
uradhakrishna
Messages: 7
Registered: August 2006
Location: India
Junior Member
Hi friend

I opened file once and finally closed after endloop. where i need to check whether it is already opened or not.
please reply me , If u don't mind.
Or else suggest if any statement is required.

thnaks

=======================================================
CREATE OR REPLACE procedure loaddata
is
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('TEST1' , 'RK.TXT' , '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_Operation [message #189561 is a reply to message #189546] Fri, 25 August 2006 02:06 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
Try at OS level, move or rename flat file RK.TXT. If you can not do it then file is still locked.
Re: UTL_FILE - Invalid_Operation [message #189568 is a reply to message #189561] Fri, 25 August 2006 02:37 Go to previous messageGo to next message
uradhakrishna
Messages: 7
Registered: August 2006
Location: India
Junior Member
Hi

I renamed RK.TXT to RK1.txt but still it is giving sme problem.

any way thanks
Re: UTL_FILE - Invalid_Operation [message #189883 is a reply to message #189546] Mon, 28 August 2006 05:44 Go to previous message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Just try this code before opening the file ...


IF utl_file.is_open(v_filehandle) THEN
    utl_file.fclose_all;
END IF;


Previous Topic: negative value
Next Topic: update if exists else insert
Goto Forum:
  


Current Time: Fri Dec 09 11:36:04 CST 2016

Total time taken to generate the page: 0.09888 seconds