John Flack

The DBMS_UTILITY package has a nice utility for handling comma delimited lists. What if your list isn't delimited by commas? What if list items are longer than 240 bytes? What if you need other list handling capabilities? For instance, I needed to break apart lines of text at the linefeed characters (CHR(10)). So I wrote my own LIST package. See the comments in the code for more information. Note that this is newly updated as of 3/5/2003 with a procedure to sort a list.

HTTP_REQUEST (package in HTTP_REQUEST.pks and body in HTTP_REQUEST.pkb) is a package that can import a pair of NAME/VALUE arrays in the format that mod_plsql uses for dynamic parameter passing as I described in the article, "MVC Development in PL/SQL". Then it provides functions for accessing the values. HTTP_REQUEST would normally be created in a schema expressly designated for shared objects (mine is called COMMON) and granted to PUBLIC with a public synonym.

I also attached STATE_SUBMISSIONS_CSR, which is an example of what I call a "Cursor Package". This is explained pretty well in the comments. I've been writing cursor packages for some time, but this one uses HTTP_REQUEST for a much better way to do this, so it is a good example of what you can do with HTTP_REQUEST. The MAJOR benefit of this cursor package over other versions is that it uses bind variables rather than just adding to the text of the SELECT. This can give you better performance, and also prevents a form of security attack known as "SQL Injection". Google this term if you've never heard it before - anyone that allows queries of SQL databases from a web page should be aware of this.

This is an old retired procedure for running a host command or shell script from PL/SQL. The command runs on the database server, not on the user's workstation. Security is provided via the PRODUCT_USER_PROFILE table, in a manner similar to SQL*Plus. This has been specifically tailored for database servers running Unix and it's look-alikes, like Linux, but you could probably get it to work in Windows with a few minor changes. It is retired, because Oracle 9 and later include built-in capabilities that do many of the things that we used to do with this utility. I no longer have a database with this installed, but included it here as an example of external procedures.

This zip contains two files, orahost.c, orahost.sql. Orahost.c must be compiled on your database server using the "shared library" output option of your C compiler. The resulting object file will normally be named orahost.so, and must be readable and executable to the owner of your database, which is usually the user named "oracle". Read Oracle's documentation on external procedures for more information. Orahost.sql creates a LIBRARY object in the database to reference orahost.so - change the code to identify where this file exists on your server. Then it creates a PACKAGE and PACKAGE BODY with the PL/SQL stub code to call the external procedure. The public procedures include the security code. Read the SQL*Plus documentation on PRODUCT_USER_PROFILE to get an understanding of how entries in this table govern who may execute what.

This Java Stored Procedure was written by Tim Hall, the owner of Oracle-Base (http://www.oracle-base.com/) which I highly recommend as a source of Oracle information, scripts, utilities, papers and other assorted useful stuff. I did some major modifications to the PL/SQL wrapper that I think improves yet simplifies the security, basing it around Oracle Directory objects.

The package has procedures for reading a directory (folder) in the file system that hosts your Oracle database, and manipulating the files. It can move, copy, rename and delete files. This is documented more thoroughly in Outside In and Inside Out - Data to and from Outside the Oracle Database.

You will need to grant Java file IO privileges to the owner of this package. You could grant the JAVASYSPRIV role, but this could grant more privileges than you need or want. Use DBMS_JAVA.GRANT_PERMISSION to set java.io.FilePermission for more fine-grained access.

This is a package of procedures to send e-mail via the built-in utl_smtp package. It is based on code that Oracle included as sample code on Oracle Technology Network.

The original can be found at: http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html

The e_mail package presents a higher level API for just such simple tasks making it easy to...

• send a short plain text email with no attachments via a single call
• send a long email in plain text or HTML by...
  • addressing it and setting the message MIME-type
  • defining the message piecewise
• send an email with attachments by...
  • addressing it and setting the message MIME-type to MULTIPART
  • defining the message piecewise
  • adding character attachment(s) with defined MIME-type
  • adding binary attachment(s) with defined MIME-type and encoding scheme
  • stating whether each attachment is to be inline or not
• send an email in a multibyte character set (eg for Chinese, Japanes or Korean)

SQL*Loader is a great way to get data into your Oracle Database, but it is a command line tool that isn't very friendly. This VBScript utility for Windows starts an Internet Explorer session and displays a form for getting the Oracle connect string, control file, data file, and log file information from the user. Then it runs SQL*Loader. If any of the required parameters are on the command line to call the utility, it doesn't include them on the form. This version is very basic, but you can customise the HTML for the form to make this as fancy as you like.

Ever get frustrated with the fact that TO_NUMBER and TO_DATE cause an error if the character string is not in a valid format? I did, so I created this package with versions of these functions that have a third parameter for the user to provide a default value to be returned if the input string is not in a valid format for a number or date conversion. As a bonus, the package includes a TO_DURATION function that converts a string in 'HH24', 'HH24:MI', or 'HH24:MI:SS' format to a number of days, including fractions of a day.

Did you ever write a report with the Web PL/SQL toolkit, and then wish that you could save the output to a file or send it as an e-mail, instead of displaying it on the web? This is a solution to that problem, which will be explained in an upcoming paper.

This version has two procedures, one for running the report and sending the results in an e-mail, and the other for storing the report with UTL_FILE. Both take a character string for executing the report with EXECUTE IMMEDIATE. For security, this is AUTHID CURRENT_USER: you won't be able to run any report that you couldn't run from the web. E-Mail transmission requires that you install the E_Mail package that is also available under the heading Send E-Mail from PL/SQL above.

This package has a few utilities to make working with long text strings easier. It contains: A function to determine if there are any non-printable characters besides the whitespace characters, tab, linefeed and carriage return in a string. A function to change all non-printables to something else, a space by default. A function to change all newlines, which might be a linefeed (LF), a carriage return (CR), or a carriage return/linefeed combination (CRLF) to the Internet standard newline, which is a CRLF. A replacement for INITCAP that only changes strings that are not already in mixed case. This is a work in progress, and I may add new functions before I'm done.

These scripts produce a report that shows what is in a tablespace block by block. You can see how fragmented the objects are, where the contiguous space is, and where the free space is. The Oracle 7 version shows the individual extents even if the extents of the same object are next to one another. This can make for a rather long report, so I used some PL/SQL and the ability of Oracle8I to create a temporary table to make a more concise, rolled up version.

This package contains a set of functions for parsing names into their components, like first name and surname. It can take names in "first middle last, suffix" format or "last, first middle" format. This does rely on the normal American ordering of names, so it may be of less utility for international use. In fact, it has a few problems with some Hispanic names, since they often use two last names without hyphenating as is common in Anglo/American usage. Still, I've found it more useful than not.

This package calls the Google Maps web service to geocode locations. This version has one main function that takes an address and returns an SDO_GEOMETRY object containing the longitude and latitude. It can be extended to return other items such as a standardized address and a zip code. You must go to the Google Maps API website at http://www.google.com/apis/maps/ to get an API key in order to use this package. You should also read Google's terms and conditions.

The Web Toolkit packages included with the Oracle Web Application Server (or Web Server, or Application Server or whatever Oracle's name for this product is this week) provide a good interface with the PL/SQL Cartridge, but not perfect. This package include some routines that fix bugs in Oracle's toolkit. Others add functionality that I found myself coding again and again, until I generalized it. This isn't our entire local toolkit (allow us SOME trade secrets) but it does include all the functions that I mentioned in my presentation, PL/SQL Web Cartridge Tips and Techniques.