My Oracle Support Banner

How to ALTER SYSTEM or ALTER SESSION From Stored PROCEDURE (Doc ID 1024795.6)

Last updated on NOVEMBER 12, 2019

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.