Re: DBMS_SHARED_POOL.KEEP

[Bunyamin K. Karadeniz]

Tried and does not work ..     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. ----- Original Message ----- From: Mercadante, Thomas F To: Multiple recipients of list ORACLE-L Sent: Thursday, April 18, 2002 4:18 PM Subject: RE: DBMS_SHARED_POOL.KEEP 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||')'; or 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----- From: Bunyamin K. Karadeniz [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 4:28 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_SHARED_POOL.KEEP 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.