ExcelDocTypeUtils Update

At the suggestion of  a user (Paul Jensen … thanks for the sample code as well), I have updated the ExcelDocTypeUtils package making the COL_COUNT attribute of the T_WORKSHEET_DATA type optional.  This will allow users to introduce queries with varying numbers of columns dynamically, or use queries such as SELECT * FROM some_table.  In the previous version, the COL_COUNT attribute was mandatory.  I’ve also added a new function called getColHeaderString that will generate a delimited column name list for use with the COL_HEADER_LIST attribute of the T_WORKSHEET_DATA.  This could be used with a ‘SELECT * FROM (more...)

PL/SQL Key/Value Pair Utility

Get the code: keyValueUtils.pkg

KeyValueUtils is a simple (but very useful) Key/Value pair utility that allows you to define sets (collections) of KV pairs for use in PL/SQL applications.  The utility also provides the ability to access the KV set in a query using a pipeline function (and a table function).  This gives a developer the ability to create something similar to a NoSQL type of KV pair table (on the fly).

The utility consists of:

  • A  user defined type called T_KEY_VALUE, that contains two VARCHAR2 attributes key and value.
  • A collection of T_KEY_VALUE type called (more...)

Trinidad 2.0.0 PPR Bug with JSF 2.0 outputText Tag

I am currently in the process of porting all of my Oracle AS 10G R2 (OC4J) applications over to WebLogic  10.3.5.  After upgrading Oracle JDeveloper to version 11.1.2 and hand converting an old ADF 10.1.3 application to Trinidad, I ran into a curios issue.  None of my components that used PPR (Partial Page Rendering) would respond to events.  Most notably, pop-up dialogs triggered  by Command Buttons, or Command Links.  The dialogs would not pop-up, but I could see the events executing in the backing beans. After a couple of weeks (more...)

Automatically Grouping Query Results into Row Sets of a Specific Size

I came up with a simple formula a couple of days ago that will automatically determine which result set  a row belongs in if the results of the query need to be grouped in specific numeric sets of rows (such as sets of five rows, or sets of 10 rows, etc ..) for retrieval and display in an object such as a table widget on a web page.  These would be the sets of rows that are displayed by the ‘Next’, ‘Last’, and ‘Previous’ buttons (or arrows) on the widget.  The formula can  be applied directly in the (more...)

ExcelDocTypeUtils New Features: Worksheet Orientation and Repeating Column Headers

At the request of several folks, I’ve added two new features to the ExcelDocumentType and ExcelDocTypeUtils PL/SQL package:

1. The ability to specify whether or not a worksheet’s orientation is Portrait or Landscape.

2. The ability to specify whether or not column headers for a worksheet will repeat at the top of each printed page.

See the EmployeeReport demo files in the code bundle (or download the ExcelDocTypeUtils Developer Guide) for specifics.

(Download the code here:Code)

ExcelDocTypeUtils API Guide

I finally broke down and documented the ExcelDocTypeUtils PL/SQL API  in a 34 page document. Sorry about the long wait …

Here it is:  (opens in new window):ExcelDocTypeUtils API Guide

ExcelDocumentType: New Feature … Freeze Column Header Row

I  added a new feature to  the ExcelDocumentType object and ExcelDocTypeUtils package that will allow report developers to freeze the column header.  This means that the column header row will stay stationary (not scroll away) as the user scrolls through all of the data rows in the worksheet.  I have added an example of how to use this feature to the EmployeeReportDemo.sql file in the code bundle.

(Download the code here:Code)

New Open Source Offering: OWA Session Cache Utility

I have added another open source code/utility offering to the site. It’s called the OWA Session Cache Utility.

The OWA Session Cache utility is a custom add-on for MOD_PLSQL and DBMS_EPG DADs that provides the ability to maintain a stateful session in a Web PL/SQL Toolkit application. The utility has features similar to those found in Java Servlet based applications such as session cookie (holds session id), ability to set session timeout period, expired session management, the ability to store and retrieve session data using AJAX enabled JavaScript, and a garbage collector to clean up the session cache.

To find (more...)

Book Review: Oracle SQL Developer 2.1

Recently, I was given the opportunity to review a new  book published by PACKT Publishing entitled “Oracle SQL Developer 2.1“.    The book was written by Sue Harper, who works for Oracle Corporation and is a product manager for the SQL Developer tool.  I was actually excited to review the book.  I’ve been using Oracle’s SQL Developer tool on and off since it’s “Project Raptor” days.  I had yet to come across a definitive and all encompassing guide to using SQL Developer, until the UPS driver delivered the book into my hands a few (more...)

ExcelDocumentType: Create Hyperlinks between Worksheets

This post  introduces a new feature in the ExcelDocTypeUtils PL/SQL package that allows a developer to create hyper linked cell data that points from one worksheet to another.   The feature is implemented by a  new function called createWorksheetLink.  The function takes two parameters: the cell data, and the name of the target worksheet.  The function is called from the dynamic SQL statement that is passed as part of a worksheet data object (ExcelDocTypeUtils.T_WORKSHEET_DATA).

The following code sample demonstrates how this feature can be used to generate a summary worksheet containing a hyper linked list (more...)

PL/SQL Phases of the Moon

