#quicktip #oracle #sqlcl logon.sql #PLSCOPE_SETTINGS and #PLSQL_WARNINGS

Quick Tip. My logon.sql file.

I don’t want plscope_settings and plsql_warnings set when I’m in production; but if I’m in my test / dev / sandbox environments, then I do turn them on.

This helps. Enjoy; this should be self explanatory.

set linesize 90
set pagesize 1000
col table_name format a35
col owner format a20
set timing on
--
DECLARE
 sInst varchar2(1);
BEGIN
/*
 select upper(SUBSTR(instance_name, 1,1))
 INTO sInst
 FROM SYS.V_$INSTANCE;
*/
-- changed to use sys_context 2018/02/23
 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;
/
--
define _editor=vi
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

-- setup aliases for sqlcl
alias tab=select table_name from user_tables;
This entry was posted in Database Stuff by rlockard. Bookmark the permalink.

About rlockard

Robert Lockard is a professional Oracle Designer, Developer and DBA working in the world of financial intelligence. In 1987 his boss called him into his office and told him that he is now their Oracle Wizard then handed him a stack of Oracle tapes and told him to load it on the VAX. Sense then, Robert has worked exclusively as an Oracle database designer, developer and Database Administrator. Robert enjoys flying vintage aircraft, racing sailboats, photography, and technical diving. Robert owns and fly’s the “Spirit of Baltimore Hon” a restored 1948 Ryan Navion and lives in Glen Burnie Maryland on Marley Creek

Welcome to oraclewizard