Update to my earlier #quicktip on setting #plsql scope and warnings.

I noticed a error in my code for setting PLSCOPE_SETTINGS and PLSQL_WARNINGS. QuicTip Logon.sql What I did was get the instance name out of v$instance to figure out if I was connecting to a production environment or one of the lower environments. The problem with this is, not everyone is going to have permissions to select on sys.v_$instance. The better way to do this is to use sys_context to get the instance name. This way, you won’t have to chase down additional privileges from your DBA.

<code>

select sys_context('userenv','instance_name')
 into sInst
 from dual;

</code>

Here is the corrected code for my logon.sql

<code>

DECLARE
 sInst varchar2(1);
 BEGIN
 -- rlockard: 2018/02/23 commented out getting instance name from v$instance.
 -- used the more apporiate sys_context('userenv','instance_name')

--select upper(SUBSTR(instance_name, 1,1))
 --INTO sInst
 --FROM SYS.V_$INSTANCE;

select sys_context('userenv','instance_name')
 into sInst
 from dual;

-- test to see if this is a production instance
 -- all production instances start with P so ...
 -- if it's not a production instance set up
 -- session properties approiate for dev / test / sandbox.
 IF sInst != 'P' THEN
 execute immediate 'ALTER SESSION SET PLSCOPE_SETTINGS=' || '''IDENTIFIERS:ALL''';
 execute immediate 'ALTER SESSION SET PLSQL_WARNINGS=' || '''ENABLE:ALL''';
 END IF;
 END;
 /

</code>