How to ALTER SYSTEM or ALTER SESSION From Stored PROCEDURE

(Doc ID 1024795.6)

Last updated on AUGUST 30, 2017

Applies to:

PL/SQL - Version 9.2.0.8 and later
Information in this document applies to any platform.

***Checked for relevance on 30-Aug-2017***


Goal

Not all SQL statements that are executed at the SQL prompt can be inserted directly into a Stored Procedure. For example, attempting to execute an ALTER SYSTEM FLUSH SHARED_POOL; command within a Stored Procedure will produce a compilation error.

SQL> CREATE OR REPLACE procedure proc1 IS
2 BEGIN
3 alter system flush shared_pool;
4 END;
5 /

Warning: Procedure created with compilation errors.

SQL> SHOW ERROR
Errors for PROCEDURE PROC1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4 PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms