a trip to the alien world of

A TRIP TO THE ALIEN WORLD ofBuilt-in Packages in Oracle9i Rel2

by

Mark Sastry MA, MCT, OCPOracle Instructor

Email: [email protected]

MARK SASTRY - Presented at Twincities Oracle User Group 2January 16th, 2003

Agenda INTRODUCTION HISTORY OF PL/SQL ABOUT PACKAGES HOW TO EXPLORE PACKAGES TECHNOLOGIES NEW IN Rel2 PACKAGES IN ACTION:

ANYDATA UTL_FILE DBMS_REDEFINITION DBMS_LDAP DBMS_METADATA

Q & AThe Marree Man, is Australia's and the worlds biggest geoglyph.


INTRODUCTION


Alien World of Packages

Oracle has provided a powerful collection of built-in packages as if they are out of this world, an alien world for two reasons: They are strange and hard to

understand. They demonstrate superior

technologies.

HISTORY OF PL/SQL


PL/SQL - Evolution

PL/SQL 1.0 had a debut as Procedural Option with Oracle6. PL/SQL was implemented within SQL*Forms 3.0.

PL/SQL 1.1 supported client-side subprograms to execute stored code transparently. PL/SQL 2.0 with Oracle7 supported stored procedures, functions, packages, user-defined record

types, PL/SQL tables and many package extensions, including DBMS_OUTPUT and DBMS_PIPE.

PL/SQL 2.1 supported user-defined subtypes, use of stored functions inside SQL statements and dynamic SQL with the DBMS_SQL package.

PL/SQL 2.2 implemented wrapper for PL/SQL programs to protect source code, supported cursor variables and made database-driven job scheduling available with DBMS_JOB package.

PL/SQL 2.3 supported remote dependency management, file I/O within PL/SQL. PL/SQL 8.0 with Oracle8 supported LOBs, VARRAYs and Nested tables and Oracle Advanced

Queuing functionality. PL/SQL 8.1 with Oracle8i introduced Native Dynamic SQL, NOCOPY parameter option, Profiler,

Bulk Binds, Autonomous Transactions, and new database triggers such as startup, shutdown, logon and logoff.

PL/SQL 9i supports Native compilation of PL/SQL, common SQL parser, integration of XML with SQL and PL/SQL with invocation of XMLType from PL/SQL, HTTP cookie support in PL/SQL, inheritance support in PL/SQL, PL/SQL CASE expressions, and globalized datatypes such as timestamp, interval, unichar, univarchar2 and uniclob.


ABOUT PACKAGES


What are the advantages of packages?

A package is a collection of PL/SQL elements, such as variables, constants, types, cursors, exceptions and program units.

ADVANTAGES: Packages extend the PL/SQL language to support features that are

available only when low-level languages, such as C, C++ are used. Packages enable to organize application development. Packages enable to manage granting privileges more efficiently. Packages may be modified without recompiling dependent schema objects. Multiple packages may be brought into memory at once. Multiple procedures may be created with the same name in the same

package, each taking arguments of different number or datatype. Global variables and cursors may be created for use in all procedures and

functions in the package.


Packages in Oracle9i Rel2

Total number of installed built-in packages in Oracle9i Rel2 is 554. Most of them are X-Packages like X-Files. (TRUTH IS OUT THERE)

The number of installed packages owned by SYS in Oracle9i Rel2 is 357. Most of these are for internal use by Oracle and therefore are not granted EXECUTE privileges. Oracle grants the EXECUTE privileges for the DBMS packages to the following four roles: EXECUTE_CATALOG_ROLE EXP_FULL_DATABASE IMP_FULL_DATABASE PUBLIC

The number of SYS owned packages with EXECUTE privileges granted to PUBLIC is 95.


DBMS Packages Enhanced in Oracle9i Rel2

1. DBMS_DDL2. DBMS_FLASHBACK3. DBMS_LOB4. DBMS_LOGMNR5. DBMS_LOGMNR_CDC_PUBLISH6. DBMS_LOGMNR_CDC_SUBSCRIBE7. DBMS_LOGMNR_D8. DBMS_METADATA9. DBMS_REDEFINITION

