Oracle7 Server Application Developer's Guide

Supplied Packages

Several packaged procedures are provided with Oracle to either allow PL/SQL access to some SQL features, or to extend the functionality of the database. You may need to take advantage of the functionality provided by these packages when creating your application, or you may simply want to use these packages for ideas in creating your own stored procedures. This section lists each of the supplied packages and indicates where they are described in more detail.

These packages run as the invoking user rather than the package owner. The packaged procedures are callable through public synonyms of the same name.

Support for SQL Features

Oracle is supplied with the following packaged procedures, which allow PL/SQL to access some features of SQL. The footnotes at the end of Table 7 - 3 explain any restrictions on the use of each procedure. You should consult the package specifications for the most up-to-date information on these packages.

Package Procedure(Arguments) SQL Command Equivalent
DBMS_SESSION close_database_link( dblink varchar2) ALTER SESSION CLOSE DATABASE dblink
reset_package (see note 5) This procedure reinitializes the state of all packages; there is no SQL equivalent
set_label(lbl varchar2) (note 4) ALTER SESSION SET LABEL text
set_mls_label_format( fmt varchar2) (note 4) ALTER SESSION SET MLS_LABEL_FORMAT = fmt
set_nls(param varchar2, value varchar2) (notes 1,4) ALTER SESSION SET nls_param = nls_param_values
set_role(role_cmd varchar2) (notes 1, 6) SET ROLE ...
set_sql_trace(sql_trace boolean) ALTER SESSION SET SQL_TRACE = [TRUE | FALSE]
unique_session_id return varchar2 This function returns a unique session ID; there is no SQL equivalent.
is_role_enabled return boolean This function is used to determine if a role is enabled; there is no SQL equivalent.
set_close_cached_open_cursors( close_cursors boolean) ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS
free_unused_user_memory This procedure lets you reclaim unused memory; there is no SQL equivalent.
DBMS_DDL alter_compile(type varchar2, schema varchar2, name varchar2) (notes 1, 2, 3, 4 ALTER PROCEDURE proc COMPILE
ALTER FUNCTION func COMPILE
ALTER PACKAGE pack COMPILE
analyze_object( type varchar2, schema varchar2, name varchar2, method varchar2, estimate_rows number default null, estimate_percent number default null) ANALYZE INDEX
ANALYZE TABLE
ANALYZE CLUSTER
DBMS_ TRANSACTION
advise_commit ALTER SESSION ADVISE COMMIT
advise_rollback ALTER SESSION ADVISE ROLLBACK
advise_nothing ALTER SESSION ADVISE NOTHING
commit (notes 1,2,4) COMMIT
commit_comment(cmnt varchar2) (notes 1,2,4) COMMIT COMMENT text
commit_force(xid varchar2, scn varchar2 default null) (notes 1,2,3,4) COMMIT FORCE text ...
read_only (notes 1,3,4) SET TRANSACTION READ ONLY
read_write (notes 1,3,4) SET TRANSACTION READ WRITE
rollback (notes 1,2,4) ROLLBACK
rollback_force(xid varchar2) (notes 1,2,3,4) ROLLBACK ... FORCE text ...
rollback_savepoint( svpt varchar2) (notes 1,2,4) ROLLBACK ... TO SAVEPOINT ...
savepoint(savept varchar2) (notes 1,2,4) SAVEPOINT savepoint
use_rollback_segment( rb_name varchar2) (notes 1,2,4) SET TRANSACTION USE ROLLBACK SEGMENT segment
purge_mixed(xid in number) See Oracle7 Server Distributed Systems, Volume I for more information
begin_discrete_transaction (notes 1,3,4,5) See the Oracle7 Server Tuning manual for more information
local_transaction_id( create_transaction BOOLEAN default FALSE) return VARCHAR2 See Oracle7 Server Distributed Systems, Volume I for more information
step_id return number See Oracle7 Server Distributed Systems, Volume I for more information
DBMS_UTILITY compile_schema(schema varchar2) (notes 1,2,3,4) This procedure is equivalent to calling alter_compile on all procedures, functions, and packages accessible by you. Compilation is completed in dependency order.
analyze_schema( schema varchar2, method varchar2, estimate_rows number default null, estimate_percent number default null) This procedure is equivalent to calling analyze_object on all objects in the given schema.
format_error_stack return varchar2 This function formats the error stack into a variable.
format_call_stack return varchar2 This function formats the current call stack into a variable.
is_parallel_server return boolean This function returns TRUE when running in Parallel Server mode.
get_time return number This function returns the time in hundredths of a second.
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) See Oracle7 Server Distributed Systems, Volume I for more information

Table 7 - 3. (continued) Supplied Packages: SQL Features

1 not allowed in triggers
2 not allowed in procedures called from SQL*Forms
3 not allowed in read-only transactions
4 not allowed in remote (coordinated) sessions
5 not allowed in recursive sessions
6 not allowed in stored procedures

