ORA-00001: Unique constraint violated

NTLM for PL/SQL

2011-08-03T06:00:00.000-07:00

NTLM, or more properly NTLMSSP is a protocol used on Microsoft Windows system as part of the so-called Integrated Windows Authentication.

Integrated Windows Authentication is also known as HTTP Negotiate authentication, NT Authentication, NTLM Authentication, Domain authentication, Windows Integrated Authentication, Windows NT Challenge/Response authentication, or simply Windows Authentication.

In Microsoft Internet Information Server (IIS), the system administrator can protect a website or folder with "Integrated Windows Authentication". When you browse to this website or folder, you must enter your Windows (domain) username and password to get access (although Internet Explorer will, depending on your security settings, send your credentials automatically without showing a login dialog box). Note that unlike Basic Authentication, which sends the password as plaintext to the web server, the NTLM protocol does not send the password but rather performs a cryptographic "handshake" with the server to establish your identity.

Use of Integrated Windows Authentication via NTLM on IIS is very common inside many companies (ie on intranets and internal web servers), where both the client and web server computers are part of the same, or trusting, domains.

Using NTLM from PL/SQL with UTL_HTTP

Unfortunately, from the PL/SQL developer's perspective, Oracle's UTL_HTTP package does not support NTLM authentication (it only supports Basic authentication via the SET_AUTHENTICATION procedure).

So, if you wanted to retrieve information from your intranet or call a web service (protected by Integrated Windows Authentication) from the database via PL/SQL and UTL_HTTP, you were out of luck.

Until now, that is... :-)

A pure PL/SQL implementation of the NTLM protocol

I came across a Python implementation of the NTLM protocol, and I decided that it should be possible to port this code to PL/SQL. Assisted by a couple of good friends and colleagues, and after a lot of bit-fiddling, reverse-engineering, study of protocol specifications, and liberal use of network packet sniffers, we got it working!

A pure PL/SQL implementation of the NTLM protocol is now available and included in the Alexandria Utility Library for PL/SQL.

The code is organized into two packages: NTLM_UTIL_PKG, which contains protocol-specific functions, and NTLM_HTTP_PKG, which is the package you actually use to make HTTP callouts, and which handles the initial NTLM "handshaking" with the web server.

Example 1: Simple request

This code simply grabs the page you direct it towards, and returns the contents as a CLOB. (What is really going on behind the scenes is a series of requests and responses to establish the authenticated connection, before the actual URL contents is served.)

declare
l_clob clob;
begin
debug_pkg.debug_on;
l_clob := ntlm_http_pkg.get_response_clob('http://servername/page', 'domain\username', 'password');
debug_pkg.print(substr(l_clob, 1, 32000));
end;

Example 2: Web service call

Here, a (persistent) connection is explicitly established before making one or more requests to the server. Note the returnvalue from the BEGIN_REQUEST function, which is the authorization string which must be passed along in the "Authorization" HTTP header on any subsequent requests. The connection is then is closed. Note that NTLM is a connection-based protocol, and will not work without the use of persistent connections.

declare
l_url varchar2(2000) := 'http://servername/page';
l_ntlm_auth_str varchar2(2000);
l_xml xmltype;
l_soap_env clob := 'your_soap_envelope_here';

begin
debug_pkg.debug_on;

-- perform the initial request to set up a persistent, authenticated connection
l_ntlm_auth_str := ntlm_http_pkg.begin_request (l_url, 'domain\username', 'password');

-- pass authorization header to next call(s)
apex_web_service.g_request_headers(1).name := 'Authorization';
apex_web_service.g_request_headers(1).value := l_ntlm_auth_str;

-- perform the actual call
-- NOTE: for this to work, you must be using a version of apex_web_service that allows persistent connections (fixed in Apex 4.1 ???)
-- see http://jastraub.blogspot.com/2008/06/flexible-web-service-api.html?showComment=1310198286769#c8685039598916415836
l_xml := apex_web_service.make_request(l_url, 'soap_action_name_here', '1.1', l_soap_env);

-- or use the latest version of flex_ws_api
-- flex_ws_api.g_request_headers(1).name := 'Authorization';
-- flex_ws_api.g_request_headers(1).value := l_ntlm_auth_str;
-- l_xml := flex_ws_api.make_request(l_url, 'soap_action_name_here', '1.1', l_soap_env);

-- this will close the persistent connection
ntlm_http_pkg.end_request;

debug_pkg.print('XML response from webservice', l_xml);
end;

Remarks

Tested successfully on Oracle 10g XE (with AL32UTF8 character set) and Oracle 10g EE (with WE8MSWIN1252 character set).
Tested successfully against IIS 6.0 with non-SSL "plain" website and SSL-enabled Sharepoint website (both set up with Integrated Windows Authentication, obviously).
The current version ignores cookies when setting up the connection. If you depend on cookies being present, you may have to deal with this specifically.

Given the diverse nature of network configuration, there may be bugs or unhandled cases in the code. So please test the code in your environment and leave a comment below, letting me me know if it works for you or not.

New version of Alexandria Utility Library for PL/SQL

2011-08-03T05:47:00.000-07:00

I've just uploaded a new version of the Alexandria Utility Library for PL/SQL.