10. DBMS_RLS11. DBMS_SPACE_ADMIN12. DBMS_STATS13. DBMS_TRANSFORM14. DBMS_WM15. DBMS_XMLGEN16. DBMS_XMLQUERY17. DBMS_XMLSAVE18. DBMS_XMLSchema


Package Descriptions

DBMS_DDL Provides access to some SQL DDL statements from stored procedures, and provides special administration operations not available as DDLs.

DMBS_FLASHBACK Lets you flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN).

DBMS_LOB Provides general purpose routines for operations on Oracle Large Object (LOBs) datatypes - BLOB,CLOB (read/write), and BFILEs (read-only).

DBMS_LOGMNR Provides functions to initialize and run the log reader. DBMS_LOGMNR_CDC_PUBLISH Identifies new data that has been added to, modified, or

removed from, relational tables and publishes the changed data in a form that is usable by an application.

DBMS_LOGMNR_CDC_SUBSCRIBE Lets you view and query the change data that was captured and published with the DBMS_LOGMNR_CDC_PUBLISH package.

DBMS_LOGMNR_D Queries the dictionary tables of the current database, and creates a text based file containing their contents.


Package Descriptions DBMS_METADATA Lets callers easily retrieve complete database object definitions (metadata)

from the dictionary. DBMS_REDEFINITION Lets you perform an online reorganization of tables. DBMS_RLS Provides row level security administrative interface. DBMS_SPACE_ADMIN Provides tablespace and segment space administration not available

through the standard SQL DBMS_STATS Provides a mechanism for users to view and modify optimizer statistics gathered

for database objects. DBMS_TRANSFORM Provides an interface to the message format transformation features of

Oracle Advanced Queuing. DBMS_WM Describes how to use the programming interface to Oracle Database Workspace

Manager to work with long transactions. DBMS_XMLGEN Converts the results of a SQL query to a canonical XML format. DBMS_XMLQUERY Provides database-to-XMLType functionality. DBMS_XMLSAVE Provides XML-to-database-type functionality. DBMS_XMLSCHEMA Explains procedures to register and delete XML schemas.


New Packages in Oracle9i Rel2

1. DBMS_APPLY_ADM2. DBMS_CAPTURE_ADM3. DBMS_LOGSTDBY4. DBMS_MGWADM5. DBMS_MGWMSG6. DBMS_PROPAGATION_ADM7. DBMS_RULE8. DBMS_RULE_ADM9. DBMS_STORAGE_MAP

10. DBMS_STREAMS11. DBMS_STREAMS_ADM12. DBMS_XDB13. DBMS_XDBT14. DBMS_XDB_VERSION15. DBMS_XMLDOM16. DBMS_XMLPARSER17. DBMS_XPLAN18. DBMS_XSLPROCESSOR


Package Descriptions DBMS_APPLY_ADM Provides administrative procedures to start, stop, and configure an apply

process. DBMS_CAPTURE_ADM Describes administrative procedures to start, stop, and configure a

capture process; used in Streams. DBMS_LOGSTDBY Describes procedures for configuring and managing the logical standby

database environment. DBMS_MGWADM Describes the Messaging Gateway administrative interface; used in Advanced

Queuing. DBMS_MGWMSG Describes object types—used by the canonical message types to convert

message bodies—and helper methods, constants, and subprograms for working with the Messaging Gateway message types; used in Advanced Queuing.

DBMS_PROPAGATION_ADM Provides administrative procedures for configuring propagation from a source queue to a destination queue.

DBMS_RULE Describes the EVALUATE procedure used in Streams. DBMS_RULE_ADM Describes the administrative interface for creating and managing rules, rule

sets, and rule evaluation contexts; used in Streams. DBMS_STORAGE_MAP Communicates with FMON to invoke mapping operations. DBMS_STREAMS Describes the interface to convert SYS.AnyData objects into LCR objects and

an interface to annotate redo entries generated by a session with a binary tag.


Package Descriptions DBMS_STREAMS_ADM Describes administrative procedures for adding and

removing simple rules, without transformations, for capture, propagation, and apply at the table, schema, and database level.

