René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

dbms_utility

Procedures/Functions

active_instances

procedure active_instances (
 instance_table                 out table of record                 ,
 instance_count                 out number                          
);

analyze_database

procedure analyze_database (
 method                          in varchar2                        ,
 estimate_rows                   in number                   default,
 estimate_percent                in number                   default,
 method_opt                      in varchar2                 default
);

analyze_part_object

procedure analyze_part_object (
 schema                          in varchar2                 default,
 object_name                     in varchar2                 default,
 object_type                     in char                     default,
 command_type                    in char                     default,
 command_opt                     in varchar2                 default,
 sample_clause                   in varchar2                 default
);

analyze_schema

procedure analyze_schema (
 schema                          in varchar2                        ,
 method                          in varchar2                        ,
 estimate_rows                   in number                   default,
 estimate_percent                in number                   default,
 method_opt                      in varchar2                 default
);

canonicalize

procedure canonicalize (
 name                            in varchar2                        ,
 canon_name                     out varchar2                        ,
 canon_len                       in binary_integer                  
);

comma_to_table

procedure comma_to_table (
 list                            in varchar2                        ,
 tablen                         out binary_integer                  ,
 tab                            out table of varchar2(227)          
);
procedure comma_to_table (
 list                            in varchar2                        ,
 tablen                         out binary_integer                  ,
 tab                            out table of varchar2(4000)         
);

compile_schema

procedure compile_schema (
 schema                          in varchar2                        ,
 compile_all                     in boolean                  default
);

create_alter_type_error_table

procedure create_alter_type_error_table (
 schema_name                     in varchar2                        ,
 table_name                      in varchar2                        
);

current_instance

function current_instance returns number (
);

data_block_address_block

function data_block_address_block returns number (
 dba                             in number                          
);
Returns the block number (within a datafile) for the given database block address (dba).

data_block_address_file

function data_block_address_file returns number (
 dba                             in number                          
);
Returns the datafile number for the given database block address.

db_version

procedure db_version (
 version                        out varchar2                        ,
 compatibility                  out varchar2                        
);
This procedure returns the Oracle version as well as the compatibility.
Theoretically, the version could also be selected through a select version from v$instance. However, this view belongs to sys and it is not always possible to select from it.
The returned compatibility is the value as specified through the compatible init parameter.

exec_ddl_statement

procedure exec_ddl_statement (
 parse_string                    in varchar2                        
);

format_call_stack

function format_call_stack returns varchar2 (
);

format_error_backtrace

function format_error_backtrace returns varchar2 (
);

format_error_stack

function format_error_stack returns varchar2 (
);

get_cpu_time

function get_cpu_time returns number (
);

get_dependency

procedure get_dependency (
 type                            in varchar2                        ,
 schema                          in varchar2                        ,
 name                            in varchar2                        
);

get_endianness

function get_endianness returns number (
);

get_hash_value

function get_hash_value returns number (
 name                            in varchar2                        ,
 base                            in number                          ,
 hash_size                       in number                          
);

get_parameter_value

function get_parameter_value returns binary_integer (
 parnam                          in varchar2                        ,
 intval                         out binary_integer          in/        ,
 strval                         out varchar2                in/        ,
 listno                          in binary_integer           default
);

get_sql_hash

function get_sql_hash returns number (
 name                            in varchar2                        ,
 hash                           out raw                             ,
 pre10ihash                     out number                          
);

get_time

function get_time returns number (
);

get_tz_transitions

procedure get_tz_transitions (
 regionid                        in number                          ,
 transitions                    out raw(32767)                      
);

is_bit_set

function is_bit_set returns number (
 r                               in raw                             ,
 n                               in number                          
);

is_cluster_database

function is_cluster_database returns boolean (
);

make_data_block_address

function make_data_block_address returns number (
 file                            in number                          ,
 block                           in number                          
);
Returns the database block address (dba) for the given file and block.

name_resolve

procedure name_resolve (
 name                            in varchar2                        ,
 context                         in number                          ,
 schema                         out varchar2                        ,
 part1                          out varchar2                        ,
 part2                          out varchar2                        ,
 dblink                         out varchar2                        ,
 part1_type                     out number                          ,
 object_number                  out number                          
);
declare
  context       number := 2;
  schema        varchar2(30);
  part1         varchar2(30);
  part2         varchar2(30);
  dblink        varchar2(128);
  part1_type    number;
  object_number number;
begin

  dbms_utility.name_resolve( '&1', context, schema, part1, part2, dblink, part1_type, object_number);

  dbms_output.put_line('Schema:      ' || schema);
  dbms_output.put_line('Part 1:      ' || part1);
  dbms_output.put_line('Part 2:      ' || part2);
  dbms_output.put_line('DB Link:     ' || dblink);
  dbms_output.put_line('Part 1 type: ' || part1_type);
  dbms_output.put_line('Object No:   ' || object_number);
end;
/
See also On using dbms_utility.name_resolve for a demonstration.

name_tokenize

procedure name_tokenize (
 name                            in varchar2                        ,
 a                              out varchar2                        ,
 b                              out varchar2                        ,
 c                              out varchar2                        ,
 dblink                         out varchar2                        ,
 nextpos                        out binary_integer                  
);

old_current_schema

function old_current_schema returns varchar2 (
);

old_current_user

function old_current_user returns varchar2 (
);

port_string

function port_string returns varchar2 (
);
dbms_utility.port_string allows to find out what hardware and OS an Oracle instance runs on:
SQL> select dbms_utility.port_string from dual;

PORT_STRING
-----------------------------------------------
IBMPC/WIN_NT-8.1.0

sqlid_to_sqlhash

function sqlid_to_sqlhash returns number (
 sql_id                          in varchar2                        
);

table_to_comma

procedure table_to_comma (
 tab                             in table of varchar2(227)          ,
 tablen                         out binary_integer                  ,
 list                           out varchar2                        
);
procedure table_to_comma (
 tab                             in table of varchar2(4000)         ,
 tablen                         out binary_integer                  ,
 list                           out varchar2                        
);

validate

procedure validate (
 object_id                       in number                          
);