Re: Error pinning PKS in shared pool

... oops
and then you might want to add that you really have to mess around with quote marks and begin/ends to get it to work - something like (and I really ought to test this before posting, 'cos it's one of those tiny details that there's no point in wasting valuable memorisation time on)

begin

    execute immediate -- no shortened form allowed

        'begin sys.dbms_shared_pool.keep(''DBMS_ALERT'') ; end;'     ;
end;

Note - double up the quotes around the quoted package name, add in the 'begin end' to make the thing you want to execute an anonymous pl/sql block, make sure that there is a semi-colon (which would be incorrect for a pure SQL example) at the end of the thing you are executing.

(I totally agree with your comments though - sys packages have been known to become
mysteriously invalid from time to time).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 24 February 2003 05:23

> ...oops, and I forgot to add that you might wanna wrap
> the call to 'dbms_shared_pool' in pin_me within exec
> immed, so that if the package ever goes invalid (or
> does not exist - ie forgotten to be run) the trigger
> will still run ok.
>
> cheers
> connor
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).