Archive

Archive for the ‘DBMS_UTILITY’ Category

PL/SQL Packages DBMS_UTILITY

November 14, 2009 Leave a comment

PL/SQL Packages DBMS_UTILITY

Source : {ORACLE_HOME}/rdbms/admin/dbmsutil.sql

COMPILE_SCHEMA Compile all procedures, functions, packages, and triggers in the specified schema.

Syntax 
DBMS_UTILITY.COMPILE_SCHEMA (
   schema VARCHAR2);
Code examples
EXEC DBMS_UTILITY.compile_schema('SCOTT');

ANALYZE_SCHEMA Analyze all the tables, clusters, and indexes in a schema.

Syntax
DBMS_UTILITY.ANALYZE_SCHEMA (
   schema           VARCHAR2,
   method           VARCHAR2,
   estimate_rows    NUMBER   DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT NULL,
   method_opt       VARCHAR2 DEFAULT NULL);
Code examples
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE')

[N.B: Do not collect stats for either the SYS or SYSTEM schemas.]

ANALYZE_DATABASE Analyze all the tables, clusters, and indexes in a database.

Syntax
DBMS_UTILITY.ANALYZE_DATABASE (
   method           VARCHAR2,
   estimate_rows    NUMBER   DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT NULL,
   method_opt       VARCHAR2 DEFAULT NULL);
Code examples
exec DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE');

ANALYZE_PART_OBJECT Equivalent to ANALYZE_DATABASE use for partition table.

Syntax
dbms_utility.analyze_part_object (
schema        IN VARCHAR2 DEFAULT NULL,
object_name   IN VARCHAR2 DEFAULT NULL,
object_type   IN CHAR DEFAULT 'T',
command_type  IN CHAR DEFAULT 'E',
command_opt   IN VARCHAR2 DEFAULT NULL,
sample_clause IN VARCHAR2 DEFAULT 'sample 5 percent');
Code examples
--assumes a partitioned table named 'part_tab'
exec dbms_utility.analyze_part_object('UWCLASS', 'PART_TAB', 'T', 'E', 'V');

FORMAT_ERROR_STACK Function Format the current error stack.

Syntax
DBMS_UTILITY.FORMAT_CALL_STACK
  RETURN VARCHAR2;
Code examples
CREATE TABLE error_log (
      timestamp     DATE,
      username      VARCHAR2(30),
      instance      NUMBER,
      database_name VARCHAR2(50),
      error_stack   VARCHAR2(2000)
      );

CREATE OR REPLACE TRIGGER LogErrors
      AFTER SERVERERROR ON DATABASE
    BEGIN
      INSERT INTO error_log
        VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK);
    END LogErrors;

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE In Oracle Database 10g, Oracle added DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, which can and should be called in your exception handler. It displays the call stack at the point where an exception was raised, even if the function is called in a PL/SQL block in an outer scope from that where the exception was raised. Thus, you can call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE within an exception section at the top level of your stack and still find out where the error was raised deep within the call stack.

Syntax
DBMS_UTILITY. FORMAT_ERROR_BACKTRACE
  RETURN VARCHAR2;

FORMAT_CALL_STACK Function Format the current call stack.
 
Code examples
CREATE OR REPLACE PROCEDURE error_test1 AS
    BEGIN
       RAISE VALUE_ERROR;
    END error_test1;
    /

CREATE OR REPLACE PROCEDURE error_test2 AS
    BEGIN
       error_test1;
    EXCEPTION
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('FORMAT_CALL_STACK ->' || DBMS_UTILITY.FORMAT_CALL_STACK);
          DBMS_OUTPUT.PUT_LINE('FORMAT_ERROR_STACK->' || DBMS_UTILITY.FORMAT_ERROR_STACK);
    END error_test2;
    /

SQL> SET SERVEROUTPUT ON
SQL> exec error_test2
FORMAT_CALL_STACK ->----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
2F226BE0         6  procedure SYSTEM.ERROR_TEST2
2F222E7C         1  anonymous block

FORMAT_ERROR_STACK->ORA-06502: PL/SQL: numeric or value error
PL/SQL procedure successfully completed.

IS_CLUSTER_DATABASE Function Is this database running in cluster database mode.

Syntax
DBMS_UTILITY.IS_CLUSTER_DATABASE
  RETURN NUMBER;
Code examples
SET SERVEROUTPUT ON
BEGIN
  IF DBMS_UTILITY.is_cluster_database THEN
    dbms_output.put_line('Clustered');
  ELSE
    dbms_output.put_line('Not Clustered');
  END IF;
END;
/

GET_TIME Function Current time in 100th’s of a second.

Syntax
DBMS_UTILITY.GET_TIME
  RETURN NUMBER;
Code examples
BEGIN
  dbms_output.put_line( 'Start Time: ' || dbms_utility.get_time );
  dbms_lock.sleep(seconds => 10);
  dbms_output.put_line( 'End Time:   ' || dbms_utility.get_time );
END;
/
Start Time: 1119218
End Time:   1120218

PORT_STRING Function Return a string that uniquely identifies the version of Oracle and the operating system.

Syntax
DBMS_UTILITY.PORT_STRING
   RETURN VARCHAR2;
Code examples
SELECT DBMS_UTILITY.port_string
FROM   dual;

DB_VERSION Version information for the database.

Syntax
DBMS_UTILITY.DB_VERSION (
   version       OUT VARCHAR2,
   compatibility OUT VARCHAR2);
Code examples
SET SERVEROUTPUT ON
DECLARE
  v_version  VARCHAR2(100);
  v_compatibility  VARCHAR2(100);
BEGIN
  DBMS_UTILITY.db_version (version => v_version, compatibility => v_compatibility);
  DBMS_OUTPUT.put_line('Version: ' || v_version || '  Compatibility: ' || v_compatibility);
END;
/

CURRENT_INSTANCE Function Return the current connected instance number.

Syntax
DBMS_UTILITY.CURRENT_INSTANCE
   RETURN NUMBER;
Code examples
SELECT DBMS_UTILITY.current_instance
FROM   dual;

ACTIVE_INSTANCES Active instance numbers and names

Syntax
DBMS_UTILITY.IS_CLUSTER_DATABASE
  RETURN NUMBER;
Code examples
SET SERVEROUTPUT ON
DECLARE
  v_instance_table  DBMS_UTILITY.instance_table;
  v_instance_count  NUMBER;
BEGIN
  DBMS_UTILITY.active_instances (instance_table => v_instance_table,instance_count => v_instance_count);

  IF v_instance_count > 0 THEN
    FOR i IN 1 .. v_instance_count LOOP
      DBMS_OUTPUT.put_line(v_instance_table(i).inst_number || ' = ' || v_instance_table(i).inst_name);
    END LOOP;
  END IF;
END;
/

EXEC_DDL_STATEMENT Execute the DDL statement in parse_string.

Syntax
DBMS_UTILITY.EXEC_DDL_STATEMENT (
   parse_string IN VARCHAR2);
Code examples
BEGIN
dbms_utility.exec_ddl_statement('create table emp_test as select * from scott.emp');
END;
/

GET_PARAMETER_VALUE Function  Get an init.ora parameter.

Syntax
DBMS_UTILITY.GET_PARAMETER_VALUE (
   parnam IN     VARCHAR2,
   intval IN OUT BINARY_INTEGER,
   strval IN OUT VARCHAR2)
  RETURN BINARY_INTEGER;

Code examples
DECLARE
  parnam VARCHAR2(256);
  intval BINARY_INTEGER;
  strval VARCHAR2(256);
  partyp BINARY_INTEGER;
BEGIN
  partyp := dbms_utility.get_parameter_value('db_block_size',intval, strval);
  dbms_output.put('parameter value is: ');
  IF partyp = 1 THEN
    dbms_output.put_line(strval);
  ELSE
    dbms_output.put_line(intval);
  END IF;
  IF partyp = 1 THEN
    dbms_output.put('parameter value length is: ');
    dbms_output.put_line(intval);
  END IF;
  dbms_output.put('parameter type is: ');
  IF partyp = 1 THEN
    dbms_output.put_line('string');
  ELSE
    dbms_output.put_line('integer');
  END IF;
END;
/
parameter value is: 8192
parameter type is: integer

COMMA_TO_TABLE Convert a comma-separated list of names into a PL/SQL table of names.

Syntax
DBMS_UTILITY.COMMA_TO_TABLE (
   list   IN  VARCHAR2,
   tablen OUT BINARY_INTEGER,
   tab    OUT UNCL_ARRAY);
Code examples
CREATE TABLE c2t_test (readline VARCHAR2(200));

INSERT INTO c2t_test VALUES ('"1","Mainframe","31-DEC-2001"');
INSERT INTO c2t_test VALUES ('"2","MPP","01-JAN-2002"');
INSERT INTO c2t_test VALUES ('"3","Mid-Size","02-FEB-2003"');
INSERT INTO c2t_test VALUES ('"4","PC","03-MAR-2004"');
INSERT INTO c2t_test VALUES ('"5","Macintosh","04-APR-2005"');
COMMIT;