Updates include both small bug fixes and some major new features (which I'll return to in another post).

Among the improvements are:

Additional functions in OOXML_UTIL_PKG for working with Excel 2007 and Powerpoint 2007 files.
Kris Scorup has contributed improved CSV parsing to the CSV_UTIL_PKG. It now handles double quotes and separator characters inside strings.
Anton Scheffer's packages for building PDF and XLSX files have been included in the library.
The PL_FPDF library by Pierre-Gilles Levallois is a port of the FPDF library for PHP. Pierre-Gilles Levallois has his own website, but his package (which is open source under the GNU license) does not appear to have been updated for several years, and several indivuals have been making their own fixes and enhancements to this package. Rob Duke and Brian McGinity have both contributed bug fixes and enhancements (such as internal document links, Javascript support, using clobs to work around the 32k limit, etc.). These changes as well as some of my own have been merged and included in the Alexandria library as PDFGEN_PKG. You'll find this package in the "extras" folder (it is not installed by default if you run the install script).

Count lines in multiple files using Windows command prompt

2011-07-29T10:45:00.000-07:00

Not really Oracle-related, but I'm posting this as a reminder to myself and possibly useful to others.

To count the number of lines in a given set of files using the Windows command prompt, do the following:

for %G in (*.sql) do find /c /v "_+_" %G

This invokes the "find" command once for each file, counting the lines that do NOT contain the string "_+_" (the string has no special significance, any weird string that would not occur "naturally" in the files can be used).

There are probably more sophisticated ways of doing this, perhaps using PowerShell and whatnot. Leave a comment if you know of other methods (something that adds together all the file line counts into one grand total would be even better).

Mythbusters: Stored Procedures Edition

2011-07-29T05:09:00.000-07:00

These days, the use of database stored procedures is regarded by many as a bad practice.

Those that dislike stored procedures tend to regard them as incompatible with the three-tier architecture:

By breaking up an application into tiers, developers only have to modify or add a specific layer, rather than have to rewrite the entire application over. There should be a presentation tier, a business or data access tier, and a data tier.

This is illustrated as follows:

Note that the "tiers" in the figure should actually be labelled "layers", for as the accompanying Wikipedia article says:

The concepts of layer and tier are often used interchangeably. However, one fairly common point of view is that there is indeed a difference, and that a layer is a logical structuring mechanism for the elements that make up the software solution, while a tier is a physical structuring mechanism for the system infrastructure.

In fact, those that argue that stored procedures are bad tend to equate the three logical layers with three physical tiers:

Data layer = data tier (database)
Logic layer = middle tier (application server)
Presentation layer = presentation tier

But if we accept the above definition of "layers" and "tiers", it is obvious that the following is a valid mapping as well:

Data layer = data tier (database)
Logic layer = data tier (database)
Presentation layer = presentation tier

In other words, the database becomes our "logic layer" through the use of database stored procedures, which, as the name implies, are physically stored (and executed) in the database. (And although I use the term "stored procedure", I'm primarily talking about Oracle and PL/SQL, where the PL/SQL code should be put in packages rather than stand-alone procedures.)

But why is this a bad idea? In fact, as it turns out, it might not be a bad idea at all. The usual reasons given against the use of stored procedures for "business logic" (or for anything at all, really) tend to be myths (or outright lies), repeated so many times that they are taken as the truth.

So let's bust these myths, once and for all. And whenever someone argues against stored procedures using one of these myths, just give them a link to this blog post. (And leave comments to prove me wrong, if you will.)

Myth #1: Stored procedures can't be version controlled

Stored procedure code lives in text files, which can be version controlled like any other piece of code or document. Storing/compiling the code in the database is just like (re-)deploying any other code.

Claiming that stored procedures cannot be version controlled (because they are in the database) is like saying your application source code (Java, C# or whatever) cannot be version controlled because it is compiled and deployed to an application server.

Myth #2: Managing the impact of changes in the database is hard

Databases such as Oracle have built-in fine-grained dependency tracking.

A wealth of information about your code is exposed via data dictionary views.

Myth #3: Database tools lack modern IDE features

There are a number of free and commercial PL/SQL code editors and IDEs, and all have various levels of syntax highlighting, code insight and refactoring support.

Myth #4: Stored procedures always result in spaghetti code

To this, I can only say that bad programmers can make pasta in any language (the above is a visual representation of a Java or .NET enterprise framework "several dozen megabytes chock full of helper classes like IEnterpriseAuthenticationProviderFactoryManagementFactory").

And a good programmer can create "beautiful" code in COBOL, Visual Basic, PHP... and any stored procedure language, for that matter.

Myth #5: Code in the database can’t be properly encapsulated and reused, you need an object-oriented language for that

PL/SQL packages, views, pipelined functions and Ref Cursors offer encapsulation and reuse. And PL/SQL has object-oriented features, too.

Myth #6: Stored procedure languages are primitive, they lack basic features such as exception handling and dynamic execution

PL/SQL has had proper exception handling from the start, over 20 years ago (although exception handling was only introduced to SQL Server in 2005).

DBMS_SQL, EXECUTE IMMEDIATE and "weak" Ref Cursors enable dynamic execution of code. Parameter overloading and the ANYDATA and ANYTYPE types allow for generic code to be written.

Myth #7: Debugging stored procedures is hard/impossible

Both Oracle and SQL Server have built-in debugging capabilities, exposed via graphical user interfaces in the common IDEs, with full support for stepping though code, inspecting values, etc.

Myth #8: Stored procedures can't be unit tested

There are a number of free and commercial unit testing frameworks available for PL/SQL. Steven Feuerstein, one of the world's leading experts on the Oracle PL/SQL language, has been preaching the importance of unit testing in the database for years, and has developed several of the available unit testing frameworks.

Myth #9: Stored procedures are not portable, and tie you to one platform

This is the "vendor lock-in" argument. But the fact is that PL/SQL runs on multiple databases.

Such as DB2:

"IBM DB2 9.7 for Linux, UNIX, and Windows has out-of-the-box support for Oracle's SQL and PL/SQL dialects. This allows many applications written against Oracle to execute against DB2 virtually unchanged."

And Postgres (EnterpriseDB):

"Postgres Plus Advanced Server implements a comprehensive suite of Oracle-compatible functionality within and around the core PostgreSQL engine, including: (...) Oracle SQL syntax and semantics, Functions and Packages, PL/SQL (extensive support)"

Add to this the fact that the Oracle database runs on more operating systems than any other database, which means that your PL/SQL code will seamlessly transfer from Windows to Unix to Linux-based systems.

So PL/SQL-based code can actually be said to be more portable than, for example, .NET code (despite the existence of Mono). There are very few truly portable technologies; even Java is "write once, debug everywhere".

Myth #10: It's stupid/dangerous to put business logic in the database

This claim is usually made without any specific reason as to why it is stupid or dangerous. It usually "just is", because it is "against best practice" and "everybody else is putting the business logic in the middle tier". Sometimes it is claimed that putting logic in the database "mixes concerns", which must be a bad thing.

The problem with "business logic" is that nobody has a clear definition of what it is (but "you'll know it when you see it"). For example, where do you draw the line between "data logic" and "business logic"? Primary keys, foreign key constraints, unique key constraints, not null constraints, check constraints -- are these "data logic" or "business logic"? "Discount must be between 0% and 5%", is that a business rule or a data constraint, and/or is it a validation rule in the presentation layer?

The fact is, if you move ALL your logic into stored procedures, you entirely avoid the "mixing of concerns" between the data tier and the logic tier. (And if you think such an approach dooms your project to failure, consider the next myth, which features an example of a massive [and wildly successful] application written entirely in the database.)

Oh, and by the way, if your business logic is somewhere else than in the database, you always run the risk of someone or something bypassing your middle tier (for example by logging in with SQL*Plus), directly updating the database and possibly corrupting the data.

So let's turn this around and conclude instead that:

"If your business logic is not in the database, it is only a recommendation."

Myth #11: Stored procedures can't scale

A frequent argument against stored procedures is that by placing all the work in the database server, your solution won't be able to scale up, because you need "application servers" in the middle tier to do that. The scalability of the database is limited by the fact that you can only have a single database server (or you need to rewrite your code to work with partitioned/sharded databases like Facebook have done).

Of course, a lot of the people who throw around this kind of argument have never worked on an application or website which needed to scale up to millions of users (and to be clear, neither have I). That's because the vast majority of us work on much smaller enterprise business systems or "normal" websites (perhaps even the kind of website that can be well served with free database software on a server with less juice than your laptop).

But stored procedures CAN scale. It's only a matter of money. And if you have millions of users, you should be able to afford decent hardware.

Let's use Oracle Application Express (Apex) as an example of a big and complex PL/SQL application:

"[Application Express] lives completely within your Oracle database. It is comprised of nothing more than data in tables and large amounts of PL/SQL code. The essence of Oracle Application Express is approximately 425 tables and 230 PL/SQL packages containing 425,000+ lines of code."

This PL/SQL application can be deployed anywhere from your laptop to any kind of server:

The biggest and fastest server you can buy is currently Oracle Exadata.

"An 8 rack configuration has a raw disk capacity of 3,360 TB and 1,680 CPU cores for SQL processing. Larger configurations can be built with additional InfiniBand switches."

Oracle makes bold claims about this machine:

"Oracle claims that (..) two Exadata database systems would be able to handle Facebook’s entire computing load."

It's hard for me to verify that claim, not being associated with neither Oracle nor Facebook, but let's assume it has at least some truth to it.

So what about running our "stored procedure" application on Exadata?

"Does APEX Work on Exadata?

"Yep, Exadata is just Oracle. Let me say that again: It’s just Oracle. Exadata runs an 11.2 Database on Linux x64. It’s the exact same binary install if you download those binaries for “generic” Linux x64 11.2 from OTN. So, if your code / app runs on 11.2, it runs on Exadata. (..) The APEX Dev Team (my old friends and colleagues) did absolutely nothing to port their code to Exadata. I've run a number of customer benchmarks with customer's data and queries and have yet to make a single change to their queries or structures to make them work on Exadata."

So... without changing a single of those 425,000 lines of code, this "stored procedure" application can run on my old laptop (I've even tried it on an Asus EEE netbook), or it can run with 1,680 CPU cores. Without offloading any logic to an application server.

I'd say that's pretty scalable.

Mobile device support in Apex 4.1

2011-05-24T01:54:00.000-07:00

The current Apex Statement of Direction for Apex 4.1 states that it will "include themes and HTML templates suitable for smart phones and mobile devices".

If you are wondering what that means, then check out this thread on the Apex OTN Forum where Marc Sewtz, one of the developers on the Apex team, provides more details about this new feature.

Interestingly, some of these "mobile-enabling" features are also relevant for regular applications, such as the ability to render a form without a table grid, enhanced label templates, and dynamic (SQL-based) lists.

Just give me a hash table and a shitload of RAM

2011-04-14T22:39:00.000-07:00

In this interview, James Gosling, the "father of Java", says:

I’ve never got it when it comes to SQL databases. It’s like, why? Just give me a hash table and a shitload of RAM, and I’m happy. And then you do something to deal with failures.

Right... just "do something to deal with failures". And maybe add some other useful stuff. Shouldn't take too long to implement and make sure it works as intended.

But Mr. Gosling, could you please give me a cost estimate and a delivery date for the data-centric business application I want you to build for me?

Amazon S3 API for PL/SQL

2011-03-28T10:48:00.000-07:00

Amazon S3 is part of Amazon's Web Service offering and the name is an abbreviation for Simple Storage Service:

"Amazon S3 provides a simple web services interface that can be used to store and retrieve any amount of data, at any time, from anywhere on the web. It gives any developer access to the same highly scalable, reliable, secure, fast, inexpensive infrastructure that Amazon uses to run its own global network of web sites. The service aims to maximize benefits of scale and to pass those benefits on to developers."

A few months ago, Jason Straub published an Oracle whitepaper on how to integrate Oracle Application Express (Apex) with Amazon S3.

As Jason points out, Amazon has a Free Usage Tier which allows you to get started using Amazon S3 for free. If you have ever bought a book from Amazon, they already have your credit card on file, so signing up for Amazon Web Services is quick and easy (and they won't start charging your credit card until the free trial period is over).

Introducing the S3 API for PL/SQL

Inspired by Jason's whitepaper, I decided to write a stand-alone PL/SQL API for Amazon S3. This API can be used in any PL/SQL solution, with or without Apex.

The API supports all common S3 operations, including

Authentication
Creating new buckets
Listing existing buckets
Listing existing objects (with or without filtering)
Creating (uploading) new objects (and setting an Access Control List - ACL)
Generating download links (with or without expiry dates)
Downloading objects
Deleting objects

See the examples below for more details.

Use Cases

So what can you do with Amazon's S3 service in combination with a PL/SQL API?

I can think of several interesting use cases, some of which I might explore further in future posts:

Backing up your database (use DBMS_DATAPUMP to dump a file to disk, then compress it using ZIP_UTIL_PKG, then encrypt it using CRYPTO_UTIL_PKG, and upload it to S3)
Backing up your PL/SQL source code (use data dictionary views or DBMS_METADATA to extract the source code, optionally zip and/or encrypt it, and upload to S3)
Backing up your Apex applications (use WWV_FLOW_UTILITIES.EXPORT_APPLICATION_TO_CLOB to generate export file, optionally zip and/or encrypt it, and upload to S3)
Cloud storage for file uploads (instead of storing [large] files inside your database, store them in the cloud and download them on demand -- especially relevant for Oracle XE which has a file size limit)
Serve static content (generate static [text, CSV, HTML, PDF] files from the database and upload to S3)
Replication or shared storage (upload from one machine/database, download to another)
Data loading or message processing (set up to poll for new incoming files - uploaded by other S3 clients - and process them)

Remember that all these things can be scheduled to run in the database using DBMS_JOB or DBMS_SCHEDULER.

Where to get the Amazon S3 API for PL/SQL

You can download the API as part of the Alexandria Utility Library for PL/SQL.

Getting started

Download and compile the relevant PL/SQL API packages. Then register with Amazon for an S3 account and get your AWS keys (key and secret key), and login to the AWS Management Console to get familiar with the basic operations.

If you are unfamiliar with Amazon S3, I recommend that you read this short getting started guide that describes the common operations.

In the following examples we shall see how you can do the same operations using PL/SQL.

Authentication

From your Amazon account control panel, you'll get the key strings you need to use the Amazon web services.

Before you call any of the following API methods, you must initialize the authentication package. You only have to do this once per database session (but remember, on the web, every page view is a separate database session, so in Apex you'll need to run this code for every page, typically as a Before Header page process).

Creating new buckets

Buckets are what you use to organize your objects (files) in Amazon S3. Think of them as top-level folders, but note that you cannot create more than 100 buckets in a single account, and the bucket name must be unique across all user accounts on Amazon S3. So creating buckets is not really something you'd do very often, and usually it will be done manually (to resolve any name conflicts with existing buckets).

A bucket is associated with a specific region where your objects will be stored. For reasons of latency/speed and possibly legal issues, it makes sense to select a region that's close to you and your users (although you may actually want to locate it far away if the purpose is backup for a major disaster in your own area).

Here's how to create a new bucket via PL/SQL code:

Checking the AWS management console to verify that the bucket has indeed been created (in the specified region):

Listing existing buckets

With one or more buckets created in your account, you can list the bucket names.

There are two way to do this, either by retrieving an index-by PL/SQL table using the GET_BUCKET_LIST function:

or, alternatively, via SQL using a pipelined function named GET_BUCKET_TAB:

Creating (uploading) new objects

An "object" is a file, and this is really what the S3 service is all about, storing files. So let's upload a file or two to our new bucket!

The API lets you upload any BLOB data to S3 using the NEW_OBJECT procedure.

When you upload a file to S3, the default Access Control List (ACL) makes sure that only the owner of the file (you!) can access (download) it.

Others get an "Access Denied" message (but see the "Generating download links" section for how to generate special time-limited download links):

There are a number of predefined ACLs that you can specify if, for example, you want to make the file publicly available.

Which can then be freely downloaded by anyone (the use of HTTPS is optional).

A note about "folders": S3 has no concept of "folders", but you can simulate folders by using a forward slash in your file names (as seen in the previous example). Some S3 clients, such as the AWS management console, will present such files in a folder structure. As far as the PL/SQL API is concerned, the slash is simply part of the file name and has no special meaning.

Listing existing objects

Now that we have uploaded a couple of files, we can list the contents of the bucket via the GET_OBJECT_LIST function:

You can also get a list in SQL via a pipelined function named GET_OBJECT_TAB:

In both cases, you can optionally specify a prefix that acts as a search filter for the file names you want to return, and/or the maximum number of items you want to return.

Generating download links

You can access a file that has been protected by an ACL by including a special checksum parameter in the URL.

The GET_DOWNLOAD_URL function lets you generate the URL needed to access the file. You can specify when the link should expire, so this means you can share a download link that will stop working after a specified amount of time, which can obviously be useful in a number of scenarios.

Pasting the generated URL into the browser allows us to access the file:

Downloading objects

Downloading a file from S3 using PL/SQL is straightforward with a call to the GET_OBJECT function which returns a BLOB:

Deleting objects

Removing a file is likewise very simple, just call the DELETE_OBJECT procedure:

Summary

The ability to upload and download any file from the Oracle database to "the cloud" (and vice versa) via PL/SQL is extremely useful for a number of purposes.

Let me know if you find this API useful!

How I hacked the Apex 4 Websheets

2011-03-14T01:17:00.000-07:00

Or, to be more specific (and less sensationalistic), how I struggled to, and finally succeeded in, using the new Apex 4 Websheets feature in a "Runtime-Only" environment.

What is an Apex runtime environment?

"Oracle recommends that you run any sensitive production Oracle Application Express applications with a runtime installation of Oracle Application Express. A runtime installation does not expose the Web-based application development environment, thus preventing the use of Application Builder, SQL Workshop, and related utilities on a production installation. Additionally, a runtime environment only includes the Oracle Application Express database objects and privileges necessary to run applications, making it a more hardened environment."

So, naturally, I set up Apex production environments using the runtime-only installation. But I hit a problem when I tried to deploy a Websheet application to such a production environment.

The setup

Here is what I did:

Installed Oracle XE
Installed Apex 4 runtime (apxrtins.sql)
Manually created an application schema (SMALL_APPS) in the database
Manually created the Websheet tables (APEX$ tables) in the SMALL_APPS schema (to do this I had to manually export the table scripts from the Apex development instance using TOAD, but I guess it could also have been done through the SQL Workshop in Apex itself)
Manually created an Apex workspace for the SMALL_APPS schema (via the APEX_INSTANCE_ADMIN package)
Exported the Workspace definition from the development instance. The generated workspace script contains the statements to create the workspace users. I removed the part of the script that creates the workspace itself (as I had already done this in the preceding step), and changed the workspace ID to the newly created workspace ID before I ran the script.

So far, so good.

The problem

I then proceeded to import the actual Websheet application.

This, however, threw up the following error:

WEBSHEET APPLICATION 112 - WebSheetSandbox
Set Credentials...
Check Compatibility...
API Last Extended:20100513
Your Current Version:20100513
This import is compatible with version: 20100513
COMPATIBLE (You should be able to run this import without issues.)

Set Application ID...
...Remove Websheet Application
...Create Websheet Application
...Create Access Control List
...Create Application Authentication Set Up
...Create Data Grid
Rollback

Error starting at line 163 in command:
declare
  q varchar2(32767) := null;
begin
q := null;
wwv_flow_api.create_ws_worksheet (
  p_id => 1311502709921962+wwv_flow_api.g_id_offset,
  p_flow_id => 4900,
  p_page_id => 2,

(snip...)

ORA-02291: integrity constraint (APEX_040000.WWV_FLOW_WORKSHEETS_FLOW_FK) violated - parent key not found
ORA-06512: at "APEX_040000.WWV_FLOW_API", line 14562
ORA-06512: at line 5
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.

From this it was evident that the data grids in the exported application are linked to application 4900, which is the built-in Websheets application. However, the Apex runtime-only installation does not install application 4900, hence the integrity error.

At this point I started to wonder if Websheets are supported in a runtime-only installation of Apex, and I posted the question in the Apex discussion forum on OTN.

But the only answer there was silence, so after a few days I decided to just go ahead and try to install application 4900 in the runtime environment, by running the f4900.sql script (from the apex\builder folder).

More problems

With application 4900 installed successfully, I was able to install my own Websheets application.

However, after login I was greeted with the following error message:

ORA-06550: line 9, column 46: PLS-00201: identifier 'WWV_FLOW_F4000_PLUGINS.RENDER_SEARCHBOX' must be declared ORA-06550: line 9, column 1: PL/SQL: Statement ignored

So, a package is missing. I located the package scripts (in the apex\core folder).

The comments in the package header (wwv_flow_f4000_plugins.sql) actually state:

"RUNTIME DEPLOYMENT: YES"

But evidently the package is not installed by the runtime installation script, so either this is a bug or the comment is wrong.

So I added the missing package by running:

alter session set current_schema = APEX_040000;

@wwv_flow_f4000_plugins.sql
@wwv_flow_f4000_plugins.plb

The final problem

This fixed the PLS-00201 error, but now I got several other errors, all similar to the following:

Unable to bind ":WS_APP_ID"
Unable to bind ":WEBPAGE_ID"
ORA-20001: run_query error q=select id, title, content, section_type, data_grid_id, report_id, data_section_style, nav_start_webpage_id, nav_max_level, nav_include_link, created_by from apex$_ws_webpg_sections where ws_app_id = :WS_APP_ID and webpage_id = :WEBPAGE_ID order by display_sequence
ORA-01003: no statement parsed

Still no feedback in the discussion forum, so I decided to dig deeper into the Apex internals (an interesting exercise in itself).

A solution!

In the end, the solution turned out to be simple. The WWV_FLOW_COMPANY_SCHEMAS table contains the workspace to schema mappings. This table contains a column called IS_APEX$_SCHEMA, and this needs to be set to "Y" (the APEX_INSTANCE_ADMIN.ADD_WORKSPACE procedure leaves the column value as NULL).

So just update the column to enable Websheets:

update wwv_flow_company_schemas
set is_apex$_schema = 'Y'
where schema = 'SMALL_APPS';

Voila! I now have a working Websheet application in my runtime-only Apex environment.

Postscript

While I was typing up this for the blog post, I stumbled across the following statement in the Apex Administration Guide:

"Tip: Websheets are not supported in an Oracle Application Express runtime environment."

I wish somebody could have pointed that out to me in the discussion forum thread. But then again, if they did, I probably wouldn't have discovered how to make it work anyway.

And if anyone from Oracle is reading this, consider this an enhancement request for Apex 4.1: Support Websheets in a runtime-only environment by:

Including application 4900 in the runtime installation
Including the wwv_flow_f4000_plugins package in the runtime installation
Add a parameter to the ADD_WORKSPACE and ADD_SCHEMA procedures to specify whether websheets should be enabled or not

Stress Testing Oracle XE 10g

2011-03-01T00:48:00.000-08:00

Oracle Express Edition (XE) is Oracle's free entry-level database product, currently available only in a 10g version. XE is usually pitched as suitable for personal work or (very) small departmental applications, but I was curious as to what kind of load it can support.

Oracle XE 10g has the following limitations:

Up to 1 instance per server
Up to 1 CPU (will not use more even if available)
Up to 1 GB RAM (will not use more even if available)
Up to 4 GB datafiles (not including XE system data)
Free to develop, distribute and deploy in production

By the way, several things seem to indicate that Oracle Express Edition (XE) 11g is just around the corner. It's rumored that XE 11g will raise the datafile limit from 4 GB to 11 GB, but the other limits will remain as far as I know (and I don't really know anything about it...!). So these performance tests will probably be representative both for XE 10g and 11g, although I can't know for sure until 11g is available.

Here is the test environment I set up:

Hardware

Note that the "hardware" in this case actually runs virtualized in a VMWare environment.
Here are the resources allocated to the server:

1 Intel Xeon X7350 2,9 GHz CPU
4 GB RAM (but remember that XE won't use more than 1 GB anyway)
30 GB disk space

Software

The software setup was as follows:

Windows Server 2003 R2 Standard Edition Service Pack 2
Microsoft Internet Information Server (IIS) 6.0
Thoth Gateway
Oracle Express Edition (XE) 10g
Oracle Application Express (Apex) 4.0

Note that I am not using the Embedded PL/SQL Gateway (DBMS_EPG) as the web server, but rather IIS in combination with the Thoth Gateway, a free ASP.NET replacement for mod_plsql and DBMS_EPG that I wrote in C# using ODP.NET.

Everything (database, web server, Apex) was installed using default settings.
The Apex images folder (/i/) was set up with "Expires"-headers (7 days) to allow browsers to cache images.

Both the database and the web server run on the same machine (server), in order to keep the setup as simple as possible (and show what is possible with just one box).

Test page

I set up an Apex test page with a mix of regions, including a report region, a couple of PL/SQL regions and an HTML region. For each page view, a random number is generated (to simulate different users looking at different things). The report query selects up to 200 rows based on this random number (using bind variables, of course), out of a system table/view (DBA_OBJECTS) with around 17,000 total rows.

There is also an after footer page process that inserts a row in a log table, so this is not just a read-only page.

The application was set up with an authorization scheme of "No application authorization scheme required" to allow it to be accessed from the online stress test tool.

Stress test tool

LoadImpact is a user-friendly, web-based stress testing service that allows you to run free tests that simulate up to 50 concurrent clients (you can pay to test with more clients). A key point here is that the load is actually generated from their test servers on the public internet, so this gives a more realistic test than simply running a stress testing tool on your local network.

You don't even need to sign up, just enter the URL of a website to test it (apparently, there are safeguards to prevent this from being used as a denial-of-service attack tool).

A note regarding image caching: The LoadImpact service does not cache anything. This means that all the Apex images, Javascript and stylesheets will be loaded on every page hit, even though in reality they would be cached in the user's browser after the first page view. Since I wanted to test the database's ability to handle page views, rather than IIS's ability to serve up static files, I changed the Apex page so that the standard Javascript and CSS files were not included. I then manually added the core stylesheets (core Apex + theme) back on the page via the page template, just to preserve the look and layout of the page. But even these could have been left out in order to simulate a scenario where most users have the files cached in their browser.

Test results

The free test at LoadImpact runs five different subtests, starting with 10 concurrent clients and ending with 50 concurrent clients. The full test takes 10 minutes, so that is in five 2-minute parts with increasing load.

While this was going on, I watched the CPU usage in the Windows Task Manager and grabbed the following screenshots:

While there are some peaks at the start of each subtest, it seems like the database "warms up" and actually does less work at the middle and end of the subtest compared to the start. Even at 40 and 50 clients, the CPU is (on average) not working at more than half capacity.

Here is the summary from LoadImpact at the end of the test:

First of all, note the nice and almost flat curve, with response times of under 300 ms even with 50 clients (and remember that the load generator is actually located in a different country, accessing the Apex page via the public Internet).

Actually, while the test was running, I was also clicking through a separate Apex application (an actual application, not just a test page) on the same server, and I couldn't really notice that the server was under any kind of stress; the response times were excellent.

The total number of requests for the whole test was 4500, but these requests include not only the actual page (the call to the "f" procedure) but also 2 stylesheets and 3 images. So the database was hit 1 in 6 times, in other words 16,7% (of 4500) or 750 times. Each hit inserted a row into a log table. If we query that log table and group by down to the second, we can get the number of database requests per second:

This shows that the XE database was handling 10 or 11 page views per second. If this can be sustained over time, that is 36,000 page views per hour, or 864,000 page views per day.

By comparison, apex.oracle.com is reported to have 4,800,000 page views per week or 685,000 page views per day. And according to this, Reddit has 10 pageviews per second per server (with 80 servers), while Facebook has just 2 pageviews per second per server (with 30,000 servers!).

I won't read too much into these numbers (as obviously a "page view" can vary wildly between different websites, and only actual use can tell whether your site is performing well or not), but clearly the free XE database is more than a toy.

I'm even tempted to buy a Basic or Professional test at LoadImpact to test with more than 50 clients and see where that curve leads with 60, 80 or 100 concurrent clients...

Apex Listener Resource Templates

2011-02-24T08:03:00.000-08:00

An early adopter version 1.1 of the Apex Listener was released a while back.

Along with the software download there is a developer's guide that describes a new feature of the listener, called Resource Templates. From the guide:

Oracle Application Express Listener enables data stored in an Oracle Database to be exposed via RESTful Application Programming Interfaces (APIs).

The guide continues:

RESTful APIs are created by configuring 'Resource Templates'. A Resource Template is a configuration file that binds a set of URIs to a SQL query or anonymous PL/SQL block. The set of URIs is identified by a 'URI Template', a simple syntax for describing URIs e.g. people/{userid}. The URI Template may contain zero or more parameters ( e.g. {userid}) which along with the HTTP Headers sent with a HTTP request can be bound to parameters in the SQL query or anonymous PL/SQL block.

This is a very interesting feature that can be used outside of the Apex framework (ie in a barebones PL/SQL web application). Personally, I would prefer to be able to configure the resource templates somewhere in the database (in a special table and/or a predefined PL/SQL API), rather than messing around with the listener configuration, but the concept itself is a good one.

Anyway, it's an interesting read, so do take a look if you haven't already done so.

Working with Office 2007 (OOXML) files using PL/SQL

2011-02-20T22:54:00.000-08:00

Starting with Office 2007, Microsoft switched to an XML-based format called Office Open XML (OOXML).

There has been some debate as to how "open" this format really is, given that the specs run to around 7,000 pages (!).

Be that as it may, it's a fact of life that a lot of people use Microsoft's Office suite, and that means we have to deal with this new format in a lot of situations.

The OOXML format is, as it turns out, not so difficult to deal with. The main concept is that an Office document, whether it is a Word document (.docx), Excel spreadsheet (.xlsx) or Powerpoint presentation (.pptx), is actually a compressed (.zip) file that contains a number of XML documents (as well as any image files the user has included in the document).

So to work with OOXML files, we need to be able to zip and unzip files, and to parse and generate XML. Oracle (and PL/SQL) has had good support for XML for a number of years, but (even though there is a UTL_COMPRESS package in the database) there is no built-in zip/unzip support. Of course you could load some Java classes into the database to do it, but dealing with the Java stuff is always a bit of a hassle. But some time ago the good gentleman Anton Scheffer published a PL/SQL implementation based on UTL_COMPRESS that supports zipping and unzipping.

Based on this I have written a package for working with OOXML documents. It's called OOXML_UTIL_PKG and you can download it as part of (you guessed it) the Alexandria utility library for PL/SQL.

Let's see what this package allows us to do.

Get document properties from a Word (docx) file

First we fire up Word and create a test document:

By the way, you can read and write the new OOXML formats using an older version of Office (as I do in the screenshot above), by downloading the Microsoft Office Compatibility Pack from Microsoft.

After saving the document, we can then extract the document properties using the GET_DOCX_PROPERTIES function, which returns a custom record type called T_DOCX_PROPERTIES.

Extract plain text from a Word (docx) file

Using our test document again, we can extract the plain text of the document using the GET_DOCX_PLAINTEXT function, which returns a CLOB.

This is of course very useful if you want to search and/or index (just) the text of a document, or otherwise work with the content.

Get document properties from an Excel (xlsx) file

Let's first create an Excel test file (again, using Excel 2003 but saving in Excel 2007 format):

(This has to be one of the lamest spreadsheets of all time, but it will do fine as an example. It has some text, some numbers, and a formula.)

Similar to the Word document, we can now use the GET_XLSX_PROPERTIES function, which returns a custom record type called T_XLSX_PROPERTIES.

You'll notice that Word documents and Excel spreadsheets have slightly different properties.

Extract a cell value from an Excel (xlsx) file

The GET_XLSX_CELL_VALUE function allows us to retrieve a single value from a named cell in a specific worksheet, like this:

Technical Detail: In the XLSX format, strings (as opposed to numbers) are not stored in the actual cell where they are entered, but rather in a "shared strings" section. The cell just contains a reference back to this "shared strings" section. The GET_XLSX_CELL_VALUE function handles this for you, so you don't have to worry about that.

Extract multiple cell values from an Excel (xlsx) file

Since the function that extracts a single value from a spreadsheet must open the file, unzip it, and parse the XML content every time you call that function, there is another function (GET_XLSX_CELL_VALUES, notice the plural) that allows you to retrieve multiple values in one call. In other words, the file is unzipped and the contents parsed as XML just once, which is obviously more efficient.

Simply specify the names of multiple cells using an array of strings:

Write contents into OOXML file using PL/SQL

Since the contents of OOXML files are XML files, you can manipulate the existing content, or generate new content, and then save it back to the zip file that contains your document.

The following demonstrates one approach; it uses a Powerpoint file, but this technique will also work with Word and Excel files.

We create a Powerpoint 2007 file (.pptx) and put in some tags that we want to replace via code. In other words, this becomes a template that we can fill with dynamic values from the database.

The GET_FILE_FROM_TEMPLATE function takes a template file as input, and two string arrays: The tag names and actual values to replace the tags with. It unzips the file, performs the substitutions, writes back the file to the zip archive, and returns the file, which you can then save back to disk (or, more likely, store in the database or send to a web browser).

The code is trivial:

Here is the result when opening the output file:

So the next time you do a presentation, you could actually update your Powerpoint slides with the latest sales figures (or whatever) from within SQL*Plus...

Conclusion

Working with Office 2007 (OOXML) files from PL/SQL is easy and opens up many possibilities, both for extracting information from documents and storing them in the database, as well as generating or modifying OOXML files in the database server.

Generating test data using PL/SQL

2011-02-14T07:26:00.000-08:00

Here's a fun little PL/SQL package, part of the Alexandria library for PL/SQL, that you can use to generate semi-random test data.

The package is called RANDOM_UTIL_PKG. At the time of writing it contains the following functions:

GET_AMOUNT
GET_BUSINESS_CONCEPT
GET_BUZZWORD
GET_DATE
GET_EMAIL_ADDRESS
GET_ERROR_MESSAGE
GET_FILE_NAME
GET_FILE_TYPE
GET_INTEGER
GET_MIME_TYPE
GET_PASSWORD
GET_PERSON_NAME
GET_TEXT
GET_VALUE
GET_WAIT_MESSAGE

These should all be fairly self-explanatory, but let's look at some examples of how you could use these to fill your database with "realistic" test data.

A list of users

An email archive

A list of orders (and order items)

A list of uploaded files (or files in a folder)

A list of errors (an error log)

A list of business concepts or strategies

This is for those times when a struggling company needs to re-focus its strategy and define a new vision. No need to hire expensive management consultants, just fire up SQL*Plus and execute the following query:

Here are some of my favorites from the concepts I generated just now:

Quickly innovate sticky products with resource-maximizing portals.
Credibly plagiarize robust opportunities using timely architecture.
Dynamically reinvent dynamic leadership for end-to-end web services.
Appropriately empower front-end human capital via error-free web services.
Globally plagiarize stand-alone ideas after next-generation alignment.

Fun with RSS and PL/SQL, Part Two

2011-02-09T14:28:00.000-08:00

In my previous post, I talked about how to read (parse) RSS feeds using PL/SQL.

This post will cover how to publish your own RSS feeds, based on a SQL query.

You need the RSS_UTIL_PKG from the Alexandria library for PL/SQL.

Publishing an RSS feed using PL/SQL

The RSS_UTIL_PKG package contains the function REF_CURSOR_TO_FEED, which takes a REF CURSOR parameter and returns a CLOB with the RSS feed (the actual format can be specified and includes RSS, Atom and RDF).

Note that any query (REF CURSOR) can be used, and the column names in your query are irrelevant. What you must do is make sure that the number and order of columns (as well as the data types) match that of the T_FEED_ITEM record type defined in the RSS_UTIL_PKG package specification.

In other words, just make sure your query includes the following information in this order: ID, title, description, link and date.

Once you have the RSS feed as a CLOB, you would use the PL/SQL Web Toolkit (OWA) to print out the contents on a web page. You should also set the MIME type to XML, as in the following example:

Example: Creating a live feed of database errors

Let's put the RSS_UTIL_PKG to good use with an example. Let's say you are part of a development team, all working on various pieces of PL/SQL code in a shared development or test database.

In this situation it would be useful to see the current status of any invalid objects or PL/SQL compilation errors in the database.

Using an RSS feed, we can create a "live bookmark" in Firefox that shows us the status information in a convenient manner, without leaving the web browser.

We will do the following:

Create a query that lists PL/SQL compilation errors, based on the USER_ERRORS view, and return the results as a REF CURSOR.
Convert the REF CURSOR to an RSS feed, and output it using the PL/SQL Web Toolkit.
Set up a live bookmark in Firefox based on the feed procedure.
Create a details page to see details of each error (RSS feed item) when it is clicked.

Starting with the query, we wrap it in a function that returns a REF CURSOR:

We then create a procedure to format the REF CURSOR as RSS, and output it:

Then start Firefox and navigate to the procedure. This brings up Firefox's built-in RSS handler. Select "Live Bookmark" and subcribe to the feed.

Whenever you want to check for errors, just click the appropriate Live Bookmark folder in the Firefox toolbar, and the feed items will be displayed in a sub-menu:

If you click on one of the items, it will take you to the details page, which we have coded to retrieve the details of the error:

This is what the details page looks like in the browser:

Conclusion

We have seen that it is really easy to create your own RSS feed (all you have to do is write the query), and perhaps the example has given you some ideas and shown that RSS feeds can be used for more than news articles from online newspapers.

Note that if you have downloaded the Alexandria library for PL/SQL, you will find the code for the PLSQL_STATUS_WEB_PKG in the "demos" folder.

Fun with RSS and PL/SQL, Part One

2011-02-07T08:50:00.000-08:00

If you follow one or more blogs, you have probably heard about RSS.

Several varieties of the RSS format exist, so I will refer to them collectively as "feeds" (and when I say "RSS" I really mean any kind of feed).

RSS can be used to publish not just blogs, but also other kinds of information, such as news updates, audit trails, log entries, and so on.

The Alexandria library for PL/SQL contains RSS_UTIL_PKG, a package for publishing and parsing RSS feeds.

In this blog post I will describe how we can use this package to read (parse) RSS feeds. A follow-up post will describe how the same package can be used to publish any query as a feed.

Reading an RSS feed with SQL

The RSS_UTIL_PKG package contains a pipelined function named RSS_TO_TABLE which can be used in a SQL query to extract feed values.

A single line to read an RSS feed, that's not too shabby, eh?

Reading an RSS feed with PL/SQL

Another function in the RSS_UTIL_PKG is RSS_TO_LIST, which returns a PL/SQL associative array.

After the feed has been parsed to a list, you can loop through it and perform any required processing.

Of course you could also use the pipelined function in a cursor FOR loop, depending on your coding style.

Creating a report in Apex based on an RSS feed

Since you can use RSS_TO_TABLE in SQL statements, you can also use it as the basis for a report region in Apex.

Create a page and add a text item to input the URL of an RSS feed.

Then add a report region (classic or interactive) and use the following query as the report source:

Running the page produces the following result:

That's it for reading (parsing) RSS feeds. In my next blog post I will describe how to publish RSS feeds from the database using PL/SQL and SQL.

Display any XML as clickable tree using PL/SQL (and Apex)

2011-02-01T14:17:00.000-08:00

I don't have too many good things to say about IE, but I do like how it displays XML files. Chrome's handling of XML files (or total lack of it) sucks. But IE (and Firefox) gives you a nice tree structure which allows you to click on the various nodes to expand or collapse them.

This is actually implemented as an XSL stylesheet that is "hardcoded" into IE, and it is possible to extract it.

Using the default IE stylesheet for XML in your own applications

Here's how we can use this XSL stylesheet in our own PL/SQL and/or Apex applications. Let's pretend we receive product information in XML format from a business partner. We parse the XML file and put the information into our product database. When something goes wrong it's helpful to look at the raw XML, so we store that in the database as well (typically in a CLOB or XMLTYPE column).

Using Apex, we build a web page to look at the logs, including the XML content. We'd like a nice clickable treeview instead of the raw XML.

Here's where our XSL stylesheet (extracted from IE) comes in. That stylesheet can be found in the PL/SQL package called XML_STYLESHEET_PKG which is part of Alexandria, the PL/SQL Utility Library. Download the library and install it, then continue with these instructions.

In Apex, create a blank page (I'll be using page 6) and put an HTML Region on it. Then put a Textarea item (called P6_XML_INPUT) in this region. This will simulate our XML product file, although in reality you would of course pull this from the database or a file on disk. Also add a button to submit the page (back to itself).

Next, add a PL/SQL Region to the page, and put the following as the region source:

Run the page, paste some (valid) XML into the text area, submit the page, and voila! The PL/SQL region should show the data nicely formatted as a clickable tree.

Another example

This "clickable tree" can also be useful to show data from regular tables or queries.

You can easily create an XML representation of a query by using a REF CURSOR and passing it to the XMLTYPE type.

For example, create a PL/SQL Region and use this as the source:

Run the page, and the output should look something like this:

Conclusion

Using some handy utility packages from the PL/SQL Utility Library and a few lines of PL/SQL code, you can display any XML document as a clickable tree.

PL/SQL Utility Library now has a proper name

2011-02-01T14:05:00.000-08:00

A good library needs a good name, and what could be a better name for a library than (drum roll, please): Alexandria.

Alexandria library, Thoth gateway, anybody see a pattern here? :-)

PL/SQL Utility Library update

2011-01-21T12:09:00.000-08:00

I've added several packages to the PL/SQL Utility Library archive, including string, date, SQL and XML libraries. There are also some new examples in the "demos" folder.

All right, now it's Friday and time for a beer :-)

Get the latest version of the PL/SQL Utility Library from this page.

Back to the Future

2011-01-19T09:30:00.000-08:00

Come with me on a short trip through space and time, taking a look at the history of building (web sites).

The Stone Age, circa 1995

Although it involved a lot of manual labor, mighty Stonehenge and the pyramids were built with very simple tools (or by aliens, but let's deal with that topic another day).

Web development was pretty basic back then, but conceptually simple enough.

The Dark Ages, circa 1998

Some pretty amazing castles were built during this age, and they still stand today.

The basic tools had improved a lot, but they were still recognizable. Even an Neanderthal from the previous age could probably be effective with these tools.

The Age of Enlightenment, circa 2002

The gothic cathedrals from around this time look awesome, but were (and still are) inhabited by unpleasant religious fanatics.

At this point, great thinkers with lots of free time combined art and science to build magnificent works of beauty and elegance.

The Age of the Astronauts and the Race for Space (via the Clouds), circa 2008

A lot of very expensive spaceships were built during this age.

The great empires fought a cold, mostly ideological, war to see who could be the first to reach the moon.

Of course this all ended with a ka-boom and the eventual retirement of the space shuttle program.

A New Hope, 2011

Realizing that space tourism and new houses on Mars might be out of reach for most people in the foreseeable future, there is a renewed interest in more lightweight approaches to building and transportation. You might even call it eco-friendly.

Even the otherwise lethargic Microsoft jumps on the bandwagon, to the acclaim of professional developers.

"WebMatrix is full 180 from the highly abstracted cathedral that is ASP.NET. (...) WebMatrix is focused on simplicity and the "Get It Done" developer. Which, to me, is a massive undersell as we're all "Get it Done" developers."

"... you have to use raw SQL to query the database. This is going to turn off the Ivory Tower crowd who prefer to wade through XML Soup and tedious designers - but that's to their detriment. As I've always said - the best DSL I've ever seen for working with data is SQL."

That's cool. This is what web development looks like with WebMatrix:

Looks strangely familiar, doesn't it? A mix of HTML markup and code, and direct data access without any of that ORM stuff? Yes, that's right. For comparison, let's bring up that screenshot from the Dark Ages (1998) again:

Conclusions & Take-Aways

(Because there always needs to be a take-away.)

I am (definitely) not saying that "Frameworks are evil", or that "Progress is uncool". I'm just as lazy as any programmer, but I prefer the simplest thing that could possibly work.

I prefer frameworks that don't try to obscure that what we (most of us) are trying to do (most of the time) is to generate some HTML based on data from an SQL database.

Do we really need something like this

If something like this can do the job?

The golden path is probably somewhere in-between those two extremes. But keep in mind: Stonehenge has lasted for 5,000 years, while the space shuttle is being retired after less than 50 years.

PL/SQL Utility Library

2011-01-18T10:54:00.000-08:00

Most programmers have a collection of utilities and code libraries that they re-use for several projects.

I've created the PL/SQL Utility Library page on Google Code to host some generic utilities that I've written myself, some that I've collected from elsewhere (credits and links can be found in the relevant source code), as well as links to useful PL/SQL libraries that are actively maintained elsewhere.

Check out the links and download the source code, there's a lot of different stuff here; from parsing CSV files, integrating with Google Maps and generating JSON, to zipping/unzipping files with PL/SQL.

I plan to add several more packages as soon as I get the code cleaned up.

Drop a comment below if you know of any other PL/SQL libraries or utilities that should be added to the list!

SOAP Server in PL/SQL

2011-01-06T11:28:00.000-08:00

Or how to expose PL/SQL packages as SOAP web services using pure PL/SQL

I have blogged before about the various options available for both consuming and exposing SOAP web services using PL/SQL. (And if you don't know what SOAP is, here is a tongue-in-cheek introduction.)

Here is yet another lightweight alternative, a small PL/SQL package that implements a simple SOAP server. It will generate a WSDL document on-the-fly for the packages you want to expose (subject to a whitelist). Functions (only) are invoked using dynamic SQL, and the results are returned in a SOAP envelope. Exceptions are handled using the SOAP Fault mechanism.

I have successfully tested this package on both the Embedded PL/SQL Gateway (DBMS_EPG) on Oracle XE 10g, as well as on Apache/OHS with mod_plsql (tested on a 10g database).

Here is a screenshot showing Web Service Studio used to test the Employee demo service (ie database package):

To try it out, follow these steps:

Download the source code
Modify the package body to suit your environment (particularly the g_schema_name constant, and the is_whitelisted function)
Install the package into your schema
If you want to run the package through the Apex DAD, remember to grant execute on soap_server_pkg to anonymous (on EPG) or apex_public_user (on mod_plsql), and modify the request validation function (wwv_flow_epg_include_mod_local) as appropriate. Create a synonym if you don't want to include the schema name in the URL.
Use a SOAP client such as Web Service Studio or SoapUI and navigate to http://your-server/dad-name/soap_server_pkg.wsdl?s=your_package_name

Issues and limitations

This initial version only supports functions that return a single value (varchar2, number, date, clob). Functions returning complex (user-defined) types, object types or array types are not supported. But as the demo package shows, you can return complex values using a single CLOB formatted as XML.
The OWA toolkit has a 32K limit on the size of CGI environment variables, which means the SOAP request body is similarly restricted. So although you can return responses of any length from your web services, the requests you can receive must be under 32K in length (including the XML tags in the SOAP request envelope).
The Apex Listener (at least as of the EA release 1.1) differs from the EPG and mod_plsql in that it does not pass the SOAP_BODY request variable to the OWA toolkit, so this solution will not work with the Apex Listener. However, it should be trivial to add to the Listener, so if you would find it useful, then you should file an enhancement request with Oracle and ask for it.

I'll end with a note of caution: This package executes dynamic SQL. While care has been taken to sanitize the input and to implement a whitelist, you should carefully review these security measures in terms of your own environment before you expose your database on the network.

Good news about Oracle XE 11g

2010-09-28T23:44:00.000-07:00

Oracle OpenWorld 2010 came and went without any announcement of Oracle 11g Express Edition (XE).

However, according to Bradley D. Brown at TUSC, Oracle has now (re-) confirmed that they are still working on it.

For some time now the ACE Director community has been asking Mark Townsend (product manager for the Oracle DB) when the 11g version of Express was going to be available. He was consistent in saying that they wouldn't even consider releasing it until 11gR2 was available. Well...it's available now! So the ACED community said "OK, Mark..." and he responded with - it's already in the works and should be out...when asked "when..." we got the standard answer. The good news is that it's coming! Another piece of good news is that they raised the DB size limit from 4GB to 10GB. That makes for a nice free DB engine.

This is good news, since it has been a very long time since XE 10g was released. With Microsoft releasing updates to its free version of SQL Server at the same time as the full version, I think Oracle should do the same.

But hey, it's good, it's free, and it's coming eventually, so I'm not complaining! :-)

Proxy Authentication with Thoth Gateway

2010-08-08T06:00:00.000-07:00

Christian Vind submitted an enhancement request for the Thoth Gateway to support Oracle proxy authentication by passing on the current Windows username to the database connection string.

The point of proxy authentication is that

The proxy user only has "create session" privileges but can't do much else.
The real user does not have "create session" privileges and cannot log on to the database without knowing the proxy user name and password (and that is only set on the web/application server).
The USER function returns the real user name, and all standard database auditing, roles, etc. work as usual.

As of version 1.3 of the Thoth Gateway, proxy authentication is now supported. Here is how it works:

IIS Setup

Set up the application (virtual directory) in IIS where the gateway runs with Integrated Windows Authentication, so that the CGI environment variable LOGON_USER will be populated with the client's Windows username. (If the user is using Internet Explorer to browse the site, his identity will be passed on to the web server/gateway automatically; if using another browser, then an explicit logon is required.)

Oracle Setup

Define an "application server user", ie the common user that connections will be established through:

-- Log on as DBA (SYS or SYSTEM) that has CREATE USER privilege.

create user appserver identified by eagle;

create user end_user identified by secret;
grant create session to end_user;
alter user end_user grant connect through appserver;

Now test the setup with SQL*Plus, by connecting with the "application server user", and then "becoming" the end user:

-- note we don't specify the end_user password, but still become that user

SQL> connect appserver[end_user]/eagle
Connected.
SQL> select user from dual;

USER
------------------------------
END_USER

SQL>

Note that since the point of this is to take advantage of existing Active Directory accounts, you probably want to create your users like this:

create user "your_domain\end_user" identified externally;

grant create session to "your_domain\end_user";

alter user "your_domain\end_user" grant connect through appserver;

Thoth Gateway Setup

In web.config, modify the DAD settings (the following example assumes a local Oracle XE installation):

param name="DatabaseConnectString" value="//127.0.0.1:1521/xe"
param name="DatabaseConnectStringAttributes" value="Enlist=false;Proxy User Id=appserver;Proxy Password=eagle;"
param name="DatabaseUserName" value="LOGON_USER"
param name="DatabasePassword" value=""

Notice the value "LOGON_USER" specified for the DatabaseUserName parameter. This is a reserved string that will be replaced with the actual value of the LOGON_USER value from the web request (ie. the user's Windows username, typically "domain\username"). You can also specify "LOGON_USER_NO_DOMAIN" to strip away the domain part of the user name -- what you use will depend on how you have set up your user accounts in Oracle.

Testing It

To test that everything works at this point, create a procedure similar to the following, and execute it via the gateway (don't forget to grant execute privileges on it to the end-user's account, and create a public synonym for it unless you prefix with the procedure owner's name in the URL).

procedure test_proxy_auth
as
begin

  htp.header(1, 'Proxy authentication');

  htp.ulistopen;
  htp.listitem ('USER = ' || user);
  htp.listitem ('Proxy user = ' || sys_context('userenv', 'proxy_user'));
  htp.listitem ('CGI LOGON_USER = ' || owa_util.get_cgi_env('LOGON_USER'));
  htp.ulistclose;

end test_proxy_auth;

If successful, the USER function should return the end-user's Windows username, and the Proxy User should display as "appserver".

Postscript: A little enigma

Actually, if you do as described above, you could possibly get this error when you try to run the procedure via the gateway:

ORA-1045: user %s lacks CREATE SESSION privilege; logon denied

At least, that's what I got . To get around it, I had to explicitly grant this to the "appserver" user:

grant create session to appserver;

The funny thing is that my example above, tested via SQL*Plus, shows that this works without the grant! But when attempting the same connection via ODP.NET, it gives the above error unless the grant is made.

And if I revoke the "create session" from the end_user, the above example doesn't work in SQL*Plus, because of the missing privilege. Which seems to contradict the purpose of proxying, as defined at the top of this blog post.

If anybody knows why SQL*Plus and ODP.NET show different behaviour here, please let me know.

Thoth Gateway version 1.3 available

2010-08-08T05:31:00.000-07:00

There is a new version of the Thoth Gateway, a mod_plsql replacement for IIS, available for download. The latest version is 1.3.

It contains the following bug fixes and enhancements:

Bug Fix: Issue with parsing client IP address: Added exception handling to prevent error when parsing client IP address with invalid format.
Ignore additional request parameters: Certain tools and frameworks may dynamically add additional parameters to a request, which causes the corresponding PL/SQL call to fail, since these parameters are not defined in the procedure signature. As of this version, the gateway will now retry the call after dropping (ignoring) any parameters that cannot be found in the Oracle data dictionary for the procedure being called.
Support for Oracle proxy authentication (and Single Sign On) via dynamic username substitution: Oracle proxy authentication, combined with Integrated Windows Authentication in IIS, allows you to pass the end-user's identity from the client to the database session (so the function USER will return the end-user's Windows username, with no login required). This is useful in an intranet scenario where users are defined in an Active Directory domain and use Internet Explorer to access the PL/SQL web application.

For existing installations, simply overwrite the existing PLSQLGatewayModule.dll file in the "bin" folder with the latest version from the downloaded archive.

Apex on Thoth Gateway and IIS 7

2010-07-15T02:19:00.000-07:00

Several people have asked for instructions on how to run the Thoth Gateway (a mod_plsql replacement) on Microsoft Internet Information Server (IIS) 7.

I finally had the chance (and the time!) to test the configuration on a server running IIS 7. The biggest challenge was understanding the new administration console user interface for IIS 7; it was was slightly confusing for someone who is used to IIS 6.

After a bit of fiddling, I got the gateway up and running:

I have updated the installation instructions in the latest download package (version 1.2.1) with separate sections for IIS 6 and IIS 7. So download, unzip and read the instructions in the "doc" folder.

And leave a comment if you run into problems (or leave a comment if you managed to get it up and running using the instructions!).

Replacing Apex? More like Find and Replace...

2010-06-26T11:22:00.000-07:00

I was getting my daily dose of Apex blog posts when I noticed this advertisement:

I was curious, so I clicked the link and got the following page (http://www.wavemaker.com/solutions/oracleforms.html):

"Well, this is strange", I thought... it says "Oracle Forms" there in the URL, and in the illustration in the middle of the page, yet the advertisement and the page heading talks about replacing "APEX". There is also a claim that the latter "costs a fortune", but as we all know Application Express is a no-cost option in the Oracle database. WTF?

There is also a link to an 8-page whitepaper on "Migrating Oracle Apex Applications to Java" (http://www.wavemaker.com/pdf/Migrating-Oracle-Apex-Apps-To-Java-With-WaveMaker.pdf). This whitepaper includes the following screenshot:

As well as this table:

OK, so it is clearly Oracle Forms that is depicted and described here, but labeled as if it was Oracle Apex.

At best, this is a clueless mistake made by some marketing sod who did a global Find & Replace from FORMS to APEX. At worst, this is deliberately misleading.

In any case, it's just wrong, wrong, wrong. I don't know about you, but I would not trust a company that is either incompetent, dishonest, or both. I'll stick with Apex, thank you.

Small patch for the Thoth Gateway (Apex on IIS)

2010-06-15T07:06:00.000-07:00

Just a quick note to announce that a minor patch release (version 1.2.1) of the Thoth Gateway is available for download. The Thoth Gateway allows you to run Oracle Apex applications on the Microsoft IIS web server (a replacement for Apache and mod_plsql).

The following has changed:

Bug Fix for Content-Length: Fixed an issue where the content-length header would be incorrectly set for non-AL32UTF8 databases if the page contained multibyte characters.
Set ODP.NET connection string attributes: Added option to specify additional connection string attributes in the DAD configuration. This allows you to fine-tune the connection properties. See the ODP.NET documentation for more details.

Upgrade for existing installations: Simply download the latest version, unzip the archive and drop the PLSQLGatewayModule.dll file into your bin folder on the web server.

ApexGen has a new home

2010-05-07T11:57:00.000-07:00

My first open-source project was ApexGen, a utility to generate Oracle Application Express (Apex) pages from PL/SQL, in a fraction of the time it takes to create Apex pages manually (using point-and-click). I originally hosted it on SourceForge. However, since then I have started a few other projects hosted on Google Code, so I decided to move ApexGen to Google Code as well.

To summarize, here are my current Oracle, Apex and PL/SQL projects, all on Google Code:

Thoth Gateway, a mod_plsql replacement that runs on Microsoft Internet Information Server (IIS). It allows you to use IIS as the web server for Apex applications (instead of Apache or the Embedded PL/SQL Gateway), and it has a few extra features as well, such as CLOB support, automatic Web Services published from PL/SQL, XDB integration, and integrated Windows authentication out-of-the-box.
JQGrid Integration Kit for PL/SQL, a set of PL/SQL packages that allows you to use the JQGrid component to display and edit tabular data in your Apex applications. It is faster, better-looking and more flexible than the built-in tabular forms in Apex.
ApexGen, a utility to generate Oracle Application Express (Apex) pages from PL/SQL, in a fraction of the time it takes to create Apex pages manually. With Apex 4.0 just around the corner, I believe ApexGen will be due for an overhaul soon, as the export files are likely to have changed quite a bit.

SELECT * FROM spreadsheet (or How to parse a CSV file using PL/SQL)

2010-04-10T11:00:00.000-07:00

I recently needed to retrieve/download a comma-separated values (CSV) file from a website, and insert the data in an Oracle database table.

After googling around a bit, I found various pieces of the solution on AskTom, ExpertsExchange and other sites, which I put together in the following generic utility package for CSV files.

Usage

Because I have implemented the main parsing routine as a pipelined function, you can process the data either using straight SQL, or in a PL/SQL program.

For example, you can retrieve a download a CSV file as a clob directly from the web and return it as a table with a single statement:

select *
from table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))

And maybe do a direct insert via INSERT .. SELECT :

insert into my_table (first_column, second_column)
select c001, c002
from table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))

You can of course also use SQL to filter the results (although this may affect performance):

select *
from table(csv_util_pkg.clob_to_csv(httpuritype('http://www.foo.example/bar.csv').getclob()))
where c002 = 'Chevy'

Or you can do it in a more procedural fashion, like this:

create table x_dump
(clob_value clob,
 dump_date date default sysdate,
 dump_id number);


declare
  l_clob clob;

  cursor l_cursor
  is
  select csv.*
  from x_dump d, table(csv_util_pkg.clob_to_csv(d.clob_value)) csv
  where d.dump_id = 1;

begin

  l_clob := httpuritype('http://www.foo.example/bar.csv').getclob();
  insert into x_dump (clob_value, dump_id) values (l_clob, 1);
  commit;
  dbms_lob.freetemporary (l_clob);

  for l_rec in l_cursor loop
    dbms_output.put_line ('row ' || l_rec.line_number || ', col 1 = ' || l_rec.c001);
  end loop;

end;

Auxiliary functions

There are a few additional functions in the package that are not necessary for normal usage, but may be useful if you are doing any sort of lower-level CSV parsing. The csv_to_array function operates on a single CSV-encoded line (so to use this you would have to split the CSV lines yourself first, and feed them one by one to this function):

declare
  l_array t_str_array;
  l_val varchar2(4000);
begin

  l_array := csv_util_pkg.csv_to_array ('10,SMITH,CLERK,"1200,50"');

  for i in l_array.first .. l_array.last loop
    dbms_output.put_line('value ' || i || ' = ' || l_array(i));
  end loop;

  -- should output SMITH
  l_val := csv_util_pkg.get_array_value(l_array, 2);
  dbms_output.put_line('value = ' || l_val);

  -- should give an error message stating that there is no column called DEPTNO because the array does not contain seven elements
  -- leave the column name out to fail silently and return NULL instead of raising exception
  l_val := csv_util_pkg.get_array_value(l_array, 7, 'DEPTNO');
  dbms_output.put_line('value = ' || l_val);

end;

Installation

In order to compile the package, you will need these SQL types in your schema:

create type t_str_array as table of varchar2(4000);
/

create type t_csv_line as object (
  line_number  number,
  line_raw     varchar2(4000),
  c001         varchar2(4000),
  c002         varchar2(4000),
  c003         varchar2(4000),
  c004         varchar2(4000),
  c005         varchar2(4000),
  c006         varchar2(4000),
  c007         varchar2(4000),
  c008         varchar2(4000),
  c009         varchar2(4000),
  c010         varchar2(4000),
  c011         varchar2(4000),
  c012         varchar2(4000),
  c013         varchar2(4000),
  c014         varchar2(4000),
  c015         varchar2(4000),
  c016         varchar2(4000),
  c017         varchar2(4000),
  c018         varchar2(4000),
  c019         varchar2(4000),
  c020         varchar2(4000)
);
/

create type t_csv_tab as table of t_csv_line;
/

Here is the code for the package specification:

create or replace package csv_util_pkg
as
 
  /*
 
  Purpose:      Package handles comma-separated values (CSV)
 
  Remarks:      
 
  Who     Date        Description
  ------  ----------  --------------------------------
  MBR     31.03.2010  Created
 
  */
  
  g_default_separator            constant varchar2(1) := ',';
 
 
  -- convert CSV line to array of values
  function csv_to_array (p_csv_line in varchar2,
                         p_separator in varchar2 := g_default_separator) return t_str_array;
 
  -- convert array of values to CSV
  function array_to_csv (p_values in t_str_array,
                         p_separator in varchar2 := g_default_separator) return varchar2;
 
  -- get value from array by position
  function get_array_value (p_values in t_str_array,
                            p_position in number,
                            p_column_name in varchar2 := null) return varchar2;

  -- convert clob to CSV
  function clob_to_csv (p_csv_clob in clob,
                        p_separator in varchar2 := g_default_separator,
                        p_skip_rows in number := 0) return t_csv_tab pipelined;

end csv_util_pkg;
/

And here is the package body:

create or replace package body csv_util_pkg
as
 
  /*
 
  Purpose:      Package handles comma-separated values (CSV)
 
  Remarks:      
 
  Who     Date        Description
  ------  ----------  --------------------------------
  MBR     31.03.2010  Created
 
  */
 
 
function csv_to_array (p_csv_line in varchar2,
                       p_separator in varchar2 := g_default_separator) return t_str_array
as
  l_returnvalue      t_str_array     := t_str_array();
  l_start_separator  pls_integer     := 0 ;
  l_stop_separator   pls_integer     := 0 ;
  l_length           pls_integer     := 0 ;
  l_idx              binary_integer  := 0 ;
  l_quote_enclosed   boolean         := false ;
  l_offset           pls_integer     := 1 ;
begin
 
  /*
 
  Purpose:      convert CSV line to array of values
 
  Remarks:      based on code from http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_23106446.html
 
  Who     Date        Description
  ------  ----------  --------------------------------
  MBR     31.03.2010  Created
 
  */
 
  l_length := length(p_csv_line) ;

  if l_length > 0 then
    loop
      l_idx := l_idx + 1;

      l_quote_enclosed := false;
      if substr(p_csv_line, l_start_separator + 1, 1) = '"' then
        l_quote_enclosed := true;
        l_offset := 2;
        l_stop_separator := instr(p_csv_line, '"', l_start_separator + l_offset, 1);
      else
        l_offset := 1;
        l_stop_separator := instr(p_csv_line, p_separator, l_start_separator + l_offset, 1);
      end if;

      if l_stop_separator = 0 then
        l_stop_separator := l_length + 1;
      end if;

      l_returnvalue.extend;
      l_returnvalue(l_idx) := substr(p_csv_line, l_start_separator + l_offset, (l_stop_separator - l_start_separator - l_offset));
      exit when l_stop_separator >= l_length;
      
      if l_quote_enclosed then
        l_stop_separator := l_stop_separator + 1;
      end if ;
      l_start_separator := l_stop_separator;
    end loop;
    
  end if;
  
  return l_returnvalue;
 
end csv_to_array;
 
 
function array_to_csv (p_values in t_str_array,
                       p_separator in varchar2 := g_default_separator) return varchar2
as
  l_value       varchar2(32000);
  l_returnvalue varchar2(32000);
begin
 
  /*
 
  Purpose:      convert array of values to CSV
 
  Remarks:      
 
  Who     Date        Description
  ------  ----------  --------------------------------
  MBR     31.03.2010  Created
 
  */
  
  for i in p_values.first .. p_values.last loop
    l_value := p_values(i);
    if instr(l_value, p_separator) > 0 then
      l_value := '"' || l_value || '"';
    end if;
    if l_returnvalue is null then
      l_returnvalue := l_value;
    else
      l_returnvalue := l_returnvalue || p_separator || l_value;
    end if;
  end loop;
 
  return l_returnvalue;
 
end array_to_csv;


function get_array_value (p_values in t_str_array,
                          p_position in number,
                          p_column_name in varchar2 := null) return varchar2
as
  l_returnvalue varchar2(4000);
begin
 
  /*
 
  Purpose:      get value from array by position
 
  Remarks:     
 
  Who     Date        Description
  ------  ----------  --------------------------------
  MBR     31.03.2010  Created
 
  */
  
  if p_values.count >= p_position then
    l_returnvalue := p_values(p_position);
  else
    if p_column_name is not null then
      raise_application_error (-20000, 'Column number ' || p_position || ' does not exist. Expected column: ' || p_column_name);
    else
      l_returnvalue := null;
    end if;
  end if;
 
  return l_returnvalue;
 
end get_array_value;


function clob_to_csv (p_csv_clob in clob,
                      p_separator in varchar2 := g_default_separator,
                      p_skip_rows in number := 0) return t_csv_tab pipelined
as
  l_line_separator         varchar2(2) := chr(13) || chr(10);
  l_last                   pls_integer;
  l_current                pls_integer;
  l_line                   varchar2(32000);
  l_line_number            pls_integer := 0;
  l_from_line              pls_integer := p_skip_rows + 1;
  l_line_array             t_str_array;
  l_row                    t_csv_line := t_csv_line (null, null,  -- line number, line raw
                                                     null, null, null, null, null, null, null, null, null, null,   -- lines 1-10
                                                     null, null, null, null, null, null, null, null, null, null);  -- lines 11-20
begin
 
  /*
 
  Purpose:      convert clob to CSV
 
  Remarks:      based on code from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1352202934074
                              and  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:744825627183
 
  Who     Date        Description
  ------  ----------  --------------------------------
  MBR     31.03.2010  Created
 
  */
  
  -- If the file has a DOS newline (cr+lf), use that
  -- If the file does not have a DOS newline, use a Unix newline (lf)
  if (nvl(dbms_lob.instr(p_csv_clob, l_line_separator, 1, 1),0) = 0) then
    l_line_separator := chr(10);
  end if;

  l_last := 1;

  loop
  
    l_current := dbms_lob.instr (p_csv_clob || l_line_separator, l_line_separator, l_last, 1);
    exit when (nvl(l_current,0) = 0);
    
    l_line_number := l_line_number + 1;
    
    if l_from_line <= l_line_number then
    
      l_line := dbms_lob.substr(p_csv_clob || l_line_separator, l_current - l_last + 1, l_last);
      --l_line := replace(l_line, l_line_separator, '');
      l_line := replace(l_line, chr(10), '');
      l_line := replace(l_line, chr(13), '');

      l_line_array := csv_to_array (l_line, p_separator);

      l_row.line_number := l_line_number;
      l_row.line_raw := substr(l_line,1,4000);
      l_row.c001 := get_array_value (l_line_array, 1);
      l_row.c002 := get_array_value (l_line_array, 2);
      l_row.c003 := get_array_value (l_line_array, 3);
      l_row.c004 := get_array_value (l_line_array, 4);
      l_row.c005 := get_array_value (l_line_array, 5);
      l_row.c006 := get_array_value (l_line_array, 6);
      l_row.c007 := get_array_value (l_line_array, 7);
      l_row.c008 := get_array_value (l_line_array, 8);
      l_row.c009 := get_array_value (l_line_array, 9);
      l_row.c010 := get_array_value (l_line_array, 10);
      l_row.c011 := get_array_value (l_line_array, 11);
      l_row.c012 := get_array_value (l_line_array, 12);
      l_row.c013 := get_array_value (l_line_array, 13);
      l_row.c014 := get_array_value (l_line_array, 14);
      l_row.c015 := get_array_value (l_line_array, 15);
      l_row.c016 := get_array_value (l_line_array, 16);
      l_row.c017 := get_array_value (l_line_array, 17);
      l_row.c018 := get_array_value (l_line_array, 18);
      l_row.c019 := get_array_value (l_line_array, 19);
      l_row.c020 := get_array_value (l_line_array, 20);
      
      pipe row (l_row);
      
    end if;

    l_last := l_current + length (l_line_separator);

  end loop;

  return;
 
end clob_to_csv;


end csv_util_pkg;
/

Performance

On my test server (not my laptop), it takes about 35 seconds to process 12,000 rows in CSV format. I don't consider this super-fast, but probably fast enough for many CSV processing scenarios.

If you have any performance-enhancing tips, do let me know!

Bonus: Exporting CSV data

You can also use this package to export CSV data, for example by using a query like this.

select csv_util_pkg.array_to_csv (t_str_array(company_id, company_name, company_type)) as the_csv_data
from company
order by company_name

THE_CSV_DATA
--------------------------------
260,Acorn Oil & Gas,EXT
261,Altinex,EXT
262,Amerada Hess,EXT
263,Atlantic Petroleum,EXT
264,Beryl,EXT
265,BG,EXT
266,Bow Valley Energy,EXT
267,BP,EXT

This might come in handy, even in these days of XML and JSON ... :-)

Using TRUNC and ROUND on dates

2010-04-06T11:19:00.000-07:00

Maybe this is old news to some, but I recently became aware that it is possible to use TRUNC and ROUND not just on a NUMBER, but also on a DATE value.

For example, you can get the start of the month for a given date (using TRUNC), or the "closest" start of the month, rounded forward or backwards in time appropriate (using ROUND):

select sysdate,
  trunc(sysdate, 'YYYY') as trunc_year,
  trunc(sysdate, 'MM') as trunc_month,
  round(sysdate, 'MM') as round_month,
  round(sysdate + 15, 'MM') as round_month2
from dual

The above gives the following results:

SYSDATE                   TRUNC_YEAR                TRUNC_MONTH               ROUND_MONTH               ROUND_MONTH2              
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- 
06.04.2010 20:10:56       01.01.2010 00:00:00       01.04.2010 00:00:00       01.04.2010 00:00:00       01.05.2010 00:00:00

Somewhat related to this topic is the relatively obscure (?) EXTRACT function, which allows you to extract a part of a DATE:

select sysdate,
  extract(day from sysdate) as extract_day,
  extract(month from sysdate) as extract_month,
  extract(year from sysdate) as extract_year
from dual

Which gives the following results:

SYSDATE                   EXTRACT_DAY            EXTRACT_MONTH          EXTRACT_YEAR           
------------------------- ---------------------- ---------------------- ---------------------- 
06.04.2010 20:13:01       6                      4                      2010

If you try to extract the "hour", "minute" or "second" from a DATE, however, you get an ORA-30076: invalid extract field for extract source.

For some reason, these only work on TIMESTAMP values, not on the DATE datatype (which seems like an arbitrary limitation to me). Nevertheless:

select systimestamp,
  extract(hour from systimestamp) as extract_hour,
  extract(minute from systimestamp) as extract_minute,
  extract(second from systimestamp) as extract_second
from dual

The above gives the following results:

SYSTIMESTAMP  EXTRACT_HOUR           EXTRACT_MINUTE         EXTRACT_SECOND         
------------- ---------------------- ---------------------- ---------------------- 
06.04.2010 20.17.12,047000000 +02:00 18                     17                     12,047

jQGrid Integration Kit for PL/SQL and Apex

2010-03-07T03:43:00.000-08:00

I started developing applications back in the good (?) old client/server days. I was fortunate enough to discover Delphi quite early. Even from the start, the lowly 16-bit Delphi version 1 had a kick-ass DBGrid control which allowed you to quickly and easily build data-centric applications. Just write a SQL statement in a TDataSet component, connect it to the grid, and voila! Instant multi-row display and editing out of the box, without any coding.

Fast forward a decade. While I do enjoy building web applications (with PL/SQL and Apex) these days, I've always missed the simplicity of that DBGrid in Delphi. Creating updateable grids with Apex is pretty tedious work (not being entirely satisfied with the built-in updateable tabular forms, I've employed a combination of the apex_item API, page processes for updates and deletes, and custom-made Javascript helpers). It doesn't help that you have to refer to the tabular form arrays by number, rather than by name (g_f01, g_f02, etc.), and that you are restricted to a total of 50 columns per page.

Enter jQGrid, "an Ajax-enabled JavaScript control that provides solutions for representing and manipulating tabular data on the web".

jQGrid can be integrated with any server-side technology, so I decided to integrate it with PL/SQL and Apex.

Features

As of version 1.0, the jQGrid for PL/SQL and Apex has the following features:

Single line of PL/SQL code to render grid

Populate data based on REF CURSOR or SQL text (with or without bind variables). The REF CURSOR support is based on my REF Cursor to JSON utility package.

Define display modes (read only, sortable, editable) and edit types (checkbox, textarea, select list) per column

Store grid configuration in database, or specify settings via code (for read-only grids)

Ajax updates (insert, update, delete) based on either automatic row processing (dynamic SQL) or against your own package API

Multiple grids per page

Integrated logging and instrumentation

Usable without Apex (for stand-alone PL/SQL Web Toolkit applications) or with Apex, optionally integrated with Apex session security

The jQGrid Integration Kit for PL/SQL is free and open source. Download and try it now!.

REF Cursor to JSON

2010-02-11T09:51:00.000-08:00

REF Cursors are cool. They allow you to encapsulate SQL queries behind a PL/SQL package API. For example, you can create a function called GET_EMPLOYEES that returns a SYS_REFCURSOR containing the employees in a specific department:

function get_employees (p_deptno in number) return sys_refcursor
as
l_returnvalue sys_refcursor;
begin

open l_returnvalue
for
select empno, ename, job, sal
from emp
where deptno = p_deptno;

return l_returnvalue;

end get_employees;

The client (an application written in Java, .NET, PHP, etc.) can call your API and process the returned REF Cursor just as if it was a normal result set from a SQL query. The benefits are legion. The client no longer needs to contain embedded SQL statements, or indeed know anything about the actual database structure and query text. Privileges on the underlying tables can be revoked. The API can be shared and reused among different clients, whether they are written in Java, .NET, or any number of other languages.

That is, unless your client is Oracle Application Express (Apex). Apex unfortunately lacks the ability to process REF Cursors, or, more accurately, you cannot create report regions in Apex based on REF Cursors. For standard reports, you have to either embed the SQL statement in the region definition, or return the SQL text string from a function (and hope that the string you built is valid SQL when it gets executed). For interactive reports, only embedded SQL statements are supported.

I dislike having to scatter literal SQL statements all around my Apex applications, and not be able to take advantage of a package-based, shared and reusable PL/SQL API to encapsulate queries. I submitted a feature request to the Apex team back in 2007, asking for the ability to base report regions on REF Cursors, but so far this has not been implemented.

The problem, as far as I know, is that Apex uses (and must use) DBMS_SQL to "describe" a SQL statement in order to get the metadata (column names, data types, etc.) for a report region. But not until Oracle 11g did DBMS_SQL include a function (TO_CURSOR_NUMBER) that allows you to convert a REF Cursor into a DBMS_SQL cursor handle. So, as long as the minimum supported database version for Apex is Oracle 10g, support for REF Cursors is unlikely to be implemented.

In the meantime, there are a couple of alternatives:

Option 1: Pipelined functions

It's possible to encapsulate your queries behind a PL/SQL API by using pipelined functions. For example, the above example could be rewritten as...

create type t_employee as object (
empno number(4),
ename varchar2(10),
job varchar2(9),
sal number
);

create type t_employee_tab as table of t_employee;

function get_employees (p_deptno in number) return t_employee_tab pipelined
as
begin

for l_rec in (select empno, ename, job, sal from emp where deptno = p_deptno) loop
pipe row (t_employee (l_rec.empno, l_rec.ename, l_rec.job, l_rec.sal));
end loop;

return;

end get_employees;

And used from Apex (in a report region) via the TABLE statement:

select *
from table(employee_pkg.get_employees (:p1_deptno))

Option 2: XML from REF Cursor

The DBMS_XMLGEN package can generate XML based on a REF Cursor. While this does not "describe" the REF Cursor per se, it does give us a way (from PL/SQL) to find the column names of an arbitrary REF Cursor query, and perhaps infer the data types from the data itself. A couple of blog posts from Tom Kyte explain how this can be used to generate HTML based on a REF Cursor.

So back to Apex, you could generate a "report" based on a PL/SQL region with code similar to this:

declare
l_clob clob;
l_rc   sys_refcursor;
begin
l_rc := get_employees (:p1_deptno);
l_clob := fncRefCursor2HTML (l_rc);
htp_print_clob (l_clob);
end;

It would also be possible to pass your own XLST stylesheet into the conversion function (perhaps an Apex report region template fetched from the Apex data dictionary?) to control the appearance of the report.

I put "report" in quotes above, because until the Apex team implements report regions based on REF Cursors, you will miss all the nice built-in features of standard (and interactive) reports, such as sorting, paging, column formatting, linking, etc.

Option 3: JSON from REF Cursor

Bear with me, I am finally getting to the point of this blog post.

JSON is cool, too, just like REF Cursors. It's the fat-free alternative to XML, and JSON data is really easy to work with in Javascript.

For triple coolness, I want to use an API based on REF Cursors in PL/SQL, client-side data manipulation based on JSON, and Apex to glue the two together.

What I need is the ability to generate JSON based on a REF Cursor.

Apex does include a few JSON-related procedures in the APEX_UTIL package, including JSON_FROM_SQL. Although this procedure does support bind variables, it cannot generate JSON from a REF Cursor. (Also, the fact that is is a procedure rather than a function makes it less flexible than it could be. Dear Apex Team, can we please have overloaded (function) versions of these JSON procedures?)

REF Cursor to JSON: The (10g) solution

So I came up with this solution: Use DBMS_XMLGEN to generate XML based on a REF Cursor, and then transform the XML into JSON by using an XSLT stylesheet.

Note: As mentioned above, in Oracle 11g you can use DBMS_SQL to describe a REF Cursor, so you could write your own function to generate JSON from a REF Cursor, without going through XML first. (And perhaps in Oracle 12g the powers that be at Redwood Shores will provide us with a built-in DBMS_JSON package that can both generate and parse JSON?)

In the meantime, for Oracle 10g, I created the JSON_UTIL_PKG package.

Here is the code for the REF_CURSOR_TO_JSON function:

function ref_cursor_to_json (p_ref_cursor in sys_refcursor,
p_max_rows in number := null,
p_skip_rows in number := null) return clob
as
l_ctx         dbms_xmlgen.ctxhandle;
l_num_rows    pls_integer;
l_xml         xmltype;
l_json        xmltype;
l_returnvalue clob;
begin

/*

Purpose:    generate JSON from REF Cursor

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     30.01.2010  Created

*/

l_ctx := dbms_xmlgen.newcontext (p_ref_cursor);

dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag);

-- for pagination
if p_max_rows is not null then
dbms_xmlgen.setmaxrows (l_ctx, p_max_rows);
end if;

if p_skip_rows is not null then
dbms_xmlgen.setskiprows (l_ctx, p_skip_rows);
end if;

-- get the XML content
l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none);

l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx);

dbms_xmlgen.closecontext (l_ctx);

close p_ref_cursor;

if l_num_rows > 0 then
-- perform the XSL transformation
l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet));
l_returnvalue := l_json.getclobval();
else
l_returnvalue := g_json_null_object;
end if;

