PC World Komputer 1996 February/PCWK0296.iso/po7_win/db/rdbms71/dbmsutil.sql

Text File | 1994-08-07 | 21.5 KB | 512 lines

rem
rem $Header: dbmsutil.sql 7010300.1 94/02/24 18:25:58 snataraj Generic<base> $
rem
Rem  Copyright (c) 1991 by Oracle Corporation
Rem    NAME
Rem      dbmsutil.sql - packages of various utility procedures
Rem    DESCRIPTION
Rem      This file contains various packages:
Rem         dbms_transaction    - transaction commands
Rem         dbms_session    - alter session commands
Rem         dbms_ddl        - ddl commands
Rem         dbms_utility    - helpful utilities
Rem    RETURNS
Rem
Rem    NOTES
Rem      The procedural option is needed to use these facilities.
Rem
Rem      All of the packages below run with the privileges of calling user,
Rem      rather than the package owner ('sys').
Rem
Rem      Procedure 'dbms_ddl.alter_compile' and 'dbms_ddl.analyze_object
Rem      commit the current transaction, perform the compilation, and
Rem      then commit again.
Rem
Rem      The dbms_utility package is run-as-caller (psdicd.c) only for
Rem      its name_resolve, compile_schema and analyze_schema
Rem      procedures.  This package is not run-as-caller
Rem      w.r.t. SQL (psdpgi.c) so that the SQL works correctly (runs as
Rem      SYS).  The privileges are checked via dbms_ddl.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem     wmaimone   02/07/94 -  add set close_cached_open_cursors to dbms_sessio
Rem     dsdaniel   02/04/94 -  dbms_util.port_string icd
Rem     adowning   02/02/94 -  split file into public / private binary files
Rem     rjenkins   10/28/93 -  make comma_to_table more consistent
Rem     rjenkins   10/12/93 -  adding comma_to_table
Rem     rjenkins   09/03/93 -  adding name_parse
Rem     hjakobss   07/15/93 -  bug 170473
Rem     hjakobss   07/13/93 -  bug 169577
Rem     dsdaniel   03/12/93 -  local_tid, step_id functions for replication
Rem     mmoore     01/11/93 -  merge changes from branch 1.37.312.1
Rem     mmoore     01/05/93 - #(145287) add another exception for discrete mode
Rem     mmoore     12/11/92 -  disable set_role in stored procs
Rem     rkooi      11/24/92 -  fixes per Peter
Rem     rkooi      11/21/92 -  get rid of error argument to name_resolve
Rem     tpystyne   11/20/92 -  fix compile_all and analyze_schema
Rem     rkooi      11/16/92 -  fix set_label
Rem     rkooi      11/16/92 -  fix comments
Rem     rkooi      11/13/92 -  add name_res procedure
Rem     tpystyne   11/07/92 -  make analyze parameters optional
Rem     mmoore     11/04/92 -  add new analyze options
Rem     ghallmar   11/03/92 -  add dbms_transaction.purge_mixed
Rem     rkooi      10/30/92 -  get rid of caller_id and unique_stmt_id
Rem     rkooi      10/26/92 -  owner -> schema for SQL2
Rem     rkooi      10/25/92 -  bug 135880
Rem     mmoore     10/13/92 - #(131686) change messages 2074,4092,0034
Rem     rkooi      10/02/92 -  compile_all fix
Rem     mmoore     10/02/92 -  change pls_integer to binary_integer
Rem     tpystyne   10/01/92 -  fix Bob's mistakes
Rem     tpystyne   09/28/92 -  disallow commit/rollback force in rpc and trigge
Rem     mmoore     09/25/92 - #(130566) don't allow set_nls or set_role in trig
Rem     tpystyne   09/23/92 -  rename analyze to analyze_object
Rem     rkooi      08/24/92 -  handle delimited id's in alter_compile
Rem     tpystyne   08/06/92 -  add analyze_schema
Rem     epeeler    07/29/92 -  add function to get time
Rem     rkooi      06/25/92 -  workaround pl/sql bug with 'in' in SQL
Rem     rkooi      06/03/92 -  add 'get unique session id'
Rem     jcohen     05/28/92 -  add = to alter session set label
Rem     jloaiza    05/12/92 -  add discrete
Rem     rkooi      04/22/92 -  put in checks for execute_sql for triggs, stored
Rem     mmoore     04/14/92 -  move begin_oltp to package transaction
Rem     rkooi      04/06/92 -  merge changes from branch 1.4.300.1
Rem     rkooi      04/01/92 -  Creation - split/recombined from other files
Rem     mroberts   02/21/92 -  call alter_compile, not sql_ddl
Rem     rkooi      02/06/92 -  testing
Rem     rkooi      02/03/92 -  compilation errors
Rem     rkooi      01/16/92 -  Creation