DBMS_WM Describes how to use the programming interface to Oracle Database Workspace Manager to work with long transactions.

DBMS_XDB Describes Resource Management and Access Control APIs for PL/SQL DBMS_XDBT Describes how an administrator can create a ConText index on the

XML DB hierarchy and configure it for automatic maintenance DBMS_XDB_VERSION Describes versioning APIs DBMS_XMLDOM Explains access to XMLType objects DBMS_XMLPARSER Explains access to the contents and structure of XML

documents. DBMS_XPLAN Describes how to format the output of the EXPLAIN PLAN command. DBMS_XSLPROCESSOR Explains access to the contents and structure of XML

documents.


EXPLORING PACKAGES


Before using a package

Before using a package, ensure Package is installed. Users have appropriate access privileges to the package. Most of the packages are created during Oracle installation under

SYS user. The scripts, Catalog.sql and catproc.sql (from $ORACLE_HOME/rdbms/admin) install these packages in most cases.

For public packages, these scripts create public synonyms with the same name as the package and grant EXECUTE privilege on the package to <role>.


Before using a package - SOURCES

With new built-in packages, the following sources should help:Documentation - Oracle supplied PL/SQL Packages and Types Reference.Notes in $ORACLE_HOME/relnotes/readmesAny notes on metalink, and lastlyInstallation scripts for any embedded helpful comments in them.

NEW TECHNOLOGIES

WITHIN ORACLE9i REL2


New Technologies in Rel2

The new technology areas in Rel2 are:Distributed Database

Replication, Streams, AQ, and HSStandby Database

Logical Standby DatabaseXML Database


Distributed Database

DISTRIBUTED DATABASE A distributed database is a network of databases managed by multiple database servers that are working together.

BENEFITThe primary benefit of a distributed database is that the data of physically separate databases can be logically combined and potentially made accessible to all users on a network.

INHERENT CHARACTERISTICSLocation TransparencySite AutonomyDistributed Data Manipulation, and Two-Phase Commit


In a distributed database, data is available at many locations, but a

particular table resides at only one location.

Services implemented to complement the experiences with distributed databases: Replication Streams Advanced Queuing, and Heterogeneous Services

Distributed Database


REPLICATIONReplication is the process of copying and maintaining database objects, such as

tables, in multiple databases. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations.

Replication uses distributed database technology to share data between multiple sites, but a replicated database and a distributed database are not the same.

Replication means that the same data is available at multiple locations. For example, the employees table can be available at db1, db2, and db3.

Distributed Database - Replication


Data can be replicated using materialized views. Oracle supports materialized views that are hierarchical and

updateable. Multi-tier replication provides increased flexibility of design for a distributed application. Using multi-tier materialized views, applications can manage multilevel data subsets with no direct connection between levels.

An updateable materialized view lets you insert, update, and delete

rows in the materialized view and propagate the changes to the target master table. Synchronous and asynchronous replication is supported.

Distributed Database - Replication


Oracle Streams enables the sharing of data and events in a data stream, either within a database or between databases.

Using Oracle Streams, you control what information is put into a stream, how the stream flows or is routed from database to database, what happens to events in the stream as they flow into each database, and how the stream terminates. By configuring specific capabilities of Streams, you can address specific requirements.

Based on your specifications, Streams can capture and manage events in the database automatically, including, but not limited to, DML changes and DDL changes.

Distributed Database - STREAMS


You can use Streams to: Capture changes at a database. Enqueue events into a queue Propagate events from one queue to another Dequeue events from a queue. Apply events at a database

Other capabilities of Streams include the following: Tags in captured LCRs Directed networks Automatic conflict detection and resolution Transformations Heterogeneous information sharing

Distributed Database - STREAMS


Oracle Advanced Queuing provides an infrastructure for distributed applications to communicate asynchronously using messages.

Messages pass between clients and servers, as well as between processes on different servers.

Messages pass between clients and servers, as well as between processes on different servers. A messaging system implements content-based routing, subscription, and querying.

Distributed Database - AQ


Heterogeneous Services (HS) allows you to access a non-Oracle database system.

