Carl's Oracle

Hi, unfortunately in the current project the customer has on development site databases on Windows Server but on production site databases on AIX based UNIX servers. Usually this is no problem when the Oracle versions are the same. Some Oracle Database objects are still OS dependent like the directory objects. Oracle Directory objects are an encapsulation of the real directories in the file system of the OS. Grants on Oracle directory objects controls the user access.

If your development database has a directory which contains a windows based path you cannot create the same directory on a UNIX machine.

I did not want to create/organize two scripts for just one database directory object. I would like to have one script which creates automatically the correct directory object dependent on the OS.

And that’s the place where PL/SQL comes in. PL/SQL is not only very useful implementing application logic but also helps the dev team for flexible deployment of database objects.

Following script checks the V$PROCESS view on Programs named ‘ORACLE.EXE%’; If found then you have a windows based machine if not – on our case an AIX machine.

/** DATABASE.INTERFACES.UFA.Directories : User FA Tool (UFA)
   OBJECT_TYPE  : DIRECTORY
   OBJECT_NAME  : OASIS_UFA_IFX_OUT
   CREATOR      : Karl r. CSC
   CREATED      : 18.10.2005
   RUN AS       : User with DBA Privileges
   VERSION      : 0.94
   CMNT         : Target directory for UFA Export
*/
DECLARE
  l_Ora_Exe_Cnt PLS_INTEGER;
  l_Directory   VARCHAR2(32)  := 'OASIS_UFA_IFX_OUT';
  l_Ux_Path     VARCHAR2(256) := '/usr/xfit/OASIS/export/UFA';
  l_Win_Path    VARCHAR2(256) := 'D:\OASIS\Ifx\UFA';

BEGIN

  SELECT COUNT(Vp.Program)
    INTO l_Ora_Exe_Cnt
    FROM V$process Vp
   WHERE Vp.Program LIKE 'ORACLE.EXE%';

  IF (l_Ora_Exe_Cnt > 0) THEN
    -- A windows based database ...
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
                      ' AS ''' || l_Win_Path || '''';
  ELSE
    -- A Non-Windows based database
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory ||
                      ' AS ''' || l_Ux_Path || '''';
  END IF;

  -- Grants on directory
  EXECUTE IMMEDIATE 'GRANT READ  ON DIRECTORY ' || l_Directory ||
                    ' TO OASIS, ROLE_OASISAE, ROLE_OASISIFX';
  EXECUTE IMMEDIATE 'GRANT WRITE ON DIRECTORY ' || l_Directory ||
                    ' TO OASIS, ROLE_OASISAE, ROLE_OASISIFX';

END;
/

The script is written like a template. You need only change the DECLARE section for another directory. I like this! I have not to care about which script to execute in which environment. It always works – because of PL/SQL.

Karl

UPDATE 09.01.2006

Used Dbms_Utility.Port_String for OS detection :

/** DATABASE.INTERFACES.UFA.Directories : User FA Tool (UFA)
   OBJECT_TYPE  : DIRECTORY
   OBJECT_NAME  : OASIS_UFA_IFX_OUT
   CREATOR      : Karl r. (EH2RE6SZ) CSC
   CREATED      : 18.10.2005
   RUN AS       : User with DBA Privileges
   VERSION      : 0.95
   CMNT         : Target directory for UFA Export

   09.01.2006   : Using dbms_utility.port_string for OS Detection -> Windows : 'IBMPC/WIN_NT-8.1.0'
*/

DECLARE
  l_Directory VARCHAR2(32) := 'OASIS_UFA_IFX_OUT';
  l_Ux_Path   VARCHAR2(256) := '/usr/xfit/OASIS/export/UFA';
  l_Win_Path  VARCHAR2(256) := 'D:\OASIS\Ifx\UFA';

BEGIN

  -- creating os dependent directory
  IF (Dbms_Utility.Port_String LIKE 'IBMPC/WIN_NT%') THEN
    -- A windows NT based database ...
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory || ' AS ''' ||
                      l_Win_Path || '''';
  ELSE
    -- A Non-Windows NT based database
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || l_Directory || ' AS ''' ||
                      l_Ux_Path || '''';
  END IF;

  -- Grants on directory
  EXECUTE IMMEDIATE 'GRANT READ  ON DIRECTORY ' || l_Directory ||
                    ' TO OASIS, ROLE_OASISAE, ROLE_OASISIFX';
  EXECUTE IMMEDIATE 'GRANT WRITE ON DIRECTORY ' || l_Directory ||
                    ' TO OASIS, ROLE_OASISAE, ROLE_OASISIFX';

END;
/

Thanks to Wilfred

Karl