l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode);

return l_returnvalue;

end ref_cursor_to_json;

Examples of usage

Get a small dataset

declare
l_clob clob;
l_cursor sys_refcursor;
begin
l_cursor := employee_pkg.get_employees (10);
l_clob := json_util_pkg.ref_cursor_to_json (l_cursor);
dbms_output.put_line (substr(l_clob, 1, 200));
end;

{"ROWSET":[{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09.06.1981","SAL":2450,"COMM":null,"DEPTNO":10},{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"31.01.2005","SAL":5000,"COMM":null,"DEPTNO":10},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"23.01.1982","SAL":1300,"COMM":null,"DEPTNO":10}]}

A large dataset, with paging

declare
l_clob clob;
l_cursor sys_refcursor;
begin
l_cursor := test_pkg.get_all_objects;
l_clob := json_util_pkg.ref_cursor_to_json (l_cursor, p_max_rows => 3, p_skip_rows => 5000);
dbms_output.put_line (substr(l_clob, 1, 1000));
end;


{"ROWSET":[{"OBJECT_ID":5660,"OBJECT_NAME":"LOGMNRT_SEED$","OBJECT_TYPE":"TABLE","LAST_DDL_TIME":"07.02.2006"},{"OBJECT_ID":5661,"OBJECT_NAME":"LOGMNRT_MDDL$","OBJECT_TYPE":"TABLE","LAST_DDL_TIME":"07.02.2006"},{"OBJECT_ID":5662,"OBJECT_NAME":"LOGMNRT_MDDL$_PK","OBJECT_TYPE":"INDEX","LAST_DDL_TIME":"07.02.2006"}]}

It works with nested datasets, too.. !

select d.deptno, d.dname,
cursor (select e.*
from emp e
where e.deptno = d.deptno) as the_emps
from dept d

declare
l_json clob;
begin
l_json := json_util_pkg.sql_to_json ('select d.deptno, d.dname,
cursor (select e.*
from emp e
where e.deptno = d.deptno) as the_emps
from dept d');
dbms_output.put_line (substr(l_json, 1, 10000));
end;

{"ROWSET":[{"DEPTNO":10,"DNAME":"ACCOUNTING",
"THE_EMPS":[{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09.06.1981","SAL":2450,"COMM":null,"DEPTNO":10},
{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"31.01.2005","SAL":5000,"COMM":null,"DEPTNO":10},
{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"23.01.1982","SAL":1300,"COMM":null,"DEPTNO":10}]},
{"DEPTNO":20,"DNAME":"RESEARCH",
"THE_EMPS":[{"EMPNO":7369,"ENAME":"SMITH","JOB":"SALESMAN","MGR":7902,"HIREDATE":"17.12.1980","SAL":880,"COMM":null,"DEPTNO":20},
{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"02.04.1981","SAL":2975,"COMM":null,"DEPTNO":20},
{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"09.12.1982","SAL":3000,"COMM":null,"DEPTNO":20},
{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"12.01.1983","SAL":1100,"COMM":null,"DEPTNO":20},
{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"03.12.1981","SAL":3000,"COMM":null,"DEPTNO":20},
{"EMPNO":9999,"ENAME":"BRATEN","JOB":"CLERK","MGR":7902,"HIREDATE":"05.05.2009","SAL":1000,"COMM":null,"DEPTNO":20},
{"EMPNO":9998,"ENAME":"DOE","JOB":"CLERK","MGR":7902,"HIREDATE":"25.04.2009","SAL":500,"COMM":null,"DEPTNO":20}]},
{"DEPTNO":30,"DNAME":"SALES",
"THE_EMPS":[{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"20.02.1981","SAL":1600,"COMM":300,"DEPTNO":30},
{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"22.02.1981","SAL":3200,"COMM":500,"DEPTNO":30},
{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"28.09.1981","SAL":1250,"COMM":1400,"DEPTNO":30},
{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"01.05.1981","SAL":2850,"COMM":null,"DEPTNO":30},
{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"08.09.1981","SAL":1500,"COMM":0,"DEPTNO":30},
{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7788,"HIREDATE":"03.12.1981","SAL":950,"COMM":null,"DEPTNO":30}]},
{"DEPTNO":40,"DNAME":"OPERATIONS",
"THE_EMPS":null}]}

Passing a REF Cursor directly to the function call by using the CURSOR function:

select json_util_pkg.ref_cursor_to_json(cursor(select * from emp))
from dual

{"ROWSET":[{"EMPNO":7369,"ENAME":"SMITH","JOB":"SALESMAN","MGR":7902,"HIREDATE":"17.12.1980","SAL":880,"COMM":null,"DEPTNO":20},{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"20.02.1981","SAL":1600,"COMM":300,"DEPTNO":30},{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"22.02.1981","SAL":3200,"COMM":500,"DEPTNO":30},{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"02.04.1981","SAL":2975,"COMM":null,"DEPTNO":20},{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"28.09.1981","SAL":1250,"COMM":1400,"DEPTNO":30},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"01.05.1981","SAL":2850,"COMM":null,"DEPTNO":30},{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"09.06.1981","SAL":2450,"COMM":null,"DEPTNO":10},{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"09.12.1982","SAL":3000,"COMM":null,"DEPTNO":20},{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"31.01.2005","SAL":5000,"COMM":null,"DEPTNO":10},{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"08.09.1981","SAL":1500,"COMM":0,"DEPTNO":30},{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"12.01.1983","SAL":1100,"COMM":null,"DEPTNO":20},{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7788,"HIREDATE":"03.12.1981","SAL":950,"COMM":null,"DEPTNO":30},{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"03.12.1981","SAL":3000,"COMM":null,"DEPTNO":20},{"EMPNO":7934,"ENAME":"MILLERø","JOB":"CLERK","MGR":7782,"HIREDATE":"23.01.1982","SAL":1300,"COMM":null,"DEPTNO":10},{"EMPNO":9999,"ENAME":"BRATEN","JOB":"CLERK","MGR":7902,"HIREDATE":"05.05.2009","SAL":1000,"COMM":null,"DEPTNO":20},{"EMPNO":9998,"ENAME":"DOE","JOB":"CLERK","MGR":7902,"HIREDATE":"25.04.2009","SAL":500,"COMM":null,"DEPTNO":20}]}

Download the package

~~You can download the complete package, including the XSLT stylsheet, here (spec) and here (body).~~

Update 12.02.2011: This package can now be downloaded as part of the Alexandria library for PL/SQL.

Note that to compile the packages you need the following SQL type defined in your schema:

create type t_str_array as table of varchar2(4000);
/

My first Apex 4 plugin: Flight Info from Web Service

2010-02-04T11:55:00.000-08:00

One of the exciting new features in Apex 4 is the support for plugin regions and items. This feature has huge potential, and will make development with Apex even more efficient, productive, and fun. There are already several plugins out there, and I think we will see a lot of interesting work in this area after Apex 4 is released.

Here is my own first attempt at a (useful) plugin: A region plugin that displays up-to-date flight information for airports in Norway, based on public flight data provided by Avinor, the company that operates the Norwegian airport network.

Avinor has a simple web service that provides flight information in XML format.

I am sure there are similar (web) services for flight information in other countries (feel free to leave a comment below if you know of any).

Here is the PL/SQL code behind the plugin:

procedure render_my_plugin (
p_region              in apex_plugin.t_region,
p_plugin              in apex_plugin.t_plugin,
p_is_printer_friendly in boolean )
as
l_clob clob;
l_airport_code varchar2(20) := p_region.attribute_01;
l_direction    varchar2(20) := p_region.attribute_02;
begin

l_clob := apex_web_service.make_rest_request(
p_url => 'http://flydata.avinor.no/XmlFeed.asp',
p_http_method => 'GET',
p_parm_name => apex_util.string_to_table('airport:direction'),
p_parm_value => apex_util.string_to_table(l_airport_code || ':' || l_direction )
);

if l_direction = 'D' then
htp.p('<p><b>Departures from ' || l_airport_code || '</b></p>');
else
htp.p('<p><b>Arrivals to ' || l_airport_code || '</b></p>');
end if;

htp.p('<table width="100%">');
htp.p('<tr><td>AIRLINE</td><td>FLIGHT</td><td>AIRPORT</td><td>TIME</td><td>GATE</td></tr>');

for l_rec in (
SELECT *
FROM XMLTABLE ('//airport/flights/flight'
PASSING XMLTYPE(l_clob)
COLUMNS unique_id       varchar2(100) path '@uniqueID',
airline         varchar2(10) path 'airline',
flight_id       varchar2(20) path 'flight_id',
airport         varchar2(20) path 'airport',
schedule_time   varchar2(100) path 'schedule_time',
gate            varchar2(100) path 'gate')
ORDER BY airline, flight_id) loop

htp.p('<tr><td>' || l_rec.airline || '</td><td>' || l_rec.flight_id || '</td><td>' || l_rec.airport || '</td><td>' || l_rec.schedule_time || '</td><td>' || l_rec.gate || '</td></tr>');

end loop;

htp.p('</table>');

htp.p('<a href="http://www.avinor.no">Flight data from Avinor.</a> Last updated: ' || to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss'));


end render_my_plugin;

The code illustrates several concepts:

How to render a region plugin using the PL/SQL Web Toolkit (HTP.P) calls
How to retrieve values from the attributes defined for the plugin
Using the new APEX_WEB_SERVICE.MAKE_REST_REQUEST function to retrieve a web page as a CLOB
Using the XMLTABLE function to transform XML into a recordset that can be used in a SELECT

An export of my plugin can be downloaded here, and installed into your own Apex 4 application.

After the plugin has been installed, using the plugin is as simple as adding a Region (of type Plugin) to the page, and configuring the values for Airport and Direction (the plugin attributes) in the region definition.

You can see a live demo of the plugin here (public page, does not require authentication):

http://tryapexnow.com/apex/f?p=test4ea:plugin_demo:0

Note that for this page, I've also taken advantage of the built-in region caching feature of Apex. The region cache duration is set to 10 minutes, which prevents us from hitting the remote web service for every page view. I really like that you can switch on region caching in Apex without writing a single line of code.

Conclusion: Apex 4 plugins rock!

ODP.NET minimal, non-intrusive install

2010-01-27T08:36:00.000-08:00

This might be of interest for those who use .NET to connect to Oracle databases. (Including yours truly, who wrote the Thoth Gateway, a mod_plsql replacement that runs on Microsoft IIS, using C# and ODP.NET.)

A while back, Microsoft officially deprecated their ADO.NET driver for Oracle (System.Data.OracleClient).

Fortunately, Oracle offers its own .NET driver, known as the Oracle Data Provider for .NET (ODP.NET). This driver is a better choice for Oracle connectivity, since it supports a wider range of Oracle-specific features, and improved performance.

However, ODP.NET, unlike, say, the thin JDBC drivers, still requires the normal Oracle client to be present on the machine. This Oracle client can be something of a beast, with the install package upwards of 200 megabytes. Couple this with the fact that you may have several diffent Oracle client versions installed on your machine (or application server), all specific to some application that you dare not touch for fear of it breaking.

A non-intrusive install

So, here is how you can use ODP.NET with the following advantages:

Small footprint (between 30 and 100 megabytes)
XCopy deployment
No dependency on shared files, all files in your own application's folder
No registry or system environment changes required
No tnsnames.ora file required
No interference from other Oracle client installs on the same machine

Sounds good, doesn't it? Let's see how this can be accomplished...

1. Download ODP.NET (xcopy version)

Download from here:

http://www.oracle.com/technology/software/tech/windows/odpnet/utilsoft.html

Unzip the file and locate the following 2 files:

OraOps11w.dll
Oracle.DataAccess.dll

Copy these files to your application's "bin" folder.

2. Download Oracle Instant Client

Download from here:

http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

You have a choice between the following two versions of the Instant Client

a) Instant Client Basic (approx. 100 megabytes)

Unzip the file and locate the following 3 files:

oci.dll
orannzsbb11.dll
oraociei11.dll

b) Instant Client Basic Lite (approx. 30 megabytes): This version is smaller but only supports certain character sets (WE8MSWIN1252 and AL32UTF8 are among them). It only has English messages, so in case you wonder what "ORA-06556: The pipe is empty" sounds like in your own language, go for the non-Lite version.

Unzip the file and locate the following 3 files:

oci.dll
orannzsbb11.dll
oraociicus11.dll

Whichever version you choose, copy these files to your application's "bin" folder. You now have a total of 5 new files in your "bin" folder.

3. Connection string

In your .NET program, use a connect string in the following format, to make sure you don't need to rely on any network configuration files (tnsnames.ora, etc.).

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=database_host_name)(Port=1521))(CONNECT_DATA=(SERVICE_NAME=database_service_name)))

4. Configuration

This is mostly relevant if you have other Oracle client installations already on the same machine/server.

In your configuration file (web.config), you can explicitly set the path to the Oracle DLLs you want to use. Set the "DllPath" parameter to the name of your "bin" folder.

<configuration>
<oracle.dataaccess.client>
<settings>
<add name="DllPath" value="c:\my_app_folder\bin"></add>
<add name="FetchSize" value="65536"></add>
<add name="PromotableTransaction" value="promotable"></add>
<add name="StatementCacheSize" value="10"></add>
<add name="TraceFileName" value="c:\temp\odpnet2.log"></add>
<add name="TraceLevel" value="0"></add>
<add name="TraceOption" value="0"></add>
</settings>
</oracle.dataaccess.client>
</configuration>

5. That's it!

You should now be able to run your ODP.NET application from your "bin" folder.

References

Using Google Translate from PL/SQL

2009-12-27T03:27:00.000-08:00

In today's globalized world, being able to communicate in different languages is important. Personally, I'm struggling to get my level of Spanish above the "una cerveza, por favor" level, and Google Translate is a great tool that I use often.

Wouldn't it be great to have the power of Google Translate directly from SQL? Google exposes the translation service via a RESTful (JSON) API, so I decided to write a small PL/SQL wrapper for it.

Here is the package specification:

create or replace package google_translate_pkg
as

/*

Purpose:    PL/SQL wrapper package for Google Translate API

Remarks:   see http://code.google.com/apis/ajaxlanguage/documentation/ 

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

-- http://code.google.com/apis/ajaxlanguage/documentation/reference.html#LangNameArray
g_lang_AFRIKAANS               constant varchar2(5) := 'af';
g_lang_ALBANIAN                constant varchar2(5) := 'sq';
g_lang_AMHARIC                 constant varchar2(5) := 'am';
g_lang_ARABIC                  constant varchar2(5) := 'ar';
g_lang_ARMENIAN                constant varchar2(5) := 'hy';
g_lang_AZERBAIJANI             constant varchar2(5) := 'az';
g_lang_BASQUE                  constant varchar2(5) := 'eu';
g_lang_BELARUSIAN              constant varchar2(5) := 'be';
g_lang_BENGALI                 constant varchar2(5) := 'bn';
g_lang_BIHARI                  constant varchar2(5) := 'bh';
g_lang_BULGARIAN               constant varchar2(5) := 'bg';
g_lang_BURMESE                 constant varchar2(5) := 'my';
g_lang_CATALAN                 constant varchar2(5) := 'ca';
g_lang_CHEROKEE                constant varchar2(5) := 'chr';
g_lang_CHINESE                 constant varchar2(5) := 'zh';
g_lang_CHINESE_SIMPLIFIED      constant varchar2(5) := 'zh-CN';
g_lang_CHINESE_TRADITIONAL     constant varchar2(5) := 'zh-TW';
g_lang_CROATIAN                constant varchar2(5) := 'hr';
g_lang_CZECH                   constant varchar2(5) := 'cs';
g_lang_DANISH                  constant varchar2(5) := 'da';
g_lang_DHIVEHI                 constant varchar2(5) := 'dv';
g_lang_DUTCH                   constant varchar2(5) := 'nl';  
g_lang_ENGLISH                 constant varchar2(5) := 'en';
g_lang_ESPERANTO               constant varchar2(5) := 'eo';
g_lang_ESTONIAN                constant varchar2(5) := 'et';
g_lang_FILIPINO                constant varchar2(5) := 'tl';
g_lang_FINNISH                 constant varchar2(5) := 'fi';
g_lang_FRENCH                  constant varchar2(5) := 'fr';
g_lang_GALICIAN                constant varchar2(5) := 'gl';
g_lang_GEORGIAN                constant varchar2(5) := 'ka';
g_lang_GERMAN                  constant varchar2(5) := 'de';
g_lang_GREEK                   constant varchar2(5) := 'el';
g_lang_GUARANI                 constant varchar2(5) := 'gn';
g_lang_GUJARATI                constant varchar2(5) := 'gu';
g_lang_HEBREW                  constant varchar2(5) := 'iw';
g_lang_HINDI                   constant varchar2(5) := 'hi';
g_lang_HUNGARIAN               constant varchar2(5) := 'hu';
g_lang_ICELANDIC               constant varchar2(5) := 'is';
g_lang_INDONESIAN              constant varchar2(5) := 'id';
g_lang_INUKTITUT               constant varchar2(5) := 'iu';
g_lang_IRISH                   constant varchar2(5) := 'ga';
g_lang_ITALIAN                 constant varchar2(5) := 'it';
g_lang_JAPANESE                constant varchar2(5) := 'ja';
g_lang_KANNADA                 constant varchar2(5) := 'kn';
g_lang_KAZAKH                  constant varchar2(5) := 'kk';
g_lang_KHMER                   constant varchar2(5) := 'km';
g_lang_KOREAN                  constant varchar2(5) := 'ko';
g_lang_KURDISH                 constant varchar2(5) := 'ku';
g_lang_KYRGYZ                  constant varchar2(5) := 'ky';
g_lang_LAOTHIAN                constant varchar2(5) := 'lo';
g_lang_LATVIAN                 constant varchar2(5) := 'lv';
g_lang_LITHUANIAN              constant varchar2(5) := 'lt';
g_lang_MACEDONIAN              constant varchar2(5) := 'mk';
g_lang_MALAY                   constant varchar2(5) := 'ms';
g_lang_MALAYALAM               constant varchar2(5) := 'ml';
g_lang_MALTESE                 constant varchar2(5) := 'mt';
g_lang_MARATHI                 constant varchar2(5) := 'mr';
g_lang_MONGOLIAN               constant varchar2(5) := 'mn';
g_lang_NEPALI                  constant varchar2(5) := 'ne';
g_lang_NORWEGIAN               constant varchar2(5) := 'no';
g_lang_ORIYA                   constant varchar2(5) := 'or';
g_lang_PASHTO                  constant varchar2(5) := 'ps';
g_lang_PERSIAN                 constant varchar2(5) := 'fa';
g_lang_POLISH                  constant varchar2(5) := 'pl';
g_lang_PORTUGUESE              constant varchar2(5) := 'pt-PT';
g_lang_PUNJABI                 constant varchar2(5) := 'pa';
g_lang_ROMANIAN                constant varchar2(5) := 'ro';
g_lang_RUSSIAN                 constant varchar2(5) := 'ru';
g_lang_SANSKRIT                constant varchar2(5) := 'sa';
g_lang_SERBIAN                 constant varchar2(5) := 'sr';
g_lang_SINDHI                  constant varchar2(5) := 'sd';
g_lang_SINHALESE               constant varchar2(5) := 'si';
g_lang_SLOVAK                  constant varchar2(5) := 'sk';
g_lang_SLOVENIAN               constant varchar2(5) := 'sl';
g_lang_SPANISH                 constant varchar2(5) := 'es';
g_lang_SWAHILI                 constant varchar2(5) := 'sw';
g_lang_SWEDISH                 constant varchar2(5) := 'sv';
g_lang_TAJIK                   constant varchar2(5) := 'tg';
g_lang_TAMIL                   constant varchar2(5) := 'ta';
g_lang_TAGALOG                 constant varchar2(5) := 'tl';
g_lang_TELUGU                  constant varchar2(5) := 'te';
g_lang_THAI                    constant varchar2(5) := 'th';
g_lang_TIBETAN                 constant varchar2(5) := 'bo';
g_lang_TURKISH                 constant varchar2(5) := 'tr';
g_lang_UKRAINIAN               constant varchar2(5) := 'uk';
g_lang_URDU                    constant varchar2(5) := 'ur';
g_lang_UZBEK                   constant varchar2(5) := 'uz';
g_lang_UIGHUR                  constant varchar2(5) := 'ug';
g_lang_VIETNAMESE              constant varchar2(5) := 'vi';
g_lang_WELSH                   constant varchar2(5) := 'cy';
g_lang_YIDDISH                 constant varchar2(5) := 'yi';
g_lang_UNKNOWN                 constant varchar2(5) := '';


-- translate a piece of text
function translate_text (p_text in varchar2,
p_to_lang in varchar2,
p_from_lang in varchar2 := null,
p_use_cache in varchar2 := 'YES') return varchar2;

-- detect language code for text
function detect_lang (p_text in varchar2) return varchar2;

-- get number of texts in cache
function get_translation_cache_count return number;

-- clear translation cache
procedure clear_translation_cache;

end google_translate_pkg;
/

And here is the package body:

create or replace package body google_translate_pkg
as

/*

Purpose:    PL/SQL wrapper package for Google Translate API

Remarks:   see http://code.google.com/apis/ajaxlanguage/documentation/ 

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

m_http_referrer                constant varchar2(255) := 'your-domain-name-or-website-here'; -- insert your domain/website here (required by Google's terms of use)
m_api_key                      constant varchar2(255) := null; -- insert your Google API Key here (optional but recommended)

m_service_url                  constant varchar2(255) := 'http://ajax.googleapis.com/ajax/services/language/';
m_service_version              constant varchar2(10)  := '1.0';

m_max_text_size                constant pls_integer   := 500; -- can be increased up towards 32k, the cache name size (below) must be increased accordingly 

type t_translation_cache is table of varchar2(32000) index by varchar2(550);

m_translation_cache            t_translation_cache;
m_cache_id_separator           constant varchar2(1) := '|';


procedure add_to_cache (p_from_text in varchar2,
p_from_lang in varchar2,
p_to_text in varchar2,
p_to_lang in varchar2)
as
begin

/*

Purpose:    add translation to cache

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

m_translation_cache (p_from_lang || m_cache_id_separator || p_to_lang || m_cache_id_separator || replace(substr(p_from_text,1,m_max_text_size), m_cache_id_separator, '')) := p_to_text;

end add_to_cache;


function get_from_cache (p_text in varchar2,
p_from_lang in varchar2,
p_to_lang in varchar2) return varchar2
as
l_returnvalue varchar2(32000);
begin

/*

Purpose:    get translation from cache

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

begin
l_returnvalue := m_translation_cache (p_from_lang || m_cache_id_separator || p_to_lang || m_cache_id_separator || replace(substr(p_text,1,m_max_text_size), m_cache_id_separator, ''));
exception
when no_data_found then
l_returnvalue := null;
end;

return l_returnvalue;

end get_from_cache;


function get_clob_from_http_post (p_url in varchar2,
p_values in varchar2) return clob
as
l_request     utl_http.req;
l_response    utl_http.resp;
l_buffer      varchar2(32767);
l_returnvalue clob := ' ';
begin

/*

Purpose:    do a HTTP POST and get results back in a CLOB

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

l_request := utl_http.begin_request (p_url, 'POST', utl_http.http_version_1_1);

utl_http.set_header (l_request, 'Referer', m_http_referrer); -- note that the actual header name is misspelled in the HTTP protocol
utl_http.set_header (l_request, 'Content-Type', 'application/x-www-form-urlencoded');
utl_http.set_header (l_request, 'Content-Length', to_char(length(p_values)));
utl_http.write_text (l_request, p_values);

l_response := utl_http.get_response (l_request);

if l_response.status_code = utl_http.http_ok then

begin
loop
utl_http.read_text (l_response, l_buffer);
dbms_lob.writeappend (l_returnvalue, length(l_buffer), l_buffer);
end loop;
exception
when utl_http.end_of_body then
null;
end;

end if;

utl_http.end_response (l_response);

return l_returnvalue;

end get_clob_from_http_post;


function translate_text (p_text in varchar2,
p_to_lang in varchar2,
p_from_lang in varchar2 := null,
p_use_cache in varchar2 := 'YES') return varchar2
as
l_values      varchar2(2000);
l_response    clob;
l_start_pos   pls_integer;
l_end_pos     pls_integer;
l_returnvalue varchar2(32000) := null;
begin

/*

Purpose:    translate a piece of text

Remarks:    if the "from" language is left blank, Google Translate will attempt to autodetect the language

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created
MBR     25.12.2009  Added cache for translations

*/

if trim(p_text) is not null then

if p_use_cache = 'YES' then
l_returnvalue := get_from_cache (p_text, p_from_lang, p_to_lang);
end if;

if l_returnvalue is null then

l_values := 'v=' || m_service_version || '&q=' || utl_url.escape (substr(p_text,1,m_max_text_size), false, 'UTF8') || '&langpair=' || p_from_lang || '|' || p_to_lang;

if m_api_key is not null then
l_values := l_values || '&key=' || m_api_key;
end if;

l_response := get_clob_from_http_post (m_service_url || 'translate', l_values);

if l_response is not null then

l_start_pos := instr(l_response, '{"translatedText":"');
l_start_pos := l_start_pos + 19;
l_end_pos := instr(l_response, '"', l_start_pos);

l_returnvalue := substr(l_response, l_start_pos, l_end_pos - l_start_pos);

if (p_use_cache = 'YES') and (l_returnvalue is not null) then
add_to_cache (p_text, p_from_lang, l_returnvalue, p_to_lang);
end if;

end if;

end if;

end if;

return l_returnvalue;

end translate_text;


function detect_lang (p_text in varchar2) return varchar2
as
l_url         varchar2(2000);
l_response    clob;
l_start_pos   pls_integer;
l_end_pos     pls_integer;
l_returnvalue varchar2(255);
begin

/*

Purpose:    detect language code for text

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

if trim(p_text) is not null then

l_url := m_service_url || 'detect?v=' || m_service_version || '&q=' || utl_url.escape (substr(p_text,1,m_max_text_size), false, 'UTF8');

if m_api_key is not null then
l_url := l_url || '&key=' || m_api_key;
end if;

l_response := httpuritype(l_url).getclob();

l_start_pos := instr(l_response, '{"language":"');
l_start_pos := l_start_pos + 13;
l_end_pos := instr(l_response, '",', l_start_pos);

l_returnvalue := substr(l_response, l_start_pos, l_end_pos - l_start_pos);

end if;

return l_returnvalue;

end detect_lang;


function get_translation_cache_count return number
as
l_returnvalue number;
begin

/*

Purpose:    get number of texts in cache

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

l_returnvalue := m_translation_cache.count;

return l_returnvalue;

end get_translation_cache_count;


procedure clear_translation_cache
as
begin

/*

Purpose:    clear translation cache

Remarks:    

Who     Date        Description
------  ----------  -------------------------------------
MBR     25.12.2009  Created

*/

m_translation_cache.delete;

end clear_translation_cache;


end google_translate_pkg;
/

So, let's take the package for a test drive!

Detecting languages

Google can (try to) figure out which language a specific text is in.

select google_translate_pkg.detect_lang ('hola mundo') as detect1,
google_translate_pkg.detect_lang ('ich bin ein berliner') as detect2
from dual

detect1              detect2             
-------------------- --------------------
es                   de                  

1 row selected.

Translating text

This is the most common usage of the package. Note that if you leave out the "from" language parameter, Google will attempt to autodetect the language.

select google_translate_pkg.translate_text ('excuse me, where is the toilet?', 'es') as the_phrase
from dual

the_phrase                              
----------------------------------------
Disculpe, ¿dónde está el baño?          

1 row selected.

Mass translation

Here is an example that translates several rows -- the product descriptions from the demo products table (from the Application Express demo application) -- into several languages.

select pi.product_id, pi.product_name, pi.product_description,
google_translate_pkg.translate_text (pi.product_description, 'es', 'en') as spanish_description,
google_translate_pkg.translate_text (pi.product_description, 'de', 'en') as german_description
from demo_product_info pi
order by pi.product_name

product_id product_name product_description spanish_description german_description
---------- -------------------- ------------------------------ ------------------------------ ------------------------------
3 Bluetooth Headset Hands-Free without the wires! Manos libres sin cables! Hands-Free ohne Kabel!
8 Classic Projector Does not include transparencie No incluye transparencias o lá Enthält keine Folien oder Fett
s or grease pencil piz de grasa stift

2 MP3 Player Store up to 1000 songs and tak Almacena hasta 1000 canciones Speichern Sie bis zu 1000 Song
e them with you y llevarlos con usted s, und nehmen Sie sie mit

4 PDA Cell Phone Combine your cell phone and PD Combine su teléfono celular y Kombinieren Sie Ihre Handy und
A into one device PDA en un solo dispositivo PDA in einem Gerät

5 Portable DVD Player Small enough to take anywhere! Lo suficientemente pequeño com Klein genug, um überall hin mi
o para tener en cualquier luga tnehmen!
r! tnehmen!

10 Stereo Headphones Noise-cancelling headphones pe El ruido auriculares con perfe Noise-Cancelling-Kopfhörer ide
rfect for the traveler cta para el viajero al für den Reisenden

9 Ultra Slim Laptop The power of a desktop in a po El poder de una computadora de Die Leistung eines Desktop in
rtable design escritorio en un diseño portá ein tragbares Design
rtable design til ein tragbares Design

1 3.2 GHz Desktop PC All the options, this machine Todas las opciones, se carga e Alle Optionen, ist diese Masch
is loaded! sta máquina! ine geladen!

6 512 MB DIMM Expand your PCs memory and gai Amplíe su PC la memoria y obte Erweitern Sie Ihren PC Speiche
n more performance ner más rendimiento r und gewinnen mehr Leistung

7 54" Plasma Flat Scre Mount on the wall or ceiling, Montar en la pared o el techo, Montage auf der Wand oder Deck
en the picture is crystal clear! el panorama es claro! e, das Bild ist glasklar!

10 rows selected.

Apex translation

Combine this package with the Apex dictionary views to get a kick-start when you are translating your own Apex applications into other languages (some tweaking of the results is probably necessary...).

select item_name, label,
google_translate_pkg.translate_text (label, 'es', 'en') as spanish_label,
google_translate_pkg.translate_text (label, 'nl', 'en') as dutch_label
from apex_application_page_items
where application_id = 103
and label is not null
and display_as <> 'Hidden'



item_name            label                spanish_label        dutch_label         
-------------------- -------------------- -------------------- --------------------
P101_PASSWORD        Password             Contraseña           Wachtwoord          
P101_USERNAME        User Name            Nombre de usuario    Gebruikersnaam      
P11_CUSTOMER_ID      Customer             Cliente              Klant               
P29_CUSTOMER_INFO    Customer Info        Información del clie Customer Info       
nte                                      
P29_ORDER_TIMESTAMP  Order Date           Fecha de pedido      Orderdatum          
P29_ORDER_TOTAL      Order Total          Orden total          Bestel Totaal       
P29_USER_ID          Sales Rep            Sales Rep            Vertegenwoordiger   


7 rows selected.

Caching

I have built in a simple cache mechanism, so that you avoid the network traffic if the phrase has already been translated in your current session (and the performance benefit can be huge if you repeatedly translate the same strings).
You can specify whether you want to use the cache (it's on by default), and there is also a procedure to clear (reset) the cache.

Conclusion

It is time to throw out your old-fashioned dictionaries and fire up SQL Plus instead :-)

More PL/SQL Gateway Goodies

2009-11-28T11:48:00.000-08:00

Hot on the heels of version 1.1, which was the topic of my previous blog post, the Thoth Gateway version 1.2 improves on the automatic SOAP Web Service feature, and adds a few new features as well!

Improved Automatic SOAP Web Services

Previously, every function had its own separate service endpoint. This was a bit of a pain, as you would have to (in Visual Studio-speak) add a separate web reference to each function. Now, all functions in a package are grouped together into a single service endpoint. Just add "?wsdl" to the package name, like this:

Upload files to file system instead of database table

Normally, files uploaded via a web page will be stored in the database table specified as "DocumentTableName" in the DAD configuration. In this version, there is a new configuration parameter, "DocumentFilePath", that will cause uploaded files to be saved to the file system instead.

XDB Integration

This version of the Thoth Gateway adds easy integration with Oracle XDB.

Upload files to XDB repository: You can specify a "DocumentXdbPath" in the DAD configuration file that causes uploaded files to be inserted as XDB resources in the specified folder. (This means there are now three different, and mutually exclusive, destinations for uploaded files: database table, file system, and XDB repository.)

Here, for example, we have just uploaded a zip file to XDB, which is then available via SQL, HTTP, FTP and WebDAV as usual:

Download files from XDB repository: You can specify an "XdbAlias" in the DAD configuration file. If this is specified, it will set up a virtual directory (similar to the "PathAlias" parameter) that forwards requests to the XDB repository. You can control which part of the repository you want to expose by specifying the "XdbPathRoot" parameter.

Here we are downloading the zip file via the gateway:

The new options are more fully explained in the installation guide.

Check out version 1.2 of the Thoth Gateway now!

Publish PL/SQL as SOAP Web Service

2009-11-17T09:50:00.000-08:00

You can easily consume a SOAP Web Service from PL/SQL, for example using Application Express or the FLEX_WS_API (see also my companion utilities to FLEX_WS_API).

But if you want to publish (or "expose") your PL/SQL procedures as a SOAP Web Service, your options have so far been a bit limited.

JDeveloper/JPublisher

JDeveloper has a "Publish as Web Service" feature that uses JPublisher to create various Java artifacts which must then be deployed to the application server. There are some details here, and an issue you need to be aware of if you are using Oracle 10g Express Edition (XE).

Now, this Java-based approach probably works fine for you if you have Java developers and a Java infrastructure in your company, although the need to (re-)generate the Java code whenever the PL/SQL code changes seems like a bit of a hassle to me.

Native Web Services (11g)

Oracle 11g (Release 1) introduced "Native Web Services". This is a servlet running in the XDB listener that automagically exposes PL/SQL code as SOAP Web Services. Here is some more information about it.

If you are a database guy like me, you probably like the "Native" approach better than the JPublisher method. However, there are a couple of issues with Native Web Services; first of all, it's an 11g feature (which of course means that it is not available in 10g, nor in Express Edition 10g), and it requires the XDB listener (which means you must either allow direct connections to your database, or set up another web server as a proxy for XDB).

Automatic Web Services with the Thoth Gateway

Since I like the concept of Native Web Services, I decided to implement a similar feature in the Thoth Gateway, a mod_plsql replacement for Microsoft IIS.

Version 1.1 of the Thoth Gateway adds a new DAD configuration parameter called InvocationProtocol. If this is set to "SOAP" (instead of the default "CGI"), PL/SQL called through the DAD will take its parameters from a SOAP request body, and respond with a SOAP response body.

The Web Service Definition Language (WSDL) document is automatically generated if you append "?wsdl" to the end of the URL. This allows a tool like Visual Studio to easily add a Web Reference to your stored procedure.

Let's see an example. Let's say we have the following package specification:

create or replace package employee_service
as

function get_employee_name (p_empno in number) return varchar2;

function get_employees (p_search_filter in varchar2) return clob;

end employee_service;
/

And the following package body:

create or replace package body employee_service
as

function get_employee_name (p_empno in number) return varchar2
as
l_returnvalue emp.ename%type;
begin

begin
select ename
into l_returnvalue
from emp
where empno = p_empno;
exception
when no_data_found then
l_returnvalue := null;
end;

return l_returnvalue;

end get_employee_name;


function get_employees (p_search_filter in varchar2) return clob
as
l_context     dbms_xmlgen.ctxhandle;
l_returnvalue clob;
begin

-- there are many ways to generate XML in Oracle, this is one of them...

l_context := dbms_xmlgen.newcontext('select * from emp where lower(ename) like :p_filter_str order by empno');

-- let's make Tom Kyte happy :-)
dbms_xmlgen.setbindvalue (l_context, 'p_filter_str', lower(p_search_filter) || '%');

l_returnvalue := dbms_xmlgen.getxml (l_context);

dbms_xmlgen.closecontext (l_context);

return l_returnvalue;

end get_employees;


end employee_service;
/

Now navigate to the following URL with the browser (assuming you have downloaded and installed the Thoth Gateway, of course; see the installation guide in the Doc folder):

http://localhost/pls/soap-demo/employee_service.get_employee_name?wsdl

This brings up the automatically generated WSDL:

Now use your favorite SOAP testing tool (I'm using Web Service Studio, but another good tool is SoapUI) and enter the same URL.

After the test tool has generated a proxy class for the Web Service, you should see something similar to the following:

Fill in the value in the request and invoke the Web Service:

The response from the Thoth Gateway is a SOAP envelope that contains the return value of the function.

Invoking the second function in the example package above returns a CLOB with XML that represents a dataset with several rows:

Pretty cool, heh? It "just works", with no extra code or configuration necessary, except specifying "SOAP" as the protocol in the DAD!

You can use the usual parameters such as InclusionList, ExclusionList and RequestValidationFunction to control access to specific procedures. Also, the CGI environment is set up as usual before the call, so your PL/SQL code can use owa_util.get_cgi_env to get information about the client (browser).

Limitations and Caveats

There are a couple of limitations in this first release of the SOAP feature:

You can only call PL/SQL functions (not procedures) via SOAP.
Functions must return VARCHAR2 or CLOB (but as we have seen in the example above, functions returning CLOBs allow you to return any XML as the response, so this should not really be a big limitation). Support for arrays and complex (user-defined) types might come later.
Each function is exposed as a service endpoint. This means that in Visual Studio, for example, you must create a separate Web Reference for each function you would like to call. A future version of the gateway might group all functions in a package into one service. Update (Nov 28, 2009): As of Thoth Gateway version 1.2, all functions in a package are now grouped together as a single service endpoint.

If you would like to try it out, go and grab version 1.1 of the Thoth Gateway now!

Bad news about Oracle XE 11g

2009-11-01T23:57:00.000-08:00

We all love Oracle 10g Express Edition (XE), and I'm sure everyone's waiting for the 11g version which incorporates all the feature enhancements and security fixes from the last three years.

However, it now looks like we have to wait "another year or two" for the 11g version of Oracle Express Edition :-(

Seriously, Oracle? No XE 11g before late 2011? That means something like six years between the 10g and the 11g version?

Meanwhile, Microsoft is releasing its free SQL Server Express Edition on the same schedule as the for-pay version.

If Oracle is serious about using Express Edition to gain converts to the Oracle database, it should seriously reconsider this decision to delay XE 11g.

Leave a comment below if you would like to see Oracle Express Edition 11g before 2011!

Apex Interactive Report Tip #1: Aggregating numbers in string columns

2009-10-14T09:01:00.000-07:00

I have been working on an Interactive Report based on a collection recently, to work around the fact that the SQL query for Interactive Reports must be static, and my requirement was to run different queries based on user input.

One problem is that since all columns in a collection are varchars (strings), the built-in aggregation feature of Interactive Reports does not work (it only allows you to aggregate on numbers, which is quite sensible, I guess). Normally you could always convert the string column to a number directly in the underlying query, but since my queries were dynamic (the whole point of using collections), that was not an option.

However, after some fiddling around I discovered that you can aggregate based on computed columns.

So, let's say we have this "transaction amount" column in our report, which consists of numbers but which Apex interprets as a string since it is retrieved from a varchar2 column in the apex_collections view:

Use the "Compute" menu item of the Interactive Report to create a new column, using the TRUNC function (for some reason, the TO_NUMBER function is not available from the list of functions, but TRUNC works -- just remember to include the number of decimals as the second parameter if you need decimals) and setting the appropriate numeric format mask:

We can now see that the new column has been added as a numeric column, since it is right-aligned instead of left-aligned (and also with a nice format mask):

The new column is now available under the "Aggregate" menu item of the Interactive Report, so we can create a Sum of the numbers:

After adding a "Control Break" to the report, we can see that the aggregation works:

Did I mention that I really like Interactive Reports? :-)

How to integrate PL_FPDF with Apex

2009-10-12T02:42:00.000-07:00

In my previous post about the free PL_FPDF package that allows you to produce PDF documents from PL/SQL, there was a comment asking for a step-by-step guide on how to integrate this with Oracle Application Express (Apex).

This is really quite simple. By default, the pl_fpdf.output procedure will "print" your PDF document to the web browser along with a header that instructs the browser that this is a downloadable document. All you need to do is to call your procedure that generates the PDF document from a page process within Apex.

Here are the steps:

1. Start by compiling the demo procedure from my previous post into the parsing schema of your Apex application.

2. In Apex, go to your application and add a new, blank page. Let's assume the new page number is 4.

3. Under the "Page Rendering" section, add a new Process of type "PL/SQL". The name of the process can be anything, but let's call it "Produce PDF". Make sure that the point is "On load - before header".

4. In the process source, add the following PL/SQL code:



begin

  -- call the procedure to generate the PDF document and send it to the browser
  test_pl_fpdf;

  -- stop the Apex engine from running the rest of the page
  apex_application.g_unrecoverable_error := true;

end;

5. Add a link to the new page (page number 4 if you follow my assumption in step 2) from any other page in the application.

6. Run the application. Now, whenever you navigate to page 4 the PDF file will be downloaded to the browser.

That's all you need to integrate PDF generation into your Apex application.

Free PDF package for PL/SQL

2009-10-08T08:17:00.000-07:00

I'm sure you've heard about PL/PDF, the PL/SQL-based solution that allows you to create PDF documents directly from your database. PL/PDF is a good product, but it is not free (the current license price is USD 600 per database server).

However, I just came across a free alternative called PL_FPDF, which is a PL/SQL port of the PHP-based FPDF. The latest version of PL_PDF seems to have been released about a year ago, and I can't believe I haven't seen or heard about it before now... !

UPDATE, MARCH 2011: Also check out this alternative PDF generation package by Anton Scheffer. It seems simpler and more robust than the PL_FPDF package, but may lack certain features. Be sure to evaluate both!

PL_FPDF is just a single package, so installation is a snap. Note that it uses the ORDImage data type to place images in PDF documents, so if you are running Oracle XE (which doesn't include the ORDImage data type), you need to comment out the few procedures that deal with this data type (and obviously you will not be able to include images in your PDF documents...).

After a few hours of experimentation, I was able to produce a semi-complex PDF document that tests a number of features in PL_FPDF. My test script is included below for your convenience. Note that the default assumption is that output goes to the web browser via the HTP package, but it should be trivial to add a procedure that saves the resulting BLOB to a database table instead.

Note: Since PL_FPDF is based on FPDF, I found the online documentation for FPDF (see link above) very useful in order to find out how the API for PL_FPDF works.

create or replace procedure test_pl_fpdf
as
l_text varchar2(32000) := 'First, reduce actual complexity by eliminating unnecessary features and then hiding what you can''t eliminate. Secondly, reduce perceived complexity by minimizing visual noise and reusing elements. And finally, use the blank state to help orient users. Minimizing complexity in the user interface will help people learn your application more quickly, use it more effectively and be happier all the while.

As jazz musician Charles Mingus said, "Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that''s creativity."';

begin

-- create document in portrait mode, use measurements in millimeters (mm), and use the A4 page format
pl_fpdf.FPDF('P','mm','A4');
pl_fpdf.openpdf;

-- display full page, two facing pages 
pl_fpdf.setdisplaymode ('fullpage', 'two');

-- a procedure that will be called at the end of each page
-- pl_fpdf.setfooterproc('demo.test_pdf_footer');  

pl_fpdf.AddPage();

-- set up some headers
pl_fpdf.SetFillColor(255,128,128);
pl_fpdf.SetFont('Arial','B',14);
--this header will be filled with the background color 
pl_fpdf.cell(40,7,'TABLESPACE', 1, 0, 'L', pfill => 1);
pl_fpdf.cell(40,7,'CONTENTS', 1);
pl_fpdf.cell(40,7,'INITIAL EXTENT', 1, 1);

pl_fpdf.SetFont('Arial','',14);

for l_rec in (select tablespace_name, contents, initial_extent from dba_tablespaces order by 1) loop

pl_fpdf.settextcolor(0,0,0);
pl_fpdf.cell(40,7,l_rec.tablespace_name,'B');
pl_fpdf.cell(40,7,l_rec.contents,'B');
-- some conditional formatting
if l_rec.initial_extent > 66000 then
pl_fpdf.settextcolor(255,0,0);
else
pl_fpdf.settextcolor(0,0,0);
end if;
pl_fpdf.cell(40,7,l_rec.initial_extent,'B', 1, 'R');

end loop;

-- a page that shows how to position chunks of text (with automatic line breaks) on the page

pl_fpdf.AddPage();

pl_fpdf.SetFont('Arial','B',16);
pl_fpdf.setxy (100, 20);
pl_fpdf.Cell(10,10,'Cool Quote 1:');

pl_fpdf.setxy (100, 50);
pl_fpdf.SetFont('Times','',12);
pl_fpdf.multicell(100,0,l_text);


pl_fpdf.SetFont('Arial','B',16);
pl_fpdf.setxy (10, 130);
pl_fpdf.Cell(10,10,'Cool Quote 2:');

pl_fpdf.setxy (10, 150);
pl_fpdf.SetFont('Times','',12);
pl_fpdf.multicell(100,0,l_text);

-- a page that demonstrates some simple drawing with lines and rectangles

pl_fpdf.AddPage();

pl_fpdf.SetFont('Arial','B',14);
pl_fpdf.Cell(0,0,'And now, some beautiful line art...',0,1,'C');

pl_fpdf.line (10,10, 50, 50);
pl_fpdf.rect (50,50, 50, 50);
pl_fpdf.setdrawcolor(0,255,0);
pl_fpdf.line (150,150, 50, 50);

pl_fpdf.setdrawcolor(0,0,0);

-- a simple table of employees, without headings

pl_fpdf.AddPage();

for l_rec in (select empno, ename from emp order by ename) loop

pl_fpdf.cell(40,7,l_rec.empno, 'B');
pl_fpdf.cell(40,7, l_rec.ename, 'B', 1);

end loop;


pl_fpdf.SetFont('Arial','B',48);
pl_fpdf.settextcolor(0,0,255);

pl_fpdf.setxy (100, 250);

pl_fpdf.cell(80,10,'THE END', palign => 'C');

pl_fpdf.Output();

end test_pl_fpdf;

Conclusion: While PL/PDF is more advanced in terms of features, PL_FPDF might be for you if you just need some simple PDF reports in your application.

Native PL/SQL support in DB2

2009-10-05T01:47:00.000-07:00

As reported by Steven Feuerstein in his PL/SQL programming newsletter this month, it is now possible to compile and run PL/SQL applications in IBM's DB2 database! Check out this demonstration video and more details at IBM's Developerworks.

Of course, the list of supported built-in packages is rather short (for example, UTL_HTTP and the XML packages are missing), but they do have support for REF CURSORS, CONNECT BY, and other advanced Oracle features.

This is cool, because it opens up a new market for all us PL/SQL developers out here.

Worth mentioning, too, is that EnterpriseDB (based on PostgreSQL) also has PL/SQL support.

Thoth Gateway: mod_plsql replacement for Microsoft IIS

2009-08-18T08:36:00.001-07:00

Take a look at this screenshot:

Yes, that's right... Oracle Application Express running on Microsoft's Internet Information Server (IIS)! In-between trips to the beach this summer, I decided to write a PL/SQL gateway module for IIS. The result, called the Thoth Gateway, is now available as open source.

This means that the deployment options for PL/SQL web applications are now:

The Embedded PL/SQL Gateway (DBMS_EPG), which is a webserver built into the database itself
The Oracle HTTP Server (OHS), which is based on the Apache codebase, with mod_plsql
Java-based web servers (such as Tomcat, WebLogic, and others) with the open-source DBPrism plugin, or the upcoming, Oracle-supported Apex Listener
And now, Microsoft Internet Information Server (IIS) with the Thoth Gateway module

The Thoth Gateway is implemented in C# as an ASP.NET HttpModule and uses the Oracle Data Provider for .NET (ODP.NET) to communicate with the database.

The Thoth Gateway supports almost all of mod_plsql's features, and even adds a few more (such as CLOB parameter support for values over 32K). Check out the project page for the details.

It's also worth noting that the Thoth Gateway, being a volunteer project, is not officially supported by Oracle in any way. But at least now you have the option of using Microsoft's IIS as the web server for Oracle PL/SQL web applications. In fact, you can use any of the above listed gateways side-by-side for the same PL/SQL web application (all on the same box, or on different physical servers).

I am very interested in feedback if you decide to go ahead and try the Thoth Gateway, so feel free to add comments to this post about any bugs or problems you encounter, or use the project's issue tracker (adding issues requires a Google account).

Calling a SOAP web service from PL/SQL by extending the FLEX_WS_API

2009-07-14T06:16:00.000-07:00

Jason Straub has written a Flexible Web Service API package that allows you to call SOAP web services from PL/SQL. The API handles a lot of low-level details for you. As far as I know, the API will be incorporated into the upcoming Apex 4.0.

The FLEX_WS_API package is very useful; however, there are still a few things, such as constructing the SOAP envelope and logging requests and response for debugging purposes, that you need to implement yourself.

Here are a few helpers that I have written to do just that:

Web service log table

First, let's make a table that can be used to log web service requests and responses.

create table ws_log (
request_start_date  date,
request_end_date    date default sysdate,
log_text            varchar2(4000),
ws_url              varchar2(4000),
ws_method           varchar2(4000),
ws_request          clob,
ws_response         clob,
val1                varchar2(4000),
val2                varchar2(4000),
val3                varchar2(4000)
);

Web service utility package

Here is the header of a package that handles logging, and also simplifies the extraction of values from the web service response.

create or replace package flex_ws_util
as

/*

Purpose:    The package is a companion to the flex_ws_api package

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

-- get string value
function get_value (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null) return varchar2;

-- get clob value
function get_value_clob (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null) return clob;

-- get date value
function get_value_date (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in date := null,
p_date_format in varchar2 := null) return date;

-- get number value
function get_value_number (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in number := null) return number;

-- log web service request
procedure log_request (p_url in varchar2,
p_method in varchar2,
p_request in clob,
p_response in xmltype,
p_request_start_date in date := null,
p_log_text in varchar2 := null,
p_val1 in varchar2 := null,
p_val2 in varchar2 := null,
p_val3 in varchar2 := null);

end flex_ws_util;
/

And then the package body:

create or replace package body flex_ws_util
as

/*

Purpose:    The package is a companion to the flex_ws_api package

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/


function get_value (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null) return varchar2
as
l_returnvalue varchar2(32767);
begin

/*

Purpose:    Get string value from web service response

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

begin
l_returnvalue := flex_ws_api.parse_xml (p_xml, '//' || p_name || '/text()', p_namespace);
exception
when others then
l_returnvalue := nvl(p_value_if_error, sqlerrm);
end;

return l_returnvalue;

end get_value;


function get_value_clob (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in varchar2 := null) return clob
as
l_returnvalue clob;
begin

/*

Purpose:    Get clob value from web service response

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

begin
l_returnvalue := flex_ws_api.parse_xml_clob (p_xml, '//' || p_name || '/text()', p_namespace);
exception
when others then
l_returnvalue := nvl(p_value_if_error, sqlerrm);
end;

return l_returnvalue;

end get_value_clob;


function get_value_date (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in date := null,
p_date_format in varchar2 := null) return date
as
l_str         varchar2(32767);
l_returnvalue date;
begin

/*

Purpose:    Get date value from web service response

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/


begin
l_str := flex_ws_api.parse_xml (p_xml, '//' || p_name || '/text()', p_namespace);
l_returnvalue := to_date (l_str, nvl(p_date_format, 'DD.MM.RRRR HH24:MI:SS'));
exception
when others then
l_returnvalue := p_value_if_error;
end;

return l_returnvalue;

end get_value_date;


function get_value_number (p_xml in xmltype,
p_name in varchar2,
p_namespace in varchar2 := null,
p_value_if_error in number := null) return number
as
l_str         varchar2(32767);
l_returnvalue number;
begin

/*

Purpose:    Get number value from web service response

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

begin
l_str := flex_ws_api.parse_xml (p_xml, '//' || p_name || '/text()', p_namespace);
l_returnvalue := to_number (l_str);
exception
when others then
l_returnvalue := p_value_if_error;
end;

return l_returnvalue;

end get_value_number;


procedure log_request (p_url in varchar2,
p_method in varchar2,
p_request in clob,
p_response in xmltype,
p_request_start_date in date := null,
p_log_text in varchar2 := null,
p_val1 in varchar2 := null,
p_val2 in varchar2 := null,
p_val3 in varchar2 := null)
as
pragma autonomous_transaction;
l_sysdate date := sysdate;
begin

/*

Purpose:    Log web service request

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

insert into ws_log (request_start_date, request_end_date,
log_text, ws_url, ws_method,
ws_request, ws_response,
val1, val2, val3)
values (nvl(p_request_start_date, l_sysdate), l_sysdate,
substr(p_log_text,1,4000), substr(p_url,1,4000), substr(p_method,1,4000),
p_request, p_response.getclobval(),
substr(p_val1,1,4000),substr(p_val2,1,4000), substr(p_val3,1,4000));

commit;

end log_request;



end flex_ws_util;
/

PL/SQL object type for SOAP envelopes

The following object type is used to simplify creation of SOAP envelopes to be used in web service calls:

create or replace TYPE t_soap_envelope AS OBJECT (

/*

Purpose:    Object type to handle SOAP envelopes for web service calls

Remarks:

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

-- public properties
service_namespace       varchar2(255),
service_method          varchar2(4000),
service_host            varchar2(4000),
service_path            varchar2(4000),
service_url             varchar2(4000),
soap_action             varchar2(4000),
soap_namespace          varchar2(255),
envelope                clob,

-- private properties
m_parameters            clob,

constructor function t_soap_envelope (p_service_host in varchar2,
p_service_path in varchar2,
p_service_method in varchar2,
p_service_namespace in varchar2 := null,
p_soap_namespace in varchar2 := null,
p_soap_action in varchar2 := null) return self as result,

member procedure add_param (p_name in varchar2,
p_value in varchar2,
p_type in varchar2 := null),

member procedure add_xml (p_xml in clob),

member procedure build_env,

member procedure debug_envelope

);
/

The type body is implemented like this:

create or replace type body t_soap_envelope
as

/*

Purpose:    Object type to handle SOAP envelopes for web service calls

Remarks:  

Who     Date        Description
------  ----------  -------------------------------------
MBR     17.02.2009  Created

*/

constructor function t_soap_envelope (p_service_host in varchar2,
p_service_path in varchar2,
p_service_method in varchar2,
p_service_namespace in varchar2 := null,
p_soap_namespace in varchar2 := null,
p_soap_action in varchar2 := null) return self as result
as
begin
self.service_host := p_service_host;
self.service_path := p_service_path;
self.service_method := p_service_method;
self.service_namespace := nvl(p_service_namespace, 'xmlns="' || p_service_host || '/"');
self.service_url := p_service_host || '/' || p_service_path;
self.soap_namespace := nvl(p_soap_namespace, 'soap');
self.soap_action := nvl(p_soap_action, p_service_host || '/' || p_service_method);
self.envelope := '';
build_env;
return;
end;


member procedure add_param (p_name in varchar2,
p_value in varchar2,
p_type in varchar2 := null)
as
begin

if p_type is null then
m_parameters := m_parameters || chr(13) || '  <' || p_name || '>' || p_value || '</' || p_name || '>';
else
m_parameters := m_parameters || chr(13) || '  <' || p_name || ' xsi:type="' || p_type || '">' || p_value || '</' || p_name || '>';
end if;
build_env;

end add_param;


member procedure add_xml (p_xml in clob)
as
begin

m_parameters := m_parameters || chr(13) || p_xml;
build_env;

end add_xml;


member procedure build_env (self in out t_soap_envelope)
as
begin

self.envelope := '<' || self.soap_namespace || ':Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:' || self.soap_namespace || '="http://schemas.xmlsoap.org/soap/envelope/">' ||
'<' || self.soap_namespace || ':Body>' ||
'<' || self.service_method || ' ' || self.service_namespace || '>' ||
self.m_parameters || chr(13) ||
'</' || self.service_method || '>' ||
'</' || self.soap_namespace || ':Body>' ||
'</' || self.soap_namespace || ':Envelope>';    

end build_env;


member procedure debug_envelope
as
i      pls_integer;
l_len  pls_integer;
begin

if envelope is not null then

i := 1; l_len := length(envelope);

while (i <= l_len) loop
dbms_output.put_line(substr(envelope, i, 200));
i := i + 200;
end loop;

else
dbms_output.put_line ('WARNING: The envelope is empty...');
end if;


end debug_envelope;

end;
/

Example of use

With the above objects created in your database, the code for calling a web service and extracting and logging the results now becomes simple and elegant like this:

declare
l_env          t_soap_envelope;
l_xml          xmltype;
l_val          varchar2(4000);
l_start_date   date;
begin

l_env := t_soap_envelope ('http://www.webserviceX.NET', 'length.asmx', 'ChangeLengthUnit', 'xmlns="http://www.webserviceX.NET/"');

l_env.add_param ('LengthValue', '100');
l_env.add_param ('fromLengthUnit', 'Feet');
l_env.add_param ('toLengthUnit', 'Meters');

l_start_date := sysdate;

l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);

l_val := flex_ws_util.get_value (l_xml, 'ChangeLengthUnitResult', l_env.service_namespace, 'error');

flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date, p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);

end;

If you have complex parameters that you need to add to the request, you can use the add_xml member procedure of the t_soap_envelope type to add any content to the envelope.

Creating a REST web service with PL/SQL (and making pretty URLs for your Apex apps)

2009-07-05T05:50:00.000-07:00

If you need to expose the data in your Oracle database to other systems in a standardized and technology-neutral way, web services are a natural choice. Broadly speaking, web services come in two flavors: SOAP and REST. Despite its name, the Simple Object Access Protocol (SOAP) can be complex and overkill for many common scenarios. Representational State Transfer (REST) is considered more lightweight and easy to use.

There is a good introduction to REST here: http://www.xfront.com/REST-Web-Services.html

A key point is that REST is not really a standard, but an architectural style. It is not limited to web services consumed by machines, either. Applications built using Ruby on Rails and the ASP.NET MVC framework typically have user-friendly URLs based on the REST principles.

For example, if you have been to StackOverflow.com, you will see URLs like the following, which are "clean" and friendly both to users and to search engines:

http://stackoverflow.com/users
http://stackoverflow.com/users/1/jeff-atwood
http://stackoverflow.com/questions
http://stackoverflow.com/questions/tagged/oracle
http://stackoverflow.com/questions/1078506/oracle-sql-developer-how-to-view-results-from-a-ref-cursor

So, REST is a good way of exposing resources on the web, and your Oracle database is full of resources (data), but how can you build a REST service using only PL/SQL?

The key to building a REST service in PL/SQL is in a documented, but little-used feature of the Embedded PL/SQL Gateway (and mod_plsql) called "Path Aliasing". I was not aware that this feature existed until I discovered it "by accident" while browsing the mod_plsql documentation:

http://download.oracle.com/docs/cd/A97335_02/apps.102/a90099/feature.htm#1007126

"If the PL/SQL Gateway encounters in an incoming URL the keyword entered in the Path Alias field, it invokes the procedure entered in the Path Alias Procedure field. (...) Applications that use path aliasing must implement the Path Alias Procedure. The procedure receives the rest of the URL (path_alias_URL) after the keyword, URL, as a single parameter, and is therefore responsible and also fully capable of dereferencing the object from the URL. Although there is no restriction on the name and location for this procedure, it can accept only a single parameter, p_path, with the datatype varchar2."

Sounds good, so let's try it out. First, we need to configure the Database Access Descriptor (DAD) to define a PL/SQL procedure which will handle our REST requests. Using the embedded gateway (DBMS_EPG), the attributes are called "path-alias" and "path-alias-procedure" (the corresponding DAD attributes for mod_plsql are "PlsqlPathAlias" and "PlsqlPathAliasProcedure").

I will be using the embedded gateway for this example. Assuming you have an existing DAD called "devtest", run the following as user SYS (or another user who has the privileges to modify the EPG configuration).

begin
dbms_epg.set_dad_attribute (dad_name => 'devtest', attr_name => 'path-alias', attr_value => 'rest-demo');
dbms_epg.set_dad_attribute (dad_name => 'devtest', attr_name => 'path-alias-procedure', attr_value => 'rest_handler.handle_request');

end;
/

Then we need to create the procedure itself. Run the following in the schema associated with the DAD:

create or replace package rest_handler
as

/*

Purpose:      A simple example of RESTful web services with PL/SQL (see http://en.wikipedia.org/wiki/Representational_State_Transfer#RESTful_web_services)

Remarks:      The DAD must be configured to use a path-alias and path-alias-procedure

Who     Date        Description
------  ----------  --------------------------------
MBR     05.07.2009  Created

*/

-- the main procedure that will handle all incoming requests
procedure handle_request (p_path in varchar2);

end rest_handler;
/

And then the package body (the example assumes that the EMP and DEPT demo tables exist in your schema; if not, then modify the code accordingly):

create or replace package body rest_handler
as

/*

Purpose:      A simple example of RESTful web services with PL/SQL (see http://en.wikipedia.org/wiki/Representational_State_Transfer#RESTful_web_services)

Remarks:      The DAD must be configured to use a path-alias and path-alias-procedure

Who     Date        Description
------  ----------  --------------------------------
MBR     05.07.2009  Created

*/


g_request_method_get           constant varchar2(10) := 'GET';
g_request_method_post          constant varchar2(10) := 'POST';
g_request_method_put           constant varchar2(10) := 'PUT';
g_request_method_delete        constant varchar2(10) := 'DELETE';

g_resource_type_employees      constant varchar2(255) := 'employees';
g_resource_type_departments    constant varchar2(255) := 'departments';


procedure handle_emp (p_request_method in varchar2,
p_id in number)
as
begin

/*

Purpose:      Specific handler for Employees

Remarks:

Who     Date        Description
------  ----------  --------------------------------
MBR     05.07.2009  Created

*/

if (p_id is not null) then

if p_request_method = g_request_method_delete then

delete
from emp
where empno = p_id;

elsif p_request_method = g_request_method_get then

for l_rec in (select * from emp where empno = p_id) loop
htp.p(l_rec.empno || ';' || l_rec.ename || ';' || l_rec.sal);
end loop;

end if;

else

if p_request_method = g_request_method_get then

for l_rec in (select * from emp order by empno) loop
htp.p(l_rec.empno || ';' || l_rec.ename || ';' || l_rec.sal || '
');
end loop;

end if;

end if;

end handle_emp;


procedure handle_dept (p_request_method in varchar2,
p_id in number)
as
begin

/*

Purpose:      Specific handler for Departments

Remarks:

Who     Date        Description
------  ----------  --------------------------------
MBR     05.07.2009  Created

*/

if (p_id is not null) then

if p_request_method = g_request_method_delete then

delete
from dept
where deptno = p_id;

elsif p_request_method = g_request_method_get then

for l_rec in (select * from dept where deptno = p_id) loop
htp.p(l_rec.deptno || ';' || l_rec.dname || ';' || l_rec.loc);
end loop;

end if;

else

if p_request_method = g_request_method_get then

for l_rec in (select * from dept order by deptno) loop
htp.p(l_rec.deptno || ';' || l_rec.dname || ';' || l_rec.loc || '
');
end loop;

end if;

end if;

end handle_dept;


procedure handle_request (p_path in varchar2)
as
l_request_method constant varchar2(10) := owa_util.get_cgi_env('REQUEST_METHOD');
l_path_elements  apex_application_global.vc_arr2;
l_resource       varchar2(2000);
l_id             number;

begin

/*

Purpose:      The main procedure that will handle all incoming requests

Remarks:      Parses the incoming path and calls a specific handler for each resource type

Who     Date        Description
------  ----------  --------------------------------
MBR     05.07.2009  Created

*/

-- note that an extra delimiter is added to the path, in case the user leaves out the trailing slash
l_path_elements := apex_util.string_to_table (p_path || '/', '/');

begin
l_resource := l_path_elements(1);
l_id := l_path_elements(2);
exception
when value_error or no_data_found then
l_resource := null;
l_id := null;
end;

case lower(l_resource)
when g_resource_type_employees then
handle_emp (l_request_method, l_id);
when g_resource_type_departments then
handle_dept (l_request_method, l_id);
when 'apex-employees' then
-- we can also use this REST handler to make pretty, search-engine-friendly URLs for Apex applications without having to use Apache mod_rewrite
apex_application.g_flow_id := 104;
owa_util.redirect_url('http://127.0.0.1:8080/apex/f?p=104:2:' || apex_custom_auth.get_session_id_from_cookie || '::::P2_EMPNO:' || l_id, true);
else
owa_util.status_line(404, 'Resource type not recognized.', true);
end case;

end handle_request;


end rest_handler;
/

Now try the following URLs in your browser, and you should be able to see the familiar EMP and DEPT data, in all their RESTful glory! Note that for simplicity, the example code produces simple semicolon-separated values, but depending on your requirements and who will consume the service (machine or human), you will probably want to use XML or HTML as the output format.

http://127.0.0.1:8080/devtest/rest-demo/departments
http://127.0.0.1:8080/devtest/rest-demo/departments/10
http://127.0.0.1:8080/devtest/rest-demo/employees
http://127.0.0.1:8080/devtest/rest-demo/employees/7839

Bonus: Making the URLs in your Apex applications pretty

The URLs generated by Apex are not very friendly to users nor to search engines. You can use Apache with mod_rewrite to set up mapping between REST-style URLs to your Apex pages, but the example code above also shows how this can be accomplished using pure PL/SQL.

The example assumes that you have an Apex application with Application ID = 104, and that you have made a Form on the EMP table on Page 2 of the application.

Try the following link

http://127.0.0.1:8080/devtest/rest-demo/apex-employees/7839

and you should be redirected to the Apex application (and if you were already logged into the Apex application, you don't have to login again as it will reuse your existing session).

REST for inserts and updates
The four HTTP methods are GET, PUT, POST and DELETE. To create a REST web service that can update data as well as query it, we need to inspect the CGI environment variable REQUEST_METHOD and process the request accordingly (see the Wikipedia article for details). The example code implements the GET and DELETE methods for Employees and Departments.

However, I don't see how we can implement POST or PUT processing using the PathAlias technique. The problem is that the webserver/gateway only sends the URL to the PathAliasProcedure. Any data that is POSTed to the URL is simply discarded by the gateway. Ideally, the names and values of the request should be sent to the PathAliasProcedure in name/value arrays (just like the gateway does when using flexible parameter passing). If anyone from Oracle is reading this, it can be considered an enhancement request for the next version of mod_plsql and the embedded gateway!

Even with this limitation, the ability to expose (read-only) data from the database as RESTful web services using just PL/SQL is pretty cool, isn't it? :-)

The Fat Database (or Thick Database) Approach

2009-06-16T14:15:00.001-07:00

I'm a big believer in the so-called "Fat Database" paradigm for data-centric business applications.

I think I first heard the term "Thick Database" in a presentation by Dr. Paul Dorsey at the ODTUG conference in 2007.
I prefer the slightly more hip term "Fat Database", and offer my own definition of the term:

"Building applications using the Fat Database approach means leveraging the full potential of the database engine and its features, rather than treating the database as a bit bucket. If a problem can be solved using the database, it should be solved using the database, rather than in a programming language outside the database."

In other words, the exact opposite of the current trend, which is to avoid any database feature except basic tables. The enterprise architecture astronauts would rather reinvent the wheel over and over again, using the latest silver bullet in the endless stream of "new and improved" languages and frameworks that appear (and disappear) every few years.
Benefits of the Fat Database approach include reduced cost and complexity, increased performance, and a degree of immunity against the need to constantly rewrite code in a rapidly changing technology landscape.

Here is a collection of links to presentations and papers related to the Fat Database approach:

Dr. Paul Dorsey, co-author of seven Oracle Press books on Designer, Database Design, Developer, and JDeveloper

Toon Koppelaars, co-author of Applied Mathematics for Database Professionals

Others

Lucas Jellema (Oracle ACE): Optimal Use of Oracle Database 10g and Oracle Database 11g for Modern Application Development
Mike Ault: The Myth of Database Independence
Tom Kyte (AskTom): "Why [do we need an] application server?"

I will return to this subject in future postings on this blog.

The Oracle Database as Development Platform

2009-06-10T23:18:00.000-07:00

There is really a lot of amazing stuff that you can do using just the Oracle database and PL/SQL these days. I've made this diagram to illustrate the Oracle database developer's toolbox (click image to enlarge):

Note that this is all native functionality in the database itself, it does not include anything from the Oracle Fusion (Java) technology stack (except JDeveloper, which is free and can be used for general database and web development, not just Java).

Adventures with Apex, part three: Setting up Apex with Tomcat and the DBPrism plugin

2008-12-21T06:03:00.000-08:00

It is common knowledge that you can run Apex using either the Apache-based Oracle HTTP Server (with mod_plsql) or the Embedded PL/SQL Gateway (on 10g XE and 11g), but did you also know that there is a third alternative?

The DBPrism project is a free, open-source plugin for the Tomcat web server that, among other things, can function as a replacement for mod_plsql.

Side Note 1: Interestingly, Oracle itself is also developing the "Apex Listener", what I assume is based on similar [Java] technology. So by Apex 4.0 we will have even more deployment choices.

Side Note 2: My wish is that someone familiar with Microsoft's IIS should write a plugin to mimic mod_plsql on that platform as well, to make it easier to start using Apex in companies that have standardized on Microsoft technology (sneaking it in the back door, so to speak :-). UPDATE (AUGUST 2009): I decided to implement a free, open-source PL/SQL gateway for IIS myself. The Thoth Gateway runs all mod_plsql applications (including Apex applications) on Microsoft Internet Information Server (IIS) 6.0 or later.

Anyway, here are instructions for using Apex with Tomcat and DBPrism:

ON THE DATABASE SERVER

1. Download the latest version of Application Express from oracle.com

- http://www.oracle.com/technology/products/database/application_express/download.html

2. Install Apex into the database according to the instructions

- do not worry about configuring either the Embedded PL/SQL Gateway (DBMS_EPG) or Oracle HTTP Server,
- but make sure to unlock the apex_public_user schema and make a note of the password given to apex_public_user (you will need it to configure the JDBC connection on the web server)

ON THE WEB SERVER

Note: This could be the same physical machine as the database, if desired.

1. Download Java Runtime Engine (JRE) from sun.com

- install by running setup program
- create an environment variable called JRE_HOME and set it to the location where JRE was installed

2. Download Tomcat from tomcat.apache.org

- unzip to a folder, for example c:\program files\apache-tomcat-xxx (where xxx is version number)
- follow instructions in RUNNING.txt to start Tomcat and verify installation (http://localhost:8080)

3. Download the Oracle JDBC Thin driver from Oracle.com

- http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_111060.html
- get the "ojdbc5.jar" file

4. Download DBPrism from http://sourceforge.net/projects/dbprism/

- choose the "dbprism" package (not the "cms" package)
- unzip and get the "dpls.war" file
- put the dpls.war file into the "webapps" folder in Tomcat
- shutdown and restart Tomcat
- this will create a "dpls" folder under "webapps"
- put the "ojdbc5.jar" file from step 3 in the dpls\WEB-INF\lib folder
- edit the prism.xconf file
- under "variables", change the value of the "demo.db" to a valid jdbc connectionstring to the database
- under under category named "DAD_apex", change the "dbpassword" to the correct password of the "apex_public_user" (note that this was set during/after the installation of Apex)

- shutdown and restart Tomcat again

5. Configure Apex images/javascript for Tomcat

- Create a folder called "i" under "webapps" in Tomcat
- shutdown and restart Tomcat
- Extract the files in the "images" folder in the Apex installation zip file and copy it to the "i" folder

6. Verify that everything works

- Go to http://localhost:8080/dpls/apex (this should display the login page of the Apex environment)

Pretty cool, isn't it? :-)

Adventures with Apex, part two: Setting up the stand-alone Oracle HTTP Server as a Windows service

2008-04-12T09:11:00.000-07:00

The latest stand-alone version of Oracle HTTP Server (OHS) for Windows is available on OTN as the file "iAS_101330_Apache2_Modplsql2_win32.zip".

I ran the default setup on a Windows 2003 server (a separate machine from the database server) and encountered no errors during installation. The setup process created a few Start Menu items to start and stop the Apache process (through the Oracle Process Manager, OPMN, and the "opmnctl startall" command, which in turn starts the HTTP Server).

After configuring OHS and mod_plsql on the webserver to connect to Apex on the database server (as per the Apex installation docs), everything seemed to work fine. However, when I logged out of the server, the Apex application (and OHS) stopped responding.

Investigating, I found that the Apache process (apache.exe) is started and runs under the current logged-in user when using the Start Menu item to start it. So once I logged out of the server, the Apache process shuts down...!

As far as I could tell, there should have been a Windows service called OracleProcessManager that runs the OPMN process in the background, without requiring a logged-on user. However, there is no such service created after installation.

I tried creating the service manually myself using the "sc create" command. The service was created successfully but gave an error message ["...did not respond in a timely fashion..."] when trying to start it.)

Fortunately, I had a quick look in the Knowledgebase on Metalink and found the solution there. According to Note:459474.1, entitled "Howto Create a Windows Services Entry for Starting Oracle Application Server Processes", this behavior is by design (!)...:

Most Oracle Application Server installations automatically create a Windows Services entry to allow the OracleAS processes to start up on server startup. By design, certain OracleAS releases such as the SOA Suite 10.1.3.1 do not create the Windows Services entry. In such installations, the user starts OracleAS processes from the Windows Start Menu. When the user logs out of the Windows session, the OracleAS processes will terminate as well. Resulting in an unexpected Application Server shutdown.

This note provides the steps to create a Windows Services entry for starting up OracleAS processes upon reboot/startup of the server. Processes started via Windows Services entries are not affect by a user logging out of the Windows session.

I'll leave the question as to why on earth the setup program doesn't create the necessary Windows services by default, for what is clearly a server-type product?!? up in the air for now.

Instead, I will provide the necessary details here for those who do not have access to Metalink:

Turns out I was right about having to create a Windows service manually using the "sc" command, but there is also a few registry entries that need to be present for it to work.

First, find the "key" value from the file \opmn\bin\oracle.key on the server. In my case, the value of this key was:

SOFTWARE\ORACLE\KEY_oracleas1

Run regedit and add the following two entries (in my case, the first entry already existed):


HKLM / Software / ORACLE / Key_oracleas1 / ORACLE_HOME_KEY = SOFTWARE\ORACLE\KEY_oracleas1

and


HKLM / Software / ORACLE / oracleas1 / ORACLE_OPMN_SERVICE = OracleHTTPServerProcessManager

Then run the following command to create the service:


sc create OracleHTTPServerProcessManager binPath= "c:\OraHome_1\opmn\bin\opmn.exe -s" DisplayName= "OracleHTTPServerProcessManager" start= "auto"

Note that there must be an empty space between the equal sign and the value in the command above. Apparently some kind of quirk with the sc command. By the way, the sc command is part of the Windows server resource kit (but it was already present on my Windows 2003 server).

After creating the service, go to Services and verify that the service starts up. Also verify using Task Manager that apache.exe runs under the SYSTEM user, rather than the logged-in user.

Adventures with Apex, part one: Boosting performance when using DBMS_EPG

2008-04-12T08:23:00.000-07:00

We are currently developing a web application using Apex 3.1. There is a handful of developers on the team and we all work in the Apex Application Builder against a shared Oracle 11g database server, which has the Embedded PL/SQL Gateway (DBMS_EPG), rather than a full Apache (Oracle HTTP Server) setup, as per the default database install.

It did not take long before we noticed a significant variation in the time it took to serve Apex pages from the database. Most of the time, pages were served quickly enough, but at uneven intervals pages would just take forever to load. It seemed as if the underlying database or webserver/XDB session was timing out.

After some head-scratching, googling, and experimentation, the following two configuration changes fixed the problem. Page performance is now consistently excellent, and we do not experience timeouts anymore.

Increase the XDB session-timeout parameter (I changed it from the default of 6000 [1 minute] to a new value of 30000 [5 minutes].
Increase the shared_servers database parameter (I changed it from the default of 1 to a new value of 5).

Note: I changed both these settings at the same time, so I did not measure the effect of each individually. I suspect the shared_servers database setting is the most important of the two.

Detailed instructions follow:

Changing the session-timeout parameter in XDB

Connect as SYSDBA and run the following:

declare
  l_newconfig xmltype;
begin
  select updatexml (dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/session-timeout/text()', 30000)
  into l_newconfig
  from dual;

  dbms_xdb.cfg_update (l_newconfig);

  commit;
end;
/

Note: ~~The database must be restarted for the change to take effect.~~ Issue "alter system register" for the new changes to take effect without restarting the database.

Changing the shared_servers database parameter

Connect as SYSDBA and run the following:

SQL> alter system set shared_servers = 5 scope=both;

System altered.

SQL> alter system register;

System altered.

This will create more background sessions to handle the Apex page requests served through XDB.

Implementing table inheritance in the database

2008-03-25T00:18:00.001-07:00

Over at sqlteam.com, there is an interesting article about "Implementing table inheritance in SQL Server". But the concept is just as applicable to Oracle, of course:

http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

Yet another Oracle blog!

2008-03-21T00:24:00.000-07:00

I was not really planning to start a blog, but then I realized the subdomain ORA-00001 was available on Blogger, so I thought "why not"? At least now I have a place to ramble about various Oracle-related stuff.