For more details on each SQL command equivalent, refer to the Oracle7 Server SQL Reference manual. The COMMIT, ROLLBACK, ROLLBACK... TO SAVEPOINT, and SAVEPOINT procedures are directly supported by PL/SQL; they are included in the package for completeness.

Additional Functionality

Several packages are supplied with Oracle to extend the functionality of the database. The cross-reference column in Table 7 - 4 tells you where to look for more information on each of these packages.

Package Name Description Cross-reference
DBMS_ALERT Supports asynchronous notification of database events. Chapter 12
DBMS_DESCRIBE Lets you describe the arguments of a stored procedure.
DBMS_JOB Lets you schedule administrative procedures that you want performed at periodic intervals. Oracle7 Server Administrator's Guide
DBMS_LOCK Lets you use the Oracle Lock Management services for your applications.
DBMS_OUTPUT Lets you output messages from triggers, procedures, and packages.
DBMS_PIPE Allows sessions in the same instance to communicate with each other. Chapter 8
DBMS_SHARED_POOL Lets you keep objects in shared memory, so that they will not be aged out with the normal LRU mechanism. Oracle7 Server Tuning
DBMS_APPLICATION_ INFO Lets you register an application name with the database for auditing or performance tracking purposes. Oracle7 Server Tuning
DBMS_SYSTEM Provides system-level utilities, such as letting you enable SQL trace for a session. Oracle7 Server Tuning
DBMS_SPACE Provides segment space information not available through standard views. Oracle7 Server Administrator's Guide
DBMS_SQL Lets you write stored procedures and anonymous PL/SQL blocks using dynamic SQL; lets you parse any DML or DDL statement. Chapter 10
DBMS_REFRESH Lets you create groups of snapshots that can be refreshed together to a transactionally consistent point in time. Use of this feature requires the distributed option. Oracle7 Server Distributed Systems, Volume II
DBMS_SNAPSHOT Lets you refresh one or more snapshots that are not part of the same refresh group, purge snapshot log. Use of this feature requires the distributed option. Oracle7 Server Distributed Systems, Volume II
DBMS_DEFER, DMBS_DEFER_SYS, DBMS_DEFER_QUERY Lets you build and administer deferred remote procedure calls. Use of this feature requires the replication option. Oracle7 Server Distributed Systems, Volume II
DBMS_REPCAT Lets you use Oracle's symmetric replication facility. Use of this feature requires the replication option. Oracle7 Server Distributed Systems, Volume II
DBMS_REPCAT_AUTH, DBMS_REPCAT_ADMIN Lets you create users with the privileges needed by the symmetric replication facility. Use of this feature requires the replication option. Oracle7 Server Distributed Systems, Volume II

Table 7 - 4. (continued) Supplied Packages: Additional Functionality

Package	Procedure(Arguments)	SQL Command Equivalent
`DBMS_SESSION`	`close_database_link( dblink varchar2)`	`ALTER SESSION CLOSE DATABASE dblink`
	`reset_package (see note 5)`	This procedure reinitializes the state of all packages; there is no SQL equivalent
	`set_label(lbl varchar2) (note 4)`	`ALTER SESSION SET LABEL text`
	`set_mls_label_format( fmt varchar2) (note 4)`	`ALTER SESSION SET MLS_LABEL_FORMAT = fmt`
	`set_nls(param varchar2, value varchar2) (notes 1,4)`	`ALTER SESSION SET nls_param = nls_param_values`
	`set_role(role_cmd varchar2) (notes 1, 6)`	`SET ROLE ...`
	`set_sql_trace(sql_trace boolean)`	`ALTER SESSION SET SQL_TRACE = [TRUE \| FALSE]`
	`unique_session_id return varchar2`	This function returns a unique session ID; there is no SQL equivalent.
	`is_role_enabled return boolean`	This function is used to determine if a role is enabled; there is no SQL equivalent.
	`set_close_cached_open_cursors( close_cursors boolean)`	`ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS`
	`free_unused_user_memory`	This procedure lets you reclaim unused memory; there is no SQL equivalent.
`DBMS_DDL`	`alter_compile(type varchar2, schema varchar2, name varchar2) (notes 1, 2, 3, 4`	`ALTER PROCEDURE proc COMPILE`
		`ALTER FUNCTION func COMPILE`
		`ALTER PACKAGE pack COMPILE`
	`analyze_object( type varchar2, schema varchar2, name varchar2, method varchar2, estimate_rows number default null, estimate_percent number default null)`	`ANALYZE INDEX`
		`ANALYZE TABLE`
		`ANALYZE CLUSTER`