At work this past week, we kicked off a large Predictive Analytics and BI project.  The predictive analytics piece will use various factors and statistical models to try and pre-determine likely areas of criminal activity around our city.  Among those factors … the current and historical phase of the moon (based on date).  The Oracle RDBMS contains a plethora of date and calendar related functions, but determination of the moon’s phase is not among them.   Since I am the resident Oracle “guru”, the gauntlet was thrown down in front of me to come up with such (more...)

FusionCharts PL/SQL API Optimized for Performance

Thanks to Rune Langtind of the U.K. for optimizing the StreamDataSet procedure in the FusionFlashCharts PL/SQL package … the charts render 100% faster now.  Prior to his change, charts with large data sets took a very long time to render.

If you haven’t tried this API and you are a Fusion Charts user, give it a try (see the FusionCharts PL/SQL API link on the top menu or the right side menu).

Convert Oracle Date Format to Excel Date Format

MS Excel uses a numeric format, called Serial Date format, when storing and dealing with dates and times.   Serial date is calculated by taking a given date and determining the number of days that have passed since that date and 01-Jan-1900 (actually 00-Jan1900, but that’s a story for another time).  Why is this important to an Oracle developer?  If you are using a tool such as the ExcelDocumentType to generate an Excel document with PL/SQL and you are trying to apply an Excel style to an Oracle Date data type … it will not work.  The (more...)

Oracle APEX: Easily Integrate Custom Web PL/SQL Procedures and Packages

In this post, we look at a code authorization utility consisting of a schema, a table, and a PL/SQL package that allows developers to easily integrate their own custom web PL/SQL code into an Oracle APEX application.  The utility works in conjunction with APEX’s built-in security function, wwv_flow_epg_include_mod_local, that determines if a given procedure is authorized to be executed by the APEX PL/SQL DAD.  The security function resides in Oracle APEX home schema (in this case, APEX_030200), and must be customized to allow custom (external) procedures.  Most examples of customizing the security function demonstrate the use (more...)

Exposing Web Services as Database Views

In recent years, Web Services have become the defacto standard in passing  data between applications.   They make communication between disparate computer languages and architectures relatively simple.  For the most part, we tend to think of web services in terms of traditional application development (Java, .NET, Ruby, PHP, etc …) and inter-process communication.  What if we looked at them from another perspective?  What if we could easily integrate web services into the database?  Make them consumable by Business Intelligence tools, and Data Mining engines WITHOUT having to change the traditional manner in which these tools access (more...)

PL/SQL Web Service Utility: Accessing Web Services with PL/SQL is Simple!

This entry showcases a PL/SQL package called WebServiceUtils that makes accessing a web service from with the Oracle database very simple.  The package makes use of Tim Hall’s SOAP_API PL/SQL package (updated by me to get the SOAP response as a CLOB instead of a VARCHAR2) to do the SOAP encoding and processing.  The WebServiceUtils package puts a user friendly layer over the SOAP_API.    The WebServiceUtils package (here after known as the “utils” package) provides three structures (2 record types and 1 collection).  The two record types are:

  • service_definition_type – Contains attributes related to the service (more...)

PL/SQL: Create Dynamic PL/SQL Functions with the AnonymousFunction Data Type

(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, November 9, 2008)

Spending some time with loosely typed languages like JavaScript and LISP has made me realize how powerful anonymous functions can be.  In my last big project using ADF Faces, I found it necessary to rewrite some the ADF Faces JavaScript functions on the fly (at runtime) in order to force a specific and non-native behavior. Since JavaScript is loosely typed (meaning, in a nutshell, I don’t need to explicitly specify a type for my functions or variables) and supports anonymous functions (functions defined and executed at runtime), (more...)

JavaScript: The Utlimate RegExp Email Address Format Validator

(Originally posted on the “old” Jason Bennett’s Developer Corner, Thursday, September 18, 2008)

I developed the following email address format validator for my current ADF Faces project.  The QA person keep failed my feeble attempt at a validator, so I decided to go big guns and create a validator that conforms to the standards for email addresses as set forth in this wiki entry:http://en.wikipedia.org/wiki/E-mail_address .  I created a single Regular Expression to handle all cases (unless someone can break it).  Here is the function:

(Make sure you put the regexp in one long string if you cut and (more...)

ADF Faces: Passing a Java Collection from a Custom ViewObjectImpl Class to a PL/SQL Function

(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, August 17, 2008)

I recently had the challenge of creating several complex query search screens using ADF Faces.  These new search screens had to integrate seamlessly into our current application’s (not a J2EE application …) search screens (look the same, act the same, feel the same …).  One of the challenges that presented itself was to determine how to pass multiple search parameters with various operators (=, <, LIKE,SOUNDEX, etc …) and the values associated with them and map them to their associated columns in a dynamic where (more...)

The Oracle Report Bean

(Originally posted on the “old” Jason Bennett’s Developer Corner, Sunday, June 15, 2008)

The Oracle Report Bean is a cool little bit of code I developed this week that will let you to execute an Oracle Report from within your JEE or plain old Java application.  The bean allows you to configure all of the report execution parameters and contains methods to execute the report as a printed report or have the report streamed back to the client if the chosen format (PDF, RTF, HTML, XML, etc).  The code basically constructs the URL you need to access the (more...)