Home » SQL & PL/SQL » SQL & PL/SQL » Discarding state of UTL_FILE
Discarding state of UTL_FILE [message #177131] Tue, 13 June 2006 03:24 Go to next message
ptgeorge
Messages: 19
Registered: April 2005
Junior Member
Have any of you seen this error before ?

  1  create or replace procedure test as
  2  test_file utl_file.file_type;
  3  begin
  4  test_file:=utl_file.fopen('/tmp/', 'test.txt', 'w') ;
  5  utl_file.fclose(test_file);
  6* end ;
SQL> /

Procedure created.

SQL> exec test
BEGIN test; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "USER1.UTL_FILE" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "USER1.TEST", line 4
ORA-06512: at line 1


SQL> exec test
BEGIN test; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "USER1.UTL_FILE" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "USER1.TEST", line 4
ORA-06512: at line 1


From what I understand, ORA-4608 occurs when a different session recompiles the package, which causes the package state variables to change.

I had initially got this error for package SYS.UTL_FILE. I tried again after recreating the UTL_FILE package for USER1. But I'm still getting the same problem. Does anyone know how to rectify this ?

[Updated on: Tue, 13 June 2006 09:29]

Report message to a moderator

Re: Discarding state of UTL_FILE [message #190216 is a reply to message #177131] Tue, 29 August 2006 14:28 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi,

It Looks like some problem in Your Database or OS


Your code is running fine in my database

create or replace procedure test as
test_file utl_file.file_type;
begin
test_file:=utl_file.fopen('d:\utl_dir', 'emp2.txt', 'w') ;
utl_file.fclose(test_file);
end ;

I have win2k server and Database is 9.2.0


RP Singh
Re: Discarding state of UTL_FILE [message #190233 is a reply to message #177131] Tue, 29 August 2006 17:18 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I would suggest dropping the package from USER1 and either recompiling the SYS code using UTLRP.SQL or, if necessary, reinstalling the UTL_FILE package under SYS using the UTLFILE.SQL script.
Re: Discarding state of UTL_FILE [message #190277 is a reply to message #190233] Wed, 30 August 2006 00:25 Go to previous messageGo to next message
ptgeorge
Messages: 19
Registered: April 2005
Junior Member
Thanks a lot for your time, but I found the problem. I was working on Oracle 9.2.0.6.0 on AIX. Apparently there is a bug where the UTL_FILE package body is not created, just the definition. Anyway, after I installed the patch its working fine.

Incidentally, utlfile.sql only recreates the package definition. You have to run prvtfile.plb to recreate package body - its wrapped. This file was the problem with the 9.2.0.6.0 upgrade
Re: Discarding state of UTL_FILE [message #190425 is a reply to message #190277] Wed, 30 August 2006 11:47 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
We went through that issue on that patch but since you provided no version or platform information, I did not mention that possibility.
Re: Discarding state of UTL_FILE [message #190498 is a reply to message #190425] Thu, 31 August 2006 01:51 Go to previous message
ptgeorge
Messages: 19
Registered: April 2005
Junior Member
Yes, my mistake. At the time I posted this I figured it must be some compilation mistake or something in the packages. 'cause on another server (same OS & Oracle version) it was working fine. Only realized it was a bug after dredging Google Smile
Previous Topic: What are the new future in oracle 10g compare to 9 oracle database
Next Topic: Creating a view contains...
Goto Forum:
  


Current Time: Fri Dec 09 02:24:26 CST 2016

Total time taken to generate the page: 0.09999 seconds