GENERATING AN ENVIROMENT FILE FROM PL/SQL

 
REQUEST

 
RELATED

 
SOLUTION

-------------------------------------------------------
-- Victor Khalturin
-- $Header 14:22 30.01.2018
--
-- Script generates the current environment file.
-- You must have execute privilege on SYS.DBMS_SYSTEM.
-------------------------------------------------------
SET LINES 300
SET PAGES 999
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
   PLATFORM    VARCHAR2(100);
   DELIMITER   VARCHAR2(1);
   ORACLE_SID  VARCHAR2(10);
   ORACLE_HOME VARCHAR2(200);
   DBS_HOME    VARCHAR2(200);
   TNS_HOME    VARCHAR2(200);
   PATCH_HOME  VARCHAR2(200);
   NLS_LANG    VARCHAR2(100);   
   PATH        VARCHAR2(100);
   PS1         VARCHAR2(200);
   LC_ALL      VARCHAR2(200);
   DISPLAY     VARCHAR2(10);
BEGIN

   BEGIN
      SELECT PLATFORM_NAME INTO PLATFORM FROM V$DATABASE WHERE LOWER(PLATFORM_NAME) LIKE '%windows%';
      DELIMITER := '\';
	  PATH := '%ORACLE_HOME%\bin;%ORACLE_HOME%\OPatch;%PATH%';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN 
	  DELIMITER := '/';
	  PATH := '$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH';
   END;
   
   --------------------------------
   -- GET ENVIRONMENT VALUES
   --------------------------------
   
   SELECT INSTANCE_NAME INTO ORACLE_SID FROM V$INSTANCE;
   DBMS_SYSTEM.GET_ENV('ORACLE_HOME',ORACLE_HOME);
   DBMS_SYSTEM.GET_ENV('PATCH_HOME',PATCH_HOME);
   DBMS_SYSTEM.GET_ENV('NLS_LANG',NLS_LANG);
   --DBMS_SYSTEM.GET_ENV('PS1',PS1);
   DBMS_SYSTEM.GET_ENV('LC_ALL',LC_ALL);
   DBMS_SYSTEM.GET_ENV('DISPLAY',DISPLAY);

   --------------------------------
   -- SET DEFAULT 
   --------------------------------
   
   IF NLS_LANG IS NULL THEN NLS_LANG := 'AMERICAN_AMERICA.AL32UTF8'; END IF;
   IF LC_ALL IS NULL THEN LC_ALL := 'en_US.UTF-8'; END IF;
   IF PS1 IS NULL THEN
      PS1 := '"\[\e[35;1m\]\u@\h\[\e[m\]|\A|\[\e[0;33m\]\w:\[\e[m\]\[\e[32;1m\]<${ORACLE_SID}> \[\e[m\]"'; 
   END IF;
   
   --------------------------------
   -- PRINT ENVIRONMENT FILE
   --------------------------------

   DBMS_OUTPUT.NEW_LINE;
   DBMS_OUTPUT.PUT_LINE('#################################');   
   DBMS_OUTPUT.PUT_LINE('## ' || LOWER(ORACLE_SID) || '.env');
   DBMS_OUTPUT.PUT_LINE('#################################');  
   
   DBMS_OUTPUT.NEW_LINE;   
   DBMS_OUTPUT.PUT_LINE('unset ORACLE_SID');
   DBMS_OUTPUT.PUT_LINE('unset ORACLE_HOME');
   DBMS_OUTPUT.PUT_LINE('unset PATH');
   
   DBMS_OUTPUT.NEW_LINE;
   DBMS_OUTPUT.PUT_LINE('export ORACLE_SID=' || ORACLE_SID);
   DBMS_OUTPUT.PUT_LINE('export ORACLE_HOME=' || ORACLE_HOME);
   DBMS_OUTPUT.PUT_LINE('export DBS_HOME=$ORACLE_HOME' || DELIMITER || 'dbs');
   DBMS_OUTPUT.PUT_LINE('export TNS_HOME=$ORACLE_HOME' || DELIMITER || 'network' || DELIMITER || 'admin');
   DBMS_OUTPUT.PUT_LINE('export PATCH_HOME=' || PATCH_HOME);
   DBMS_OUTPUT.PUT_LINE('export NLS_LANG=' || NLS_LANG);
   DBMS_OUTPUT.PUT_LINE('export PATH=' || PATH);
   DBMS_OUTPUT.PUT_LINE('export PS1=' || PS1);
   DBMS_OUTPUT.PUT_LINE('export LC_ALL=' || LC_ALL);
   DBMS_OUTPUT.PUT_LINE('export DISPLAY=' || DISPLAY);
   
   DBMS_OUTPUT.NEW_LINE;
   DBMS_OUTPUT.PUT_LINE('echo');
   DBMS_OUTPUT.PUT_LINE('echo "   ===================================="');
   DBMS_OUTPUT.PUT_LINE('echo "   ORACLE_HOME : ${ORACLE_HOME}"');
   DBMS_OUTPUT.PUT_LINE('echo "   ORACLE_SID  : ${ORACLE_SID}"');
   DBMS_OUTPUT.PUT_LINE('echo "   DBS_HOME    : ${DBS_HOME}"');
   DBMS_OUTPUT.PUT_LINE('echo "   TNS_HOME    : ${TNS_HOME}"');
   DBMS_OUTPUT.PUT_LINE('echo "   PATCH_HOME  : ${PATCH_HOME}"');
   DBMS_OUTPUT.PUT_LINE('echo "   NLS_LANG    : ${NLS_LANG}"');
   DBMS_OUTPUT.PUT_LINE('echo "   ===================================="');
   DBMS_OUTPUT.PUT_LINE('echo');
END;
/

#################################
## dbpilot.env
#################################

unset ORACLE_SID
unset ORACLE_HOME
unset PATH

export ORACLE_SID=DBPILOT
export ORACLE_HOME=/oracle/product/12.2.0.1
export DBS_HOME=$ORACLE_HOME/dbs
export TNS_HOME=$ORACLE_HOME/network/admin
export PATCH_HOME=/oracle/patches
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export PS1="\[\e[35;1m\]\u@\h\[\e[m\]|\A|\[\e[0;33m\]\w:\[\e[m\]\[\e[32;1m\]<${ORACLE_SID}> \[\e[m\]"
export LC_ALL=en_US.UTF-8
export DISPLAY=:1

echo
echo "   ===================================="
echo "   ORACLE_HOME : ${ORACLE_HOME}"
echo "   ORACLE_SID  : ${ORACLE_SID}"
echo "   DBS_HOME    : ${DBS_HOME}"
echo "   TNS_HOME    : ${TNS_HOME}"
echo "   PATCH_HOME  : ${PATCH_HOME}"
echo "   NLS_LANG    : ${NLS_LANG}"
echo "   ===================================="
echo

NOTE: The DBMS_SYSTEM.GET_ENV does not return PATH variable.

[oracle@dbpilot.net ~]$ cd .env/
[oracle@dbpilot.net .env]$ source dbpilot.env

   ====================================
   ORACLE_HOME : /oracle/product/12.2.0.1
   ORACLE_SID  : DBPILOT
   DBS_HOME    : /oracle/product/12.2.0.1/dbs
   TNS_HOME    : /oracle/product/12.2.0.1/network/admin
   PATCH_HOME  : /oracle/patches
   NLS_LANG    : AMERICAN_AMERICA.AL32UTF8
   ====================================

oracle@dbpilot.net|14:16|~/.env:<DBPILOT>