Appendix AA

Overview

In this section of the book, we will cover the supplied database packages that, in my opinion, everyone needs to be aware of. Each of these packages is documented in the Oracle document entitled Oracle8i Supplied PL/SQL Packages Reference. The supplied documentation typically shows the entry points (externalized procedures and functions) of the supplied package, and gives you an overview of each function/procedure's usage. In this section we will describe in more detail when you might (or might not) choose to use such a package. We will not go into depth on each and every procedure in every package. Rather, we will discuss the most commonly used package entry points, and show how they are used. For a comprehensive list of all procedures available in a given package, along with all possible parameters, I will refer you to the aforementioned document.

This appendix will serve as a 'jumping off' point for using these supplied packages. After you are done with it, you will have a good feel for the intended use of many of them. We do not cover every supplied package in this section. This does not imply that they are not useful, just that their use falls outside the scope of typical development. We will explore the packages that applications will employ in most cases.

The packages we will cover are:

  • DBMS_ALERT and DBMS_PIPE - Inter-process communication facilities in the database. DBMS_ALERT can be used to signal all interested sessions that some event has taken place. DBMS_PIPE allows two sessions to 'talk' to each other, much like a TCP/IP socket.

  • DBMS_APPLICATION_INFO - Allows an application to register useful information in the V$ tables. Extremely useful to monitor what your stored procedure is doing, and register other information.

  • DBMS_JAVA - A PL/SQL package useful for working with Java stored procedures.

  • DBMS_JOB - A database job scheduler. Used when you have that stored procedure you want to execute every night at 2am, or when you just want to run something in the background.

  • DBMS_LOB - For working with Large OBjects (LOBs) in the database.

  • DBMS_LOCK - To create your own user-defined locks, separate and distinct from Oracle's row row or table level locks.

  • DBMS_LOGMNR - To review and analyze the contents of your online redo log files

  • DBMS_OBFUSCATION_TOOLKIT - Provides data encryption in the database.

  • DBMS_OUTPUT - Provides simple screen I/O capabilities for PL/SQL in SQL*PLUS and SVRMGRL.

  • DBMS_PROFILER - A PL/SQL source code profiler built into the database.

  • DBMS_UTILITY - A 'hodge-podge' collection of useful procedures.

  • UTL_FILE - Provides text file I/O for PL/SQL. Allows PL/SQL to read and write text files on the server.

  • UTL_HTTP - Provides access to the HTTP (Hyper Text Transfer Protocol) protocol from within PL/SQL. Allows PL/SQL to 'grab' web pages.

  • UTL_RAW - Provides conversion between the RAW and VARCHAR2 types. Extremely useful when working with TCP/IP, BLOBs and BFILEs, and encryption.

  • UTL_SMTP - Provides access to the SMTP (Simple Mail Transfer Protocol) from within PL/SQL. Specifically, it allows you to send an e-mail from PL/SQL.

  • UTL_TCP -Provide TCP/IP socket abilities for PL/SQL. Allows PL/SQL to open a connection to any TCP/IP service.

Why Use the Supplied Packages?

The reasoning behind using the supplied packages is simple, it is much easier and more maintainable to develop using supplied functionality then it is to build your own. If Oracle supplies a package for doing something (for example, data encryption) it would not be productive to write your own. Often, I find people implementing functionality that they did not know already existed in the database, purely out of ignorance. Knowing what tools you have available to you will make your life much easier.

About The Supplied Packages

The supplied packages from Oracle all begin with either DBMS_ or UTL_. Historically, packages that were created by Server Technologies (the guys who write the database) begin with DBMS_. The UTL_ packages were derived from other sources. The UTL_HTTP package, for performing HTTP calls from PL/SQL (to retrieve web pages and such), is an example of such an external package. The Application Server Division at Oracle developed this package in order to support the concept of ICX (Inter-Cartridge eXchange) with OAS (the Oracle Application Server), which has now been replaced with iAS, (the internet Application Server). This naming difference does not mean anything to us, the developers, really - it is just interesting to note.

Most of these packages are stored in a compiled, wrapped format in the database. This wrapped format protects the code from snooping eyes. We can see the specification of the code but we cannot see the code itself. If you were to select the code of DBMS_OUTPUT PACKAGE BODY from the database itself, it might look like something like this:

tkyte@TKYTE816> select text   2  from all_source   3  where name = 'DBMS_OUTPUT'   4  and type = 'PACKAGE BODY'   5  and line < 10   6  order by line   7  /      TEXT ------------------------------------------      package body dbms_output wrapped 0 abcd abcd abcd abcd abcd abcd abcd      9 rows selected. 

Not very useful. What is very useful however, is if we select out the specification of the PACKAGE:

tkyte@TKYTE816> select text   2    from all_source   3   where name = 'DBMS_OUTPUT'   4     and type = 'PACKAGE'   5     and line < 26   6   order by line   7  /      TEXT --------------------------------------------------------------------------      package dbms_output as        ------------   --  OVERVIEW   --   --  These procedures accumulate information in a buffer (via "put" and   --  "put_line") so that it can be retrieved out later (via "get_line" or   --  "get_lines").  If this package is disabled then all   --  calls to this package are simply ignored.  This way, these routines   --  are only active when the client is one that is able to deal with the   --  information.  This is good for debugging, or SP's that want to want   --  to display messages or reports to sql*dba or plus (like 'describing   --  procedures', etc.).  The default buffer size is 20000 bytes.  The   --  minimum is 2000 and the maximum is 1,000,000.   -----------   --  EXAMPLE   --   --  A trigger might want to print out some debugging information.  To do   --  do this the trigger would do   --    dbms_output.put_line('I got here:'||:new.col||' is the new value');   --  If the client had enabled the dbms_output package then this put_line   --  would be buffered and the client could, after executing the statement   --  (presumably some insert, delete or update that caused the trigger to   --  fire) execute      25 rows selected. 

Hidden in the database is an online source of documentation. Each of these packages has a specification that has a nice overview of what the package is, what each function or procedure does, and how to use it. This is obviously very handy when you don't have the documentation, but is also useful even when you do, since the specification sometimes contains data that the documentation doesn't mention, or has further examples that are useful.

We will now look at the various packages I find useful in day-to-day work with Oracle. These are the packages, which not only I use frequently, but find others using as well. Additionally, we'll introduce some new packages, or ways to do things to work around some of the limitations of these built-in packages - limits people frequently hit, in my experience.



Expert One on One Oracle
Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
Year: 2005
Pages: 41
Authors: Richard Laermer, Michael Prichinello
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net