10 UTL_IDENT

The UTL_IDENT package indicates which database or client PL/SQL is running in, such as TimesTen versus Oracle Database, and server versus client. Each database or client running PL/SQL has its own copy of this package.

This chapter contains the following topics:

Using UTL_IDENT

This section contains topics that relate to using the UTL_IDENT package.

Overview

The UTL_IDENT package indicates whether PL/SQL is running on TimesTen, an Oracle Database client, an Oracle Database server, or Oracle Forms. Each of these has its own version of UTL_IDENT with appropriate settings for the constants.

The primary use case for the UTL_IDENT package is for conditional compilation, resembling the following, of PL/SQL packages that are supported by Oracle Database, TimesTen, or clients such as Oracle Forms.

$if utl_ident.is_oracle_server $then
    [...Run code supported for Oracle Database...]
$elsif utl_ident.is_timesten $then
    [...code supported for TimesTen Database...]
$end

Also see "Examples".

Security model

The UTL_IDENT package runs as the package owner SYS. The public synonym UTL_IDENT and EXECUTE permission on this package are granted to PUBLIC.

Constants

The UTL_IDENT package uses the constants shown in Table 10-1, which indicates the settings for TimesTen.

Table 10-1 UTL_IDENT constants

Constant Type Value Description

IS_ORACLE_SERVER

BOOLEAN

FALSE

Stipulates whether Oracle Database.

IS_ORACLE_CLIENT

BOOLEAN

FALSE

Stipulates whether Oracle Client.

IS_ORACLE_FORMS

BOOLEAN

FALSE

Stipulates whether Oracle Forms.

IS_TIMESTEN

BOOLEAN

TRUE

Stipulates whether TimesTen.

Examples

This example uses the UTL_IDENT and TT_DB_VERSION packages to show information about the database being used. For the current release, it displays either "Oracle Database 11.2" or "TimesTen 11.2.1". The conditional compilation trigger character, $, identifies code that is processed before the application is compiled.

Command> run what_db.sql
 
create or replace function what_db
return varchar2
as
 dbname varchar2(100);
 version varchar2(100);
begin
$if utl_ident.is_timesten
$then
 dbname := 'TimesTen';
 version := substr(tt_db_version.version, 1, 2) ||
            '.' ||
            substr(tt_db_version.version, 3, 1) ||
            '.' ||
            substr(tt_db_version.version, 4, 1);
$elsif utl_ident.is_oracle_server
$then
 dbname := 'Oracle Database';
 version := dbms_db_version.version || '.' || dbms_db_version.release;
$else
 dbname := 'Non-database environment';
 version := '';
$end
 return dbname || ' ' || version;
end;
/
 
Function created.
 
set serveroutput on;
 
begin
dbms_output.put_line(what_db());
end;
/
 
TimesTen 11.2.1
 
PL/SQL procedure successfully completed.