REM ********************************************************************
REM THESE PACKAGES MUST NOT BE MODIFIED BY THE CUSTOMER.  DOING SO
REM COULD CAUSE INTERNAL ERRORS AND SECURITY VIOLATIONS IN THE
REM RDBMS.  SPECIFICALLY, THE PSD* AND EXECUTE_SQL ROUTINES MUST NOT BE
REM CALLED DIRECTLY BY ANY CLIENT AND MUST REMAIN PRIVATE TO THE PACKAGE BODY.
REM ********************************************************************

create or replace package dbms_transaction is

  ------------
  --  OVERVIEW
  --
  --  This package provides access to SQL transaction statements from
  --  stored procedures.
  --  It also provids functions for monitoring transaction activities
  --  (transaction ids and ordering of steps of transactions )

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure read_only;
  --  Equivalent to SQL "SET TRANSACTION READ ONLY"
  procedure read_write;
  --  Equivalent to SQL "SET TRANSACTION READ ONLY"
  procedure advise_rollback;
  --  Equivalent to SQL "ALTER SESSION ADVISE ROLLBACK"
  procedure advise_nothing;
  --  Equivalent to SQL "ALTER SESSION ADVISE NOTHING"
  procedure advise_commit;
  --  Equivalent to SQL "ALTER SESSION ADVISE COMMIT"
  procedure use_rollback_segment(rb_name varchar2);
  --  Equivalent to SQL "SET TRANSACTION USE ROLLBACK SEGMENT <rb_seg_name>"
  --  Input arguments:
  --    rb_name
  --      Name of rollback segment to use.
  procedure commit_comment(cmnt varchar2);
  --  Equivalent to SQL "COMMIT COMMENT <text>"
  --  Input arguments:
  --    cmnt
  --      Comment to assoicate with this comment.
  procedure commit_force(xid varchar2, scn varchar2 default null);
  --  Equivalent to SQL "COMMIT FORCE <text>, <number>"
  --  Input arguments:
  --    xid
  --      Local or global transaction id.
  --    scn
  --      System change number.
  procedure commit;
    pragma interface (C, commit);                          -- 1 (see psdicd.c)
  --  Equivalent to SQL "COMMIT".  Here for completeness.  This is
  --    already implemented as part of PL/SQL.
  procedure savepoint(savept varchar2);
    pragma interface (C, savepoint);               -- 2 (see psdicd.c)
  --  Equivalent to SQL "SAVEPOINT <savepoint_name>".  Here for
  --    completeness. This is already implemented as part of PL/SQL.
  --  Input arguments:
  --    savept
  --      Savepoint identifier.
  procedure rollback;
    pragma interface (C, rollback);               -- 3 (see psdicd.c)
  --  Equivalent to SQL "ROLLBACK".  Here for completeness. This is
  --    already implemented as part of PL/SQL.
  procedure rollback_savepoint(savept varchar2);
    pragma interface (C, rollback_savepoint);           -- 4 (see psdicd.c)
  --  Equivalent to SQL "ROLLBACK TO SAVEPOINT <savepoint_name>".  Here for
  --    completeness. This is already implemented as part of PL/SQL.
  --  Input arguments:
  --    savept
  --      Savepoint identifier.
  procedure rollback_force(xid varchar2);
  --  Equivalent to SQL "ROLLBACK FORCE <text>"
  --  Input arguments:
  --    xid
  --      Local or global transaction id.
  procedure begin_discrete_transaction;
    pragma interface (C, begin_discrete_transaction);      -- 5 (see psdicd.c)
  --  Set "discrete transaction mode" for this transaction.
  --  Exceptions:
  --    ORA-08175 will be generated if a transaction attempts an operation
  --      which cannot be performed as a discrete transaction.  If this
  --      exception is encountered, rollback and retry the transaction.

  --    ORA-08176 will be generated if a transaction encounters data changed
  --      by an operation that does not generate rollback data : create index,
  --      direct load or discrete transaction.  If this exception is
  --      encountered, retry the operation that received the exception.
  --
  DISCRETE_TRANSACTION_FAILED exception;
    pragma exception_init(DISCRETE_TRANSACTION_FAILED, -8175);

  CONSISTENT_READ_FAILURE exception;
    pragma exception_init(CONSISTENT_READ_FAILURE, -8176);

  procedure purge_mixed(xid varchar2);
  --  When indoubt transactions are forced to commit or rollback (instead of
  --    letting automatic recovery resolve their outcomes), there is a
  --    possibility that a transaction can have a mixed outcome: some sites
  --    commit, and others rollback.  Such inconsistency cannot be resolved
  --    automatically by ORACLE; however, ORACLE will flag entries in
  --    DBA_2PC_PENDING by setting the MIXED column to a value of 'yes'.
  --    ORACLE will never automatically delete information about a mixed
  --    outcome transaction.  When the application or DBA is sure all
  --    inconsistencies that might have arisen as a result of the mixed
  --    transaction have been resolved, this procedure can be used to
  --    delete the information about a given mixed outcome transaction.
  --  Input arguments:
  --    xid
  --      This must be set to the value of the LOCAL_TRAN_ID column in
  --      the DBA_2PC_PENDING table.

  FUNCTION local_transaction_id(create_transaction BOOLEAN := FALSE)
    RETURN VARCHAR2;
  --  Return local (to instance) unique identfier for current transaction
  --  Return null if there is no current transction.
  --  Input parmaeters:
  --     create_transaction
  --       If true , start a transaciton if one is not currently
  --       active.
  --
  FUNCTION step_id RETURN NUMBER;
  --  Return local (to local transaction ) unique positive integer that orders
  --  The DML operations of a transaction.
  --  Input parmaeters:

end;
/
drop public synonym dbms_transaction
/
create public synonym dbms_transaction for sys.dbms_transaction
/
grant execute on dbms_transaction to public
/

create or replace package dbms_session is
  ------------
  --  OVERVIEW
  --
  --  This package provides access to SQL "alter session" statements, and
  --  other session information from, stored procedures.

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure set_role(role_cmd varchar2);
  --  Equivalent to SQL "SET ROLE ...".
  --  Input arguments:
  --    role_cmd
  --      This text is appended to "set role " and then executed as SQL.
  procedure set_sql_trace(sql_trace boolean);
  --  Equivalent to SQL "ALTER SESSION SET SQL_TRACE ..."
  --  Input arguments:
  --    sql_trace
  --      TRUE or FALSE.  Turns tracing on or off.
  procedure set_nls(param varchar2, value varchar2);
  --  Equivalent to SQL "ALTER SESSION SET <nls_parameter> = <value>"
  --  Input arguments:
  --    param
  --      The NLS parameter. The parameter name must begin with 'NLS'.
  --    value
  --      The value to set the parameter to.  If the parameter is a
  --      text literal then it will need embedded single-quotes.  For
  --      example "set_nls('nls_date_format','''DD-MON-YY''')"
  procedure close_database_link(dblink varchar2);
  --  Equivalent to SQL "ALTER SESSION CLOSE DATABASE LINK <name>"
  --  Input arguments:
  --    name
  --      The name of the database link to close.
  procedure set_label(lbl varchar2);
  --  Equivalent to SQL "ALTER SESSION SET LABEL <label specification>"
  --  Input arguments:
  --    lbl
  --      Either 'DBHIGH', 'DBLOW', or a text literal.
  procedure set_mls_label_format(fmt varchar2);
  --  Equivalent to SQL "ALTER SESSION SET MLS_LABEL_FORMAT <format>"
  --  Input arguments:
  --    fmt
  --      Format specification for the label.
  procedure reset_package;
  --  Deinstantiate all packages in this session.  In other words, free
  --    all package state.  This is the situation at the beginning of
  --    a session.
  function unique_session_id return varchar2;
  --  Return an identifier that is unique for all sessions currently
  --    connected to this database.  Multiple calls to this function
  --    during the same session will always return the same result.
  --  Output arguments:
  --    unique_session_id
  --      can return up to 24 bytes.
  function is_role_enabled(rolename varchar2) return boolean;
  --  Determine if the named role is enabled for this session.
  --  Input arguments:
  --    rolename
  --      Name of the role.
  --  Output arguments:
  --    is_role_enabled
  --      TRUE or FALSE depending on whether the role is enabled.
  procedure set_close_cached_open_cursors(close_cursors boolean);
  --  Equivalent to SQL "ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS ..."
  --  Input arguments:
  --    close_cursors
  --      TRUE or FALSE.  Turns close_cached_open_cursors on or off.