`DBMS_ TRANSACTION`	`advise_commit`	`ALTER SESSION ADVISE COMMIT`
	`advise_rollback`	`ALTER SESSION ADVISE ROLLBACK`
	`advise_nothing`	`ALTER SESSION ADVISE NOTHING`
	`commit (notes 1,2,4)`	`COMMIT`
	`commit_comment(cmnt varchar2) (notes 1,2,4)`	`COMMIT COMMENT text`
	`commit_force(xid varchar2, scn varchar2 default null) (notes 1,2,3,4)`	`COMMIT FORCE text ...`
	`read_only (notes 1,3,4)`	`SET TRANSACTION READ ONLY`
	`read_write (notes 1,3,4)`	`SET TRANSACTION READ WRITE`
	`rollback (notes 1,2,4)`	`ROLLBACK`
	`rollback_force(xid varchar2) (notes 1,2,3,4)`	`ROLLBACK ... FORCE text ...`
	`rollback_savepoint( svpt varchar2) (notes 1,2,4)`	`ROLLBACK ... TO SAVEPOINT ...`
	`savepoint(savept varchar2) (notes 1,2,4)`	`SAVEPOINT savepoint`
	`use_rollback_segment( rb_name varchar2) (notes 1,2,4)`	`SET TRANSACTION USE ROLLBACK SEGMENT segment`
	`purge_mixed(xid in number)`	See Oracle7 Server Distributed Systems, Volume I for more information
	`begin_discrete_transaction (notes 1,3,4,5)`	See the Oracle7 Server Tuning manual for more information
	`local_transaction_id( create_transaction BOOLEAN default FALSE) return VARCHAR2`	See Oracle7 Server Distributed Systems, Volume I for more information
	`step_id return number`	See Oracle7 Server Distributed Systems, Volume I for more information
`DBMS_UTILITY`	`compile_schema(schema varchar2) (notes 1,2,3,4)`	This procedure is equivalent to calling `alter_compile` on all procedures, functions, and packages accessible by you. Compilation is completed in dependency order.
	`analyze_schema( schema varchar2, method varchar2, estimate_rows number default null, estimate_percent number default null)`	This procedure is equivalent to calling `analyze_object` on all objects in the given schema.
	`format_error_stack return varchar2`	This function formats the error stack into a variable.
	`format_call_stack return varchar2`	This function formats the current call stack into a variable.
	`is_parallel_server return boolean`	This function returns TRUE when running in Parallel Server mode.
	`get_time return number`	This function returns the time in hundredths of a second.
	`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)`	See Oracle7 Server Distributed Systems, Volume I for more information

Package Name	Description	Cross-reference
`DBMS_ALERT`	Supports asynchronous notification of database events.	Chapter 12
`DBMS_DESCRIBE`	Lets you describe the arguments of a stored procedure.
`DBMS_JOB`	Lets you schedule administrative procedures that you want performed at periodic intervals.	Oracle7 Server Administrator's Guide
`DBMS_LOCK`	Lets you use the Oracle Lock Management services for your applications.
`DBMS_OUTPUT`	Lets you output messages from triggers, procedures, and packages.
`DBMS_PIPE`	Allows sessions in the same instance to communicate with each other.	Chapter 8
`DBMS_SHARED_POOL`	Lets you keep objects in shared memory, so that they will not be aged out with the normal LRU mechanism.	Oracle7 Server Tuning
`DBMS_APPLICATION_ INFO`	Lets you register an application name with the database for auditing or performance tracking purposes.	Oracle7 Server Tuning
`DBMS_SYSTEM`	Provides system-level utilities, such as letting you enable SQL trace for a session.	Oracle7 Server Tuning
`DBMS_SPACE`	Provides segment space information not available through standard views.	Oracle7 Server Administrator's Guide
`DBMS_SQL`	Lets you write stored procedures and anonymous PL/SQL blocks using dynamic SQL; lets you parse any DML or DDL statement.	Chapter 10
`DBMS_REFRESH`	Lets you create groups of snapshots that can be refreshed together to a transactionally consistent point in time. Use of this feature requires the distributed option.	Oracle7 Server Distributed Systems, Volume II
`DBMS_SNAPSHOT`	Lets you refresh one or more snapshots that are not part of the same refresh group, purge snapshot log. Use of this feature requires the distributed option.	Oracle7 Server Distributed Systems, Volume II
`DBMS_DEFER, DMBS_DEFER_SYS, DBMS_DEFER_QUERY`	Lets you build and administer deferred remote procedure calls. Use of this feature requires the replication option.	Oracle7 Server Distributed Systems, Volume II
`DBMS_REPCAT`	Lets you use Oracle's symmetric replication facility. Use of this feature requires the replication option.	Oracle7 Server Distributed Systems, Volume II
`DBMS_REPCAT_AUTH, DBMS_REPCAT_ADMIN`	Lets you create users with the privileges needed by the symmetric replication facility. Use of this feature requires the replication option.	Oracle7 Server Distributed Systems, Volume II