Heterogeneous Services is generally applied in one of two ways: Oracle Transparent Gateway used in conjunction with Heterogeneous Services to access a particular, vendor-specific, non-

Oracle system for which an Oracle Transparent Gateways is designed. For example, you would use the Oracle Transparent Gateway for Sybase on Solaris to access a Sybase database system that was operating on a Solaris platform.

Heterogeneous Services’ generic connectivity used to access non-Oracle databases through ODBC or OLE DB interfaces.

Distributed Database - HS


A standby database is a replica created from the backup of a primary database. By applying archived redo logs from the primary database to the standby database, both the databases are synchronized.

A logical standby database is logically identical to the primary database but is updated using SQL statements. The tables in a logical standby database can be used simultaneously for recovery and for other tasks such as reporting, summations, and queries.

The logical standby database is open in read/write mode, but the target tables for the regenerated SQL are available only in read-only mode for reporting purposes.

BENEFITS: Efficient use of system resources Decision support

Standby Database - Logical


Oracle XML DB is a set of built-in high-performance storage and retrieval technologies geared to XML.

Oracle XML DB can be used to store, query, update, transform, or otherwise process XML, while at the same time providing SQL access to the same XML data.

BENEFITS: The ability to store and manage both structured and unstructured

data under the same standard W3C XML data model (XML Schema). Complete transparency and interchangeability between the XML and

SQL data views. Valuable Repository functionality Better management of unstructured XML data by XML indexing, and

others

XML Database


PACKAGESIN ACTION


ANYDATA

AnyData Type package is introduced in Oracle9i Rel1 to allow flexible typing of objects. This packages has the following program units:

BEGINCREATE Static procedure – begins the create process on a new AnyData

PIECEWISE Member procedure – sets the MODE of access of the current data value to be an attribute at a time if the data value is of TYPEODE_OBJECT

SET Member procedure – sets the current data value ENDCREATE Member procedure – ends creation of an AnyData GETTYPENAME Member function – get the fully qualified type

name for the AnyData GETTYPE Member function – gets the Type of the AnyData GET Member functions – gets the current data value


There are 2 ways to construct an AnyData: Make Convert*() calls. Use BeginCreate(), Set*(), EndCreate() procedures.

ANYDATA

USING CONVERTNUMBER PROCEDURE SQL> DECLARE 2 l_type ANYTYPE; 3 v_varN NUMBER := 13; 4 a_var ANYDATA; 5 BEGIN 6 a_var := ANYDATA.ConvertNumber(v_varN); 7 DBMS_OUTPUT.PUT_LINE(a_var.getType(l_type)); 8 DBMS_OUTPUT.PUT_LINE(a_var.getTypeName); 9 END; 10 / 2 SYS.NUMBER PL/SQL procedure successfully completed.

USING CONVERTOBJECT PROCEDURE SQL> create type t1 as object( x number, y varchar2(20)); 2 / Type created. SQL> DECLARE 2 l_type ANYTYPE; 3 v_varR t1; 4 a_var ANYDATA; 5 BEGIN 6 a_var := ANYDATA.ConvertObject( v_varR ); 7 DBMS_OUTPUT.PUT_LINE(a_var.getType(l_type)); 8 DBMS_OUTPUT.PUT_LINE(a_var.getTypeName); 9 END; 10 / 108 SYS.T1 PL/SQL procedure successfully completed.


Both server and client side PL/SQL programs can use the UTL_FILE package although file system access is always on the server host.

Five enhancements are made to UTL_FILE in 9i Rel2 are: Security changes: uses the CREATE DIRECTORY statement Handles binary data Adjusts pointer forward or backward Flushes automatically after write Copies, renames, deletes, and gets properties of files at the operating

system level.

UTL_FILE


Prior releases controlled the server side access through the UTL_FILE_DIR initialization parameter. With Oracle9i Database Release 2, the server side access is controlled through the directory object feature that is enabled through the CREATE DIRECTORY command.

The CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.

EXAMPLE:

SQL> CREATE DIRECTORY user_dir AS ‘/appl/dba/’;