end;
/

drop public synonym dbms_session
/
create public synonym dbms_session for sys.dbms_session
/
grant execute on dbms_session to public
/

create or replace package dbms_ddl is
  ------------
  --  OVERVIEW
  --
  --  This package provides access to some SQL DDL statements from
  --  stored procedures.

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure alter_compile(type varchar2, schema varchar2, name varchar2);
  --  Equivalent to SQL "ALTER PROCEDURE|FUNCTION|PACKAGE [<schema>.]
  --    <name> COMPILE [BODY]".  If the named object is this package,
  --    or any packages upon which it depends (currently "standard" or
  --    "dbms_standard") then the procedure simply returns (since these
  --    packages are clearly successfully compiled).
  --  Input arguments:
  --    type
  --      Must be one of "PROCEDURE", "FUNCTION", "PACKAGE" or "PACKAGE
  --      BODY".
  --    schema
  --      The schema name.  If NULL then use current schema.  Case sensitive.
  --    name
  --      The name of the object.  Case sensitive.
  --  Exceptions:
  --    ORA-20000: Insufficient privileges or object does not exist.
  --    ORA-20001: Remote object, cannot compile.
  --    ORA-20002: Bad value for object type.  Should be one of PACKAGE,
  --      PACKAGE BODY, PROCEDURE, or FUNCTION.
  procedure analyze_object
    (type varchar2, schema varchar2, name varchar2, method varchar2,
     estimate_rows number default null,
     estimate_percent number default null);
  --  Equivalent to SQL "ANALYZE TABLE|CLUSTER|INDEX [<schema>.]<name>
  --    [<method>] STATISTICS [SAMPLE <n> [ROWS|PERCENT]]"
  --  Input arguments:
  --    type
  --      One of 'TABLE', 'CLUSTER' or 'INDEX'.  If none of these, the
  --      procedure just returns.
  --    schema
  --      schema of object to analyze.  NULL means current schema.  Case
  --      sensitive.
  --    name
  --      name of object to analyze.  Case sensitive.
  --    method
  --      NULL or 'ESTIMATE'.  If 'ESTIMATE' then either estimate_rows
  --      or estimate_percent must be non-zero.
  --    estimate_rows
  --      Number of rows to estimate
  --    estimate_percent
  --      Percentage of rows to estimate.  If estimate_rows is specified
  --      than ignore this parameter.
  --  Exceptions:
  --    ORA-20000: Insufficient privileges or object does not exist.
  --    ORA-20001: Bad value for object type.  Should be one of TABLE, INDEX
  --      or CLUSTER.
end;
/

drop public synonym dbms_ddl
/
create public synonym dbms_ddl for sys.dbms_ddl
/
grant execute on dbms_ddl to public
/

