Home » SQL & PL/SQL » SQL & PL/SQL » Query : compilation of invalid objects during execution
Query : compilation of invalid objects during execution [message #254848] Sun, 29 July 2007 09:46 Go to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi,

I am using Oracle 9i R2

While an invalid function is called (executed) for the first time, it gets compiled.

Is there any situation where user will notice an error because of such invalid fuction call?I mean if the function is left invalid during call time (for known reason such as alter table add column) what are the consequences?

create table ta(a number);

create function getit return number as
b number;
begin
select max(a) into b from ta;
return b;
end;

select status from user_objects where object_name='GETIT';
VALID

insert into ta values(1);
commit;

select a from ta;
1

alter table ta add c number;

select status from user_objects where object_name='GETIT';
INVALID

select a from ta;
1

select status from user_objects where object_name='GETIT';
VALID

Thanks and Regards,
OraSaket
Re: Query : compilation of invalid objects during execution [message #254850 is a reply to message #254848] Sun, 29 July 2007 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the problem?
Is the function can be recompiled then it's fine otherwise Oracle will return an error.

Regards
Michel
Re: Query : compilation of invalid objects during execution [message #255027 is a reply to message #254850] Mon, 30 July 2007 07:53 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only common situation in which the user will notice a problem is if you invalidate a package which was maintaining some session parameters. When the user makes another call to that package after it is invalidated, then they will get an error telling them that the session state is lost - I can't remember the error off hand, and lack the enthusiasm to write an example to find out.
Previous Topic: Create column format of a single row to use as saperate cursor elements
Next Topic: out param storing the previous value of in param
Goto Forum:
  


Current Time: Fri Dec 02 21:07:12 CST 2016

Total time taken to generate the page: 0.24564 seconds