Search notes:

Oracle: DBMS_UTILITY

dbms_utility contains various utility functions.

`active_instances`
`analyze_database`
`analyze_part_object`	Deprecated in favor of using `dbms_stats`
`analyze_schema`
`canonicalize`	`a` becomes `A`, `"a"` becomes `a`, `"a".b` becomes `"a"."B"`, `"a".b,c.f` becomes `"a"."B"` (the `c.f` part is ignored).
`comma_to_table`
`compile_schema`
`create_alter_type_error_table`
`current_instance`
`data_block_address_block`	Gets the block number from a data block address (see conversion between a data block address and a file/block number)
`data_block_address_file`	Gets the file number from a data block address (see conversion between a data block address and a file/block number)
`db_version`
`directory_has_symlink`	This function is used in `$ORACLE_HOME/rdbms/admin/utldirsymlink.sql`. This function is not documented in Oracles documentation 18c through 21c.
`exec_ddl_statement`	Executes a DDL statement
`expand_sql_text`	Transforms an SQL statement into another semantically equivalent SQL statement that does not contain any references to synonyms or views.
`format_call_stack`	Returns the call stack as a text (where the individual stack items are separated by new lines)
`format_error_backtrace`	Returns the call stack at the point where an exception was raised, even if called from an exception handler in a different scope.
`format_error_stack`
`get_cpu_time`	Returns the current CPU time as 100th's of a second from some arbitrary epoch. Compare with `get_time`.
`get_dependency`	Deprecated in favor uf using `dba_dependencies` etc. directly.
`get_endianness`
`get_hash_value`	Computes a hash value for a given string.
`get_parameter_value`	Queries `v$parameter`. Deprecated
`get_sql_hash`
`get_time`	Returns a value of a counter that is increased 100 times per seconds. The value 0 seems to be somewhat related to the startup time of the instance. Cmpare with `get_cpu_time`.
`get_tz_transitions`
`invalidate`
`is_bit_set`	Checks if a given bit is set in a value whose data type is raw. Compare with the SQL function `bitand`.
`is_cluster_database`
`make_data_block_address`	Gets the data block address from the block's file and block number (see DBMS_UTILITY: Conversion between data block address (DBA) and file/block number)
`name_resolve`
`name_tokenize`
`old_current_schema`
`old_current_user`
`port_string`	Returns a string that uniquely identifies the operating system and the two task protocol version, for example `IBMPC/WIN_NT64-9.1.0`.
`sqlid_to_sqlhash`	Converts an SQLID to the corresponding hash value
`table_to_comma`
`validate`
`wait_on_pending_dml`

COMPILE_SCHEMA

select name, type, substrb(text, 1, 100) from user_errors;

create table tq84_cs_tab (
  col_foo varchar2(10),
  col_bar varchar2(10),
  col_baz varchar2(10)
);

create view tq84_cs_vw as select * from tq84_cs_tab;

select name, type from user_errors;

alter table tq84_cs_tab rename column col_baz to xyz;


select name, type from user_errors;
exec dbms_utility.compile_schema(user, false)
select name, type, substrb(text, 1, 100) from user_errors;


alter table tq84_cs_tab rename column xyz to col_baz;
exec dbms_utility.compile_schema(user, false)
select name, type, substrb(text, 1, 100) from user_errors;


drop view  tq84_cs_vw;
drop table tq84_cs_tab purge;

Github repository Oracle-Patterns, path: /Installed/dbms/utility/compile_schema.plsql

DB_VERSION

declare
   v   varchar2(100);
   c   varchar2(100);
begin
   dbms_utility.db_version(v, c);
   dbms_output.put_line(v);
   dbms_output.put_line(c);
end;
/
--
-- Version:       18.0.0.0.0
-- Compatibility: 12.0.0

Compare with procedures in dbms_db_version.version.

See also attribute instance_role in v$instance, Determine database versions

FORMAT_ERROR_STACK

--
--  Compare with format_error_backtrace.plsql
--

create or replace package tq84_error_trace as -- {

    procedure p;

end tq84_error_trace; 
/
-- }

create or replace package body tq84_error_trace as -- {

    procedure p3 is begin
        execute immediate 'error';
    end p3;

    procedure p2 is begin
        p3;
    end p2;

    procedure p1 is begin
        p2;
    end p1;

    procedure p is begin
        p1;
    end p;

end tq84_error_trace; 
/
-- }

begin
  tq84_error_trace.p;
exception when others then
  dbms_output.put_line('Error: ' || dbms_utility.format_error_stack);
end;
/


drop package tq84_error_trace;

Github repository Oracle-Patterns, path: /Installed/dbms/utility/format_error_stack.plsql

Error: ORA-00900: invalid SQL statement
ORA-06512: at "RENE.TQ84_ERROR_TRACE", line 4
ORA-06512: at "RENE.TQ84_ERROR_TRACE", line 8
ORA-06512: at "RENE.TQ84_ERROR_TRACE", line 12
ORA-06512: at "RENE.T
Q84_ERROR_TRACE", line 16

FORMAT_ERROR_BACKTRACE

--
--  Compare with format_error_stack.plsql
--

create or replace package tq84_backtrace as -- {

    procedure p;

end tq84_backtrace; 
/
-- }

create or replace package body tq84_backtrace as -- {

    procedure p3 is begin
        execute immediate 'error';
    end p3;

    procedure p2 is begin
        p3;
    end p2;

    procedure p1 is begin
        p2;
    end p1;

    procedure p is begin
        p1;
    end p;

end tq84_backtrace; 
/
-- }

begin
  tq84_backtrace.p;
exception when others then
  dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
/


drop package tq84_backtrace;

Github repository Oracle-Patterns, path: /Installed/dbms/utility/format_error_backtrace.plsql

ORA-06512: at "RENE.TQ84_BACKTRACE", line 4
ORA-06512: at "RENE.TQ84_BACKTRACE", line 8
ORA-06512: at "RENE.TQ84_BACKTRACE", line 12
ORA-06512: at "RENE.TQ84_BACKTRACE", line 16
RA-06512: at line 2

TIME

begin
  dbms_output.put_line('time:     ' || dbms_utility.get_time);
  dbms_output.put_line('cpu time: ' || dbms_utility.get_cpu_time);
end;
/

Github repository Oracle-Patterns, path: /Installed/dbms/utility/time.plsql