How to prompt sql if there is no exception I'm new to SQL advance methods. I'm trying to do some error handling after checking for exceptions.

DECLARE
value_invalid exception;
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM SET sga_max_size=12g';
EXCEPTION
when value_invalid then
null;
END


I'm trying to have a PROMPT for this SQL because I'm calling this script from a shell script. It returns out no prompt at all which is different from when I run the SQL command in sqlplus manually. See, for example,

SQL&gt; ALTER SYSTEM SET filesystemio_options=setall scope=spfile;
System altered.


In my case, once I ran the shell file it gave me this:

SQL&gt; Database Tuning for Static_Parameters
Database Tuning for sga_max_size
13   14  Disconnected from Oracle Database 12c Enterprise Edition Release
12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing
options


My questions is, was it altered successfully? How can I add a prompt to notify user that the system is altered? I tried ELSE to prompt the user if there's no exception, but still nothing appeared at the prompt.
Using 

EXCEPTION
when value_invalid then
null;


is definitively a bad idea, you should not silently suppress the error message.

Try this:

DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM SET sga_max_size=12g';
END;
/


Note, inside a PL/SQL Block you have only direct privileges. Privileges granted by role (e.g. DBA role) do not apply inside the block.