0

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.

0

2 Answers 2

0

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;
7
  • Yeah, true. I just wanted to set the parameter.I tried already with a simple approach as suggested by @Wernfried Domscheit, it works. As i see from your answer, would you mind to explain what is v$parameter and RTRIM? seems, its like additional knowledge for me..@BriteSponge
    – Lee
    Jan 4, 2017 at 10:33
  • RTRIM is a PL/SQL function to trim whitespace from the right of a string. The v$ views show information that is in place for this instantiation of the DB. So the v$parameter view shows the parameters that are set right now for the database. If using a RAC DB you can use a gv$ view to see settings across the RAC rather than specific to one instance. I suggest you take a look at the Oracle docs for complete information. Jan 4, 2017 at 11:27
  • If the message is "Failed to set parameter..." then this message should be written only if setting the parameter fails. But here you also will get this message even if the select statement fails or the dbms_output statement fails but the setting of the parameter succeeded. That is not good.
    – miracle173
    Jan 4, 2017 at 11:36
  • @miracle173 - the main purpose of the question is to set the parameter and confirm it is set. The exception text could just as easily be 'There was an error' and for this sort of action that is what I would normally have. Jan 4, 2017 at 11:42
  • @BriteSponge however, after run the above example it return error SQL> Failed to set parameter because of -2095 Error message - ORA-02095: specified initialization parameter cannot be modified seems like it not able to be alter successfully..
    – Lee
    Jan 5, 2017 at 2:29
0

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.

2
  • How about the error handling? the exception should remains?
    – Lee
    Jan 4, 2017 at 9:00
  • Try exception when others then DBMS_OUTPUT.PUT_LINE(SQLERRM), but in case of exception you will see the error anyway. Jan 4, 2017 at 9:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.