create or replace package dbms_utility is
  ------------
  --  OVERVIEW
  --
  --  This package provides various utility routines.

  ----------------------------
  --  PL/SQL TABLES
  --
  type uncl_array IS table of VARCHAR2(227) index by BINARY_INTEGER;
  --  Lists of "USER"."NAME"."COLUMN"@LINK should be stored here

  type name_array IS table of VARCHAR2(30) index by BINARY_INTEGER;
  --  Lists of NAME should be stored here

  type dblink_array IS table of VARCHAR2(128) index by BINARY_INTEGER;
  --  Lists of database links should be stored here

  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure compile_schema(schema varchar2);
  --  Compile all procedures, functions and packages in the specified
  --  schema.  After calling this procedure you should select from view
  --  ALL_OBJECTS for items with status of 'INVALID' to see if all objects
  --  were successfully compiled.  You may use the SQLDBA command "SHOW
  --  ERRORS <type> <schema>.<name>" to see the errors assocated with
  --  'INVALID' objects.
  --  Input arguments:
  --    schema
  --      Name of the schema.
  --  Exceptions:
  --    ORA-20000: Insufficient privileges for some object in this schema.
  procedure analyze_schema(schema varchar2, method varchar2,
    estimate_rows number default null,
    estimate_percent number default null);
  --  Analyze all the tables, clusters and indexes in a schema.
  --  Input arguments:
  --    schema
  --      Name of the schema.
  --    method, estimate_rows, estimate_ercent
  --      See the descriptions above in sql_ddl.analyze.object.
  --  Exceptions:
  --    ORA-20000: Insufficient privileges for some object in this schema.
  function format_error_stack return varchar2;
    pragma interface (C, format_error_stack);            -- 1 (see psdicd.c)
  --  Format the current error stack.  This can be used in exception
  --    handlers to look at the full error stack.
  --  Output arguments:
  --    format_error_stack
  --      Returns the error stack.  May be up to 2000 bytes.
  function format_call_stack return varchar2;
    pragma interface (C, format_call_stack);            -- 2 (see psdicd.c)
  --  Format the current call stack.  This can be used an any stored
  --    procedure or trigger to access the call stack.  This can be
  --    useful for debugging.
  --  Output arguments:
  --    format_call_stack
  --      Returns the call stack.  May be up to 2000 bytes.
  function is_parallel_server return boolean;
  --  Find out if this database is running in parallel server mode.
  --  Output arguments:
  --    is_parallel_server
  --      TRUE if this instance was started in parallel server mode,
  --      FALSE otherwise.
  function get_time return number;
  --  Find out the current time in 100th's of a second.
  --  Output argukments:
  --    get_time
  --      The time is the number of 100th's of a second from some
  --      arbitrary epoch.
  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);
  --  Resolve the given name.  Do synonym translation if necessary.  Do
  --    authorization checking.
  --  Input arguments:
  --    name
  --      The name of the object.  This can be of the form [[a.]b.]c[@d]
  --      where a,b,c are SQL identifier and d is a dblink.  No syntax
  --      checking is performed on the dblink.  If a dblink is specified,
  --      of the name resolves to something with a dblink, then object
  --      is not resolved, but the schema, part1, part2 and dblink out
  --      arguments are filled in.  a,b and c may be delimted identifiers,
  --      and may contain NLS characters (single and multi-byte).
  --    context
  --      Not currently used, must be set to 1 for future compatibility.
  --  Output arguments:
  --    schema
  --      The schema of the object.  If no schema is specified in 'name'
  --      then the schema is determined by resolving the name.
  --    part1
  --      The first part of the name.  The type of this name is specified
  --      part1_type (synonym, procedure or package).
  --    part2
  --      If this is non-null, then this is a procedure name within the
  --      package indicated by part1.
  --    dblink
  --      If this is non-null then a database link was either specified
  --      as part of 'name' or 'name' was a synonym which resolved to
  --      something with a database link.  In this later case, part1_type
  --      will indicate a synonym.
  --    part1_type
  --      The type of part1 is
  --        5 - synonym
  --        7 - procedure (top level)
  --        8 - function (top level)
  --        9 - package
  --      If a synonym, it means that 'name' is a synonym that translats
  --      to something with a database link.  In this case, if further
  --      name translation is desired, then you must call the
  --      dbms_utility.name_resolve procedure on this remote node.
  --    object_number
  --      If non-null then 'name' was successfully resolved and this is the
  --      object number which it resolved to.
  --  Exceptions:
  --    All errors are handled by raising exceptions.  A wide variety of
  --    exceptions are possible, based on the various syntax error that
  --    are possible when specifying object names.
  procedure name_tokenize( name    in  varchar2,
                   a       out varchar2,
                   b       out varchar2,
                       c       out varchar2,
                   dblink  out varchar2,
                           nextpos out binary_integer);
  --  Call the parser to parse the given name as "a [. b [. c ]][@ dblink ]".
  --  Strip doublequotes, or convert to uppercase if there are no quotes.
  --    Ignore comments of all sorts.  Do no semantic analysis.  Leave any
  --      missing values as null.
  --  For each of a,b,c,dblink, tell where the following token starts
  --    in anext,bnext,cnext,dnext respectively.
  PROCEDURE comma_to_table( list   IN  VARCHAR2,
                            tablen OUT BINARY_INTEGER,
                            tab    OUT uncl_array);
  --  Convert a comma-separated list of names into a PL/SQL table of names
  --  This uses name_tokenize to figure out what are names and what are commas

  PROCEDURE table_to_comma( tab    IN  uncl_array,
                            tablen OUT BINARY_INTEGER,
                            list   OUT VARCHAR2);
  --  Convert a PL/SQL table of names into a comma-separated list of names

  FUNCTION port_string RETURN VARCHAR2;
  --  Return a string that uniquely identifies the version of Oracle and
  --  the port (operating system).  EG "VAX/VMX-7.1.0.0"
  --  maximum length is port specific.

end;
/

drop public synonym dbms_utility
/
create public synonym dbms_utility for sys.dbms_utility
/
grant execute on dbms_utility to public
/