SQL> GRANT READ ON DIRECTORY user_dir to DBA;

UTL_FILE


UTL_FILE.GET_RAW and UTL_FILE.PUT_RAW are used to process files containing RAW data.

GET_RAW function reads a raw string value and adjusts the

files pointer ahead by the number of bytes read. PUT_RAW function accepts raw data as input and writes the value to

the output buffer.

Adjust pointer forwards or backwards with UTL_FILE.FSEEK procedure.

UTL_FILE


Four new routines are added to UTL_FILE to remove, copy, find attributes, and rename operating system files:

UTL_FILE.FREMOVE procedure UTL_FILE.FCOPY procedure UTL_FILE.FGETATTR procedure UTL_FILE.FRENAME function

UTL_FILE


What can be done with DBMS_REDEFINITION on a table? A non-partitioned table can be converted into a partitioned table,

and vice versa The organization of a table can be changed from a heap based to

IOTs (Index Organized Tables), and vice versa Non-primary key columns can be dropped New columns can be added to a table Existing columns can be renamed Parallel support can be added or removed Storage parameters can be modified

DBMS_REDEFINITION


The program units within the package are:

CAN_REDEF_TABLE – determines if a given table can be redefined online

START_REDEF_TABLE – initiates the redefinition process. FINISH_REDEF_TABLE – completes the redefinition process. SYNC_INTERIM_TABLE – keeps the interim table

synchronized with the original table. ABORT_REDEF_TABLE – cleans up errors that occur during the

redefinition process.

DBMS_REDEFINITION


Restrictions on the tables that can be redefined online are as follows: Must have a primary key (A primary key is mandatory since materialized views and logs are

created during the start of redefinition.) Tables that have materialized views and materialized view logs defined on them cannot be

redefined online. Tables that are materialized view container tables and AQ tables cannot be redefined online The overflow table of an IOT table cannot be redefined online. Cannot have User-defined data types Cannot have FILE or LONG columns Cannot be clustered Cannot be in the SYS or SYSTEM schema Cannot be an horizontal subsetting of data Must be re-organized within the same schema Looses its snapshot logs Can get new columns as part of the re-organization, but the new columns must be declared

NULL until the re-organization is complete.

DBMS_REDEFINITION


Privileges needed to perform table redefinition online:

Execute On DBMS_REDEFINITION Create Any Table Alter Any Table Drop Any Table Lock Any Table Select Any Table

DBMS_REDEFINITION


The PL/SQL API for OiD (DBMS_LDAP) is available with Oracle 8.1.7 release. OID version shipped with RDBMS 8.1.7 is 2.1.1.

OID is not a requirement for DBMS_LDAP as it is part of Oracle 8i Enterprise Edition.DBMS_LDAP can also be used against other LDAP compliant directories.

DBMS_LDAP shipped with version 8.1.7 is LDAP V2 compliant. From 9.0.1 onwards DBMS_LDAP is LDAP V3 compliant.

The DBMS_LDAP package is not supported in the MTS mode in 8.1.7. This has been fixed in Oracle 9.0.1.

In Oracle9i Rel 1, the memory consumption of oracle shadow process grows if DBMS_LDAP is used repeatedly. The only workaround is to exit the sessions which are using dbms_ldap regularly in order to terminate the dedicated server process. This bug is fixed in Oracle9i Rel2.

DBMS_LDAP


This package is used to get the definitions of about 45 database objects. GET_DDL function is used to get the DDL to recreate objects. An example is:

DBMS_METADATA

DECLARECURSOR select_table ISSELECT table_name FROM user_tablesWHERE table_name = ‘TEMP’;temp1 VARCHAR2(30);temp2 VARCHAR2(4000);BEGINOPEN select_table;FETCH select_table INTO temp1;temp2 := SUBSTR(dbms_metadata.get_ddl(‘TABLE’, temp1), 1, 250);dbms_output.put_line(‘SQL: ‘ || temp2);END;/


Q & A

a trip to the alien world of

Documents

dbms packages

logmnr dbms

metadata dbms

file dbms

redefinition dbms

ddl dbms

xmlquery dbms

transform dbms