Oracle Home selector

What does the Oracle home selector do?
Is the fact that it doesn’t appear to be included with 11g a problem?
And finally is Oracle locator express (ole.exe http://www.dbmotive.com/oracle_home_selector.php) an adequate replacement?

The first place to investigate the function of the Oracle home selector has to be the the Metalink Note:66464.1.

This note describes the behavior of the oracle home selector thus:

‘A change in selection [of the oracle home] means that the specified Oracle home will appear as the first directory in the PATH environment variable, but no change is written to the Windows NT Registry.’

Secondly we can analyse the behavior of the utility further using regmon and filemon (http://technet.microsoft.com/en-us/sysinternals/default.aspx)

Using regmon we can establish that;

It opens HKLMSOFTWAREORACLEALL_HOMES
It enumerates HKLMSOFTWAREORACLEALL_HOMES
It iteratively reads HKLMSOFTWAREORACLEALL_HOMESID?, HKLMSOFTWAREORACLEALL_HOMESID?
and HKLMSOFTWAREORACLEALL_HOMESID?PATH

It reads HKLMSYSTEMCurrentControlSetControlSession ManagerEnvironmentPATH

It removes the selected oracle home (if present) from the path, and it adds the selected oracle home to the beginning of the path, writing the resulting new path back to the registry.

Using filemon we can see that:
It writes to the system and system.alt files under c:winntsystem32configsystem folder (essentially confirmation that it is modifying the registry)

So contrary to the Metalink note, the way that the Oracle home selector changes the PATH variable is in fact implemented via a modification of the associated registry entry.

What doesn’t the Oracle Home selector do?

1 It doesn’t interact with the ORACLE_HOME environment variable.

2 It doesn’t touch the Java path related elements, so while it allows you to select a specific version of the oracle binaries that are to be used, the java binaries used will be the latest installed, unless manual editing of the PATH decrees otherwise.

3 It wont change the environment from the command line without user interaction. (It prompts the user to accept the home change even when this is done via the command line parameter.)

What does ole.exe do?

Well ole.exe uses different registry keys, enumeration all the keys under HKLMSOFTWAREORACLE and selecting the oracle path information from the HKLMSOFTWAREORACLEHOME?ORACLE_HOME tree, it also examines the TNS_ADMIN registry key and the TNS_ADMIN environment variable, although, as yet I haven’t seen the application modify any settings.

Is ole.exe an adequate replacement for the oracle home selector?

I suppose it is, although because it locates the ORACLE_HOME information from different registry settings, then it is possible that in some circumstances it may behave differently to the Oracle home selector.

Is the oracle home selector of any value at all?

Considering that it doesn’t appear to interact with the ORACLE_HOME environment variable, I can’t see that the utility has that much value anyway. If you have to change the ORACLE_HOME manually, then you are probably better off writing a wrapper for your oracle utilities instead:

So to run a specific version of sqlplus I create a bat file sqlplusw.bat that contains the following lines:

set ORACLE_HOME=c:orantdesigner
PATH c:orantdesignerbin
sqlplusw.exe

Which seems to me to be a much more convenient solution than using the home selector to change the path variable, and then use the System properties dialog box to change the ORACLE_HOME environment variable setting, and then invoke the utility.

So in conclusion, while OLE appears to be an adequate replacement for the Oracle Home selector, unless I have missed some functionality of the two utilities I can’t see any value in either except for the command line phobics of which I suppose there are a few.

PLSQL_Warning in 11g

In preparation for the on/off/on upgrade of our application from Oracle 8.1.7 to 9.2.8, I thought it would be interesting to migrate the PL/SQL to my installation of an 11g Oracle database that came out of the UKOUG conference OTN install fest, and also, out of interest see what warnings come out of the PL/SQL compiler when you set PLSQL_Warning=’enable:all’ compilation option.

Pleasingly out of almost 45,000 lines of source, only one new error was generated:

SQL> show errors;
Errors for PACKAGE BODY P_BODY:

LINE/COL ERROR
——– —————————————————————–
211/5 PLS-00593: default value of parameter “P_PARAM” in body must match that of spec

Here the only problem was that while Oracle 8i is happy for a default parameter value to be specified only in the package body definition, Oracle 11g requires the default parameter to be specified in both the package and the package body.

Eg. while the following code works in 8i, it doesn’t in later releases:

SQL> create or replace PACKAGE P_BODY IS
2 PROCEDURE delete_actual_data (
3 p_parameter IN VARCHAR2
4 );
5 END;
6 /

Package created.

SQL> create or replace PACKAGE body P_BODY IS
2 PROCEDURE delete_actual_data (
3 p_parameter IN VARCHAR2 := ‘N’
4 ) IS
5 BEGIN
6 NULL;
7 END;
8 END;
9
10 /

Package body created.

A small anomaly:

If a package does include errors or warnings, then if the package and body is compiled, then the errors are not displayed, only the following cryptic error message:

SQL> alter PACKAGE P_BODY compile plsql_warnings=’enable:all’;

SP2-0809: Package altered with compilation warnings

SQL> show errors;
SP2-0564: Object “p_bodyplsql_warnings=’enable:all’” is INVALID, it may not be described.

However if only the body is compiled, then the errors and warnings are successfully displayed.

So that leaves me with a selection of PLW warnings to evaluate and/or fix:

PLW-05005: function P_FUNCTION returns without value at line 244
PLW-06002: Unreachable code
PLW-06006: uncalled procedure “P_PROCEDURE” is removed.
PLW-06009: procedure “P_PROCEDURE” OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
PLW-06010: keyword “NAME” used as a defined name
PLW-06010: keyword “REF” used as a defined name
PLW-06010: keyword “RESULT” used as a defined name
PLW-07202: bind type would result in conversion away from column type
PLW-07203: parameter ‘P_PARAMETER’ may benefit from use of the NOCOPY compiler hint
PLW-07204: conversion away from column type may result in sub-optimal query plan

A superficial review of the error messages manual indicates that the number of potential warnings has grown substantially from the 9 and 11 warnings of 10g releases 1 and 2, to a total of 36 documented warnings in 11g release 1. With 3 of the 8 warning types received being new ones from 11g.

Oracle 10 R1 manual page:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10744/plwus.htm#sthref64

Oracle 10 R2 manual page:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/plwus.htm#ERRMG53664

Oracle 11 R1 manual page:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/plwus.htm