SELECT * FROM c2t_test;

CREATE TABLE test_import (
src_no NUMBER(5),
src_desc VARCHAR2(20),
load_date DATE);

CREATE OR REPLACE PROCEDURE load_c2t_test IS

c_string VARCHAR2(250);
cnt      BINARY_INTEGER;
my_table dbms_utility.lname_array;

BEGIN
  FOR t_rec IN (SELECT * FROM c2t_test)
  LOOP
    dbms_utility.comma_to_table(t_rec.readline, cnt, my_table);

    my_table(1) := TRANSLATE(my_table(1), 'A"','A');
    my_table(2) := TRANSLATE(my_table(2), 'A"','A');
    my_table(3) := TRANSLATE(my_table(3), 'A"','A');

    INSERT INTO test_import
    (src_no, src_desc, load_date)
    VALUES
    (TO_NUMBER(my_table(1)), my_table(2), TO_DATE(my_table(3)));
  END LOOP;
  COMMIT;
END load_c2t_test;
/

exec load_c2t_test;

SELECT * FROM test_import;

TABLE_TO_COMMA Converts a PL/SQL table of names into a comma-separated list of names.

Syntax
DBMS_UTILITY.TABLE_TO_COMMA (
   tab    IN  UNCL_ARRAY,
   tablen OUT BINARY_INTEGER,
   list   OUT VARCHAR2);

NAME_RESOLVE Resolve the given name.

Syntax
DBMS_UTILITY.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);

NAME_TOKENIZE Parse the given name.

Syntax
DBMS_UTILITY.NAME_TOKENIZE (
   name    IN  VARCHAR2,
   a       OUT VARCHAR2,
   b       OUT VARCHAR2,
   c       OUT VARCHAR2,
   dblink  OUT VARCHAR2,
   nextpos OUT BINARY_INTEGER);
Code examples
 declare
        v_a      varchar2(30);
        v_b      varchar2(30);
        v_c      varchar2(30);
        v_dblink varchar2(30);
        v_next   number;

        type vcArray is table of varchar2(255);
        v_names vcArray := vcArray( 'tamimdba@gmail.com','tamim@oracledba','tamim.xyz' );
   begin
       for i in 1 .. v_names.count
       loop
       begin
           dbms_utility.name_tokenize(name   => v_names(i),
                                      a      => v_a,
                                      b      => v_b,
                                      c      => v_c,
                                      dblink => v_dblink,
                                      nextpos=> v_next );

           dbms_output.put_line( 'name    ' || v_names(i) );
           dbms_output.put_line( 'A       ' || v_a );
           dbms_output.put_line( 'B       ' || v_b );
           dbms_output.put_line( 'C       ' || v_c );
           dbms_output.put_line( 'dblink  ' || v_dblink );
           dbms_output.put_line( 'next    ' || v_next || ' ' || length(v_names(i)));
       exception
           when others then
               dbms_output.put_line( 'name    ' || v_names(i) );
               dbms_output.put_line( sqlerrm );
       end;
       end loop;
   end;

GET_HASH_VALUE Function Compute a hash value for the given string.

Syntax
DBMS_UTILITY.GET_HASH_VALUE (
   name      VARCHAR2,
   base      NUMBER,
   hash_size NUMBER)
  RETURN NUMBER;
Code examples 
begin
   dbms_output.put_line ( dbms_utility.get_hash_value( to_char(1), 0, 1024 ) );
end;

DATA_BLOCK_ADDRESS_FILE Function Get the file number part of a data block address.

Syntax
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (
   dba NUMBER)
  RETURN NUMBER;

MAKE_DATA_BLOCK_ADDRESS Function Create a data block address given a file number and a block number.

Syntax
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS (
   file  NUMBER,
   block NUMBER)
  RETURN NUMBER;
Code examples 
SELECT dbms_utility.make_data_block_address(4, 6)
FROM dual;

DATA_BLOCK_ADDRESS_BLOCK Function Get the block number part of a data block address.

Syntax
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (
   dba NUMBER)
  RETURN NUMBER;
Code examples 
SELECT file_name, file_id
FROM dba_data_files;

SELECT dbms_utility.data_block_address_file(16777222)
FROM dual;
Advertisements
Categories: DBMS_UTILITY Tags: