12 UTL_IDENT

Using UTL_IDENT

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

• Overview

• Security model

• Constants

• Examples

---

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 in Table 12-1, shown here with the settings in a TimesTen installation.

---

Examples

This example shows output from a script that creates and executes a function IS_CLOB_SUPPORTED that uses the UTL_IDENT and TT_DB_VERSION packages to provide information about the database being used. The function uses UTL_IDENT to determine whether the database is TimesTen, then uses TT_DB_VERSION to determine the TimesTen version. VER_LE_1121 is TRUE for TimesTen 11.2.1 releases and FALSE for TimesTen 11g Release 2 (11.2.2) releases. In the example, because VER_LE_1121 is determined to be FALSE, then it can be assumed that this is a TimesTen 11g Release 2 (11.2.2) release and therefore LOBs are supported. The example then creates a table with a CLOB column and shows DESCRIBE output of the table.

create or replace function is_clob_supported return boolean
as
begin
$if utl_ident.is_oracle_server
$then
 return true;
$elsif utl_ident.is_timesten
$then
 $if tt_db_version.ver_le_1121
 $then
  return false; -- CLOB datatype was introduced in 11g Release 2 (11.2.2)
 $else
  return true;
 $end
$end
end;
 
Function created.
 
show errors;
No errors.
 
begin
  if is_clob_supported
  then
    execute immediate 'create table mytab (mydata clob)';
  else
    execute immediate 'create table mytab (mydata varchar2(4000000))';
  end if;
end;
 
PL/SQL procedure successfully completed.
 
describe mytab;
 
Table MYSCHEMA.MYTAB:
  Columns:
    MYDATA                          CLOB
 
1 table found.
(primary key columns are indicated with *)

(Output is shown after running the commands from a SQL script.)