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 laterInformation 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 |