Oracle SQLPlus – How to Prompt SQL if There is No Exception

oraclesqlplus

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> ALTER SYSTEM SET filesystemio_options=setall scope=spfile;
System altered.

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

SQL> 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.

Best Answer

It looks like you are actually interested in knowing that the parameter has been set. In which case the following will set the value and then query the parameters to output the new current value.

set serveroutput on size 100000

DECLARE
c_value VARCHAR2(4000);

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

  SELECT value 
  INTO   c_value
  FROM   v$parameter
  WHERE  name = 'sga_max_size';

  DBMS_OUTPUT.PUT_LINE('SGA_MAX_SIZE is set to '||RTRIM(c_value));

EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('There was an error - '||SQLCODE);
  DBMS_OUTPUT.PUT_LINE('Error message - '||SUBSTR(SQLERRM,1,128));

END;
Related Question