Re: Forms 3.0 and packaged procedures

In article: <b117cb$d3838.285_at_pdinsure.co.uk> dave_at_pdinsure.co.uk (David Broomfield) writes:
>
> We are currently running Oracle 7.1.6.2.0 and Forms 3.0.16 on a Sequent.
>
> We have developed a number of database packages that are called from
> various forms, and we have an intermittent problem where forms fail
> to find the packages when they are being generated. The packages can
> be both seen and executed from SQL*Plus, and executed (but not compiled)
> from SQL*Forms.
>
> The problem seems to sort itself out after a few minutes and all is well
> again. Occasionally, an "ALTER SYSTEM FLUSH SHARED_POOL" command seems
> to get things moving.
>

I don't understand what you mean by "packages can be...executed (but not compiled) from SQL*Forms". Surely if you can execute them from SQL*Forms you don't have a problem? And why would you want to compile them from a form?

I think I *might* know your problem.

Do you find that the packages are fairly large and they'll run for a while after flushing the shared pool but after a period of time you'll get a message something like "...BAM Buffer Error...".

What has happened is that the package has been 'aged out' of the Pool on an LRU basis. When you next try to reference the object the shared pool is too fragmented to reload the package.

Flushing the pool will solve the problem temporarily *BUT* you don't want to flush the shared pool while people are using it as this will give you performance problems while everyone has to fetch code from disk. It completely negates the point of sharing code in the first place.

What you need to do is PIN the packages in the shared pool. This is best done at database startup before the pool gets populated with any rubbish.

In your database startup script, after the database is opened you will need to reference the package to pull it into memory (e.g. issue a COMPILE command).

Next, issue the command

  DBMS_SHARED_POOL.KEEP('package_name');

This will ensure that the object is not aged out from the shared pool as long as the database is open.

HTH, Regards,

Pete

-- 

------------------------------------------------------------------------
|  Peter Moore - Database Administrator - MAT Transport Ltd, London, UK
|  pt_at_chaff.demon.co.uk : +44 (171) 410 6373
|  "With a little study you'll go a long ways & I wish you'd start now!"