In a production system why would you have stored procedures that needed to
be recompiled?
I can see in a development system the need to recompile procedures, but the
developer should be making sure their procedure compiles.
When you migrate the code to production you can list those that are invalid
and compile them. If they won't compile then you should fix them
(presumably you would have fixed them in the development or test
environments before you migrated them to production) prior to making them
live in the production system.
Jim
"Anil Asher" <anilasher_at_hotmail.com> wrote in message
news:d24b10ea.0112090658.5015c4da_at_posting.google.com...
> OK ! The reason why I need to do this is for our application server
> which uses cached connections.
>
> The connections to the database are created at startup and remain
> connected until a re-start. The server hands over the connections on
> demand to application objects which return the connection on
> completion of their task.
> Thus the connection is re-used without the overhead of connection
> time.
>
> Problem is when we use procedures. When a procedure is re-compiled the
> first call to it fails with an invalid procedure error from oracle,
> the second call usually works since the procedure is re-validated for
> the session after the first error.
>
> I would either have to re-code my application to always make the
> second call on an error OR I can incoporate a check to see if any
> procedures have been re-compiled since the connection to the db was
> established, if yes then disconnect and re-connect.
>
> The SQL was intended for this check.
>
> We did consider using the SIGNATURE mode since the TIMESTAMP mode is
> causing the error but I am at a loss as to how to determine what mode
> a session is in and the ALTER SESSION command doesn't seem to have an
> effect or doesn't work since I still get the same error on calling
> re-compiled procedure.
>
> Thanks,
> Anil
>
> Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
news:<9utgaf015ag_at_drn.newsguy.com>...
> > In article <d24b10ea.0112071529.50cc84a0_at_posting.google.com>,
> > anilasher_at_hotmail.com says...
> > >
> > >Please help me resolve an on going debate about how much load the
> > >following SQL will cause if it were to called prior to every SQL call
> > >
> > >select * from dba_objects where OWNER = 'SOMEONE'
> > >AND OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')
> > >AND STATUS = 'VALID'
> > >AND to_date(TIMESTAMP,'YYYY-MM-DD:HH24:MI:SS') >
> > >(select logon_time FROM v$sessio where audsid = userenv('SESSIONID'))
> > >
> > >The purpose of the SQL is to determine if any Functions, Packages or
> > >Procedures have been re-compiled after this connection was
> > >established.
> > >
> >
> > why? the server will detect that automatically and tell you about it
with an
> > error if you need to know (if you don't need to know, it won't tell
you).
> >
> > why the heck would you want to do this?
> >
> > Yes, it'll definitely slow things down.
> >
> >
> >
> > >Also, if anyone knows of an environment variable (or equally efficient
> > >object) which could provide us with the last compile time, please let
> > >me know.
> > >
> > >Thanks in advacne for your help.
> > >Anil
Received on Sun Dec 09 2001 - 10:29:35 CST
