Oracle-L: RE: DBMS_SHARED

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Thu, 18 Apr 2002 05:18:19 -0800
Message-ID: <F001.00447B3D.20020418051819@fatcity.com>

Bunyamin,

Either try removing the 'EXEC' or try putting a begin and end around the call?

EXECUTE IMMEDIATE
'SYS.DBMS_SHARED_POOL.KEEP('||tab_row.owner||'.'||tab_row.object_name||')';

EXECUTE IMMEDIATE 'BEGIN
SYS.DBMS_SHARED_POOL.KEEP('||tab_row.owner||'.'||tab_row.object_name||')' || '; END'; This is a great idea, by the way! Let us know how it works!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Thursday, April 18, 2002 4:28 AM
To: Multiple recipients of list ORACLE-L

I want to pin most run packages , I have loeded them into a table. For this I have written ,

create or replace procedure pin_packages_defined as sql_sentence varchar2(200);
cursor_name INTEGER;
rows_processed INTEGER;
CURSOR tab_cur IS SELECT owner,object_name FROM arsiv.pin_aday_objeler; tab_row tab_cur%ROWTYPE;
BEGIN
FOR tab_row IN tab_cur LOOP
--EXECUTE IMMEDIATE 'EXEC

SYS.DBMS_SHARED_POOL.KEEP('||tab_row.owner||'.'||tab_row.object_name||')'; cursor_name := dbms_sql.open_cursor;
sql_sentence
:='SYS.DBMS_SHARED_POOL.KEEP('''||tab_row.owner||'.'||tab_row.object_name||' '')';

dbms_output.put_line(sql_sentence);
dbms_sql.parse(cursor_name,sql_sentence, dbms_sql.native);
rows_processed := dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);

END LOOP;
END; But , It does not execute , Is it impossible to execute DBMS_SHARED_POOL.KEEP dynamically ...I tried DBMS_JOB , It did not work too.

How can I do this?

Bunyamin K. Karadeniz
Oracle DBA / Developer
Civilian IT Department
Havelsan A.S. Eskisehir yolu
7.km Ankara Turkey
Phone: +90 312 2873565 / 1217
Mobile : +90 535 3357729

The degree of normality in a database
is inversely proportional to that of its DBA.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

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). Received on Thu Apr 18 2002 - 08:18:19 CDT

RE: DBMS_SHARED_POOL.KEEP