Oracle added a "procedural" component, otherwise know as PL/SQL, to the Oracle Relational Database Management System a long time ago. With a programming language in the database engine, application developers can work with database objects in their native form with significant improvements in efficiency and scalability.

I can not claim to know everything there is to know about PL/SQL and significant changes happen from version to version that make it even more difficult to keep up. This web log is my attempt to document some of the things I have learned over the years and to report on "new" (at least to me) discoveries as I come across them.

Feedback and suggestions are always appreciated. E-mail me at JohnJeunnette at PrairieSystemsGroup dot com.


UTL_FILE and BLOB data (No comments yet. Click the title to add one.)
Keywords: BLOB , UTL_FILE
I have been moving documents out of the database with a Java procedure for quite awhile but discovered today that the Oracle 11 Express Edition does not include a JVM (Java Virtual Machine). So, after a bit of research I discovered that the UTL_FILE package can handle binary data. (Actually, the feature has been around since version 9i...). Some example code: DECLARE -- this_file utl_file.file_type; l_buffer raw(32767); l_amount binary_integer := 32767; l_pos number := 1; l_blob blob; l_blob_len number; out_path (set the path to a directory in the utl_file_dir initialization parameter) out_name varchar2(200) := null; -- BEGIN -- for document_record in ( select blob_content from ) loop out_name := (build a meaningful file name) -- l_blob_len := dbms_lob.getlength(document_record.blob_content); -- this_file := utl_file.fopen( out_path, out_name,'wb',32767); -- while l_pos < l_blob_len loop, l_amount, l_pos, l_buffer); utl_file.put_raw(l_file, l_buffer, true); l_pos := l_pos + l_amount; end loop; -- utl_file.fclose(l_file); -- end loop; -- END;

ODTUG Tool Survey (3 comments. Click the title to review or add one.)
Keywords: Oracle Tools , ODTUG

We have assisted ODTUG with an (almost) annual survey of developers for several years.

The latest and greatest analysis of the 2010 survey is available now: ODTUG_Tool_Trends_article.pdf.

Comments and/or suggestions are appreciated.


The PL/SQL Web Toolkit (No comments yet. Click the title to add one.)
Keywords: PL/SQL Web Toolkit
It is only appropriate that I start this web log with some comments on the PL/SQL Web Toolkit. I have been using the toolkit for something like ten years now to create a number of database-based web applications. I still marvel at the simplicity and power of the concept of letting a browser-based HTML page communicate directly with the database, and vice-versa.

Some site examples include this web site content management application (Max), a technical conference abstract and registration processing application (CAAT at, a resource reservation tool (RRT at, and even a recipe storage site (ORB at

Operationally, the PL/SQL Web Toolkit is an Apache web server "mod", called mod_plsql, that takes HTTP requests from the browser and hands them off to a procedure in the database. The database procedure generates HTML and sends it back to the mod_plsql which returns the HTTP response to the browser. I'll expand on the use and abuse of the PL/SQL Web Toolkit in future columns.

Another area we will focus on is the collection of system packages that come with the database. PL/SQL code is the mechanism to operate the various options so we will spend some time on how to use the system packages.

Stay tuned for occasional thoughts.


Prairie Systems Group, Limited | Denver, Colorado |
Copyright 2009, Prairie Systems Group, Limited