ORA-00001: Unique constraint violated

Tuesday, February 24, 2015

The PL/SQL Utility Library, codenamed "Alexandria", now has a new home at GitHub. This will make it easier to collaborate on the project. If you want to contribute, just fork and submit a pull request.

The liibrary contains around 50 PL/SQL packages as well as a few types. There are no table dependencies, which reflects the general-purpose nature of the utilities. Jeffrey Kemp has a great guided tour of the library which highlights some of the packages.

Before the move to GitHub, the latest version (1.7.0) had been downloaded almost 7,000 times, and all versions a total of 12,000 times. So I guess it's being pretty widely used...

Sunday, November 16, 2014

As mentioned in my previous post about the Thoth Gateway, a mod_plsql and Apex Listener alternative that runs on IIS, the goal for the next release of the gateway (tentatively called version 1.4.0) is to change the Oracle Data Provider for .NET (ODP.NET) from the "Unmanaged" to the "Managed" driver. By using the Managed (pure .NET) driver, the installation of the Thoth Gateway becomes even simpler, because it removes the need to have the full Oracle client (or even the Instant Client) on the system. Note that this will change the minimum system requirements from .NET Framework 3.5 to 4.0 (the version that the managed driver requires).

There's now a beta version of Thoth Gateway 1.4.0 available for download here. I've tested it myself successfully on Windows Server 2012 with IIS 8.5 and .NET Framework 4.5. I would appreciate feedback from those who want to download and test it out on the same, or other, configurations.

Please note the following:

The gateway no longer requires an Oracle client installed on the same machine (the web server). Similar to the way the "Thin JDBC" driver works, the "Managed ODP.NET" driver is just a single file (Oracle.ManagedDataAccess.dll) that is placed in the same "bin" folder as the gateway.

The supplied web.config file includes a configSections/SectionGroup with the .NET 4.0 sections. If upgrading from an earlier framework version running the gateway, you probably need to update the config file.

The "requestValidationMode" attribute of the "httpRuntime" node should be set to "2.0" to avoid problems when Apex passes HTML markup in the querystring (for success messages and the like).

Please leave a comment here, or in the Google Code issue log, if you had any problems or indeed if you were able to install and run it successfully.

Sunday, July 13, 2014

In my previous blog post, I took a look at the new APEX_JSON package that ships with Apex 5.0 and its capabilities for parsing JSON.

In this blog post, I am going to look at how the APEX_JSON package can be used to generate JSON from data in your database using PL/SQL.

There are multiple ways of creating JSON output using the APEX_JSON package. There are several overloaded write() procedures which can output simple types (like varchars, numbers, and dates), as well as those that print complex types (such as xmltype and ref cursors).

First, a simple example which prints a simple JSON structure with some hard-coded values combined with a value based on user input. The page setup in the Apex Page Designer looks like this:

Note the call to apex_json.initialize_output() which disables the automatic sending of JSON headers in the HTTP response. This is normally desirable when you are sending (just) JSON back to the client, but in my test application I want to output the JSON inside a normal HTML page, so I need to disable the JSON headers. The rest of the code consists of simple calls to open/close_object and write() to write the name/value pairs.

The output looks like this:

Next up is the very useful ability to generate JSON based on a ref cursor. This means that basically any SQL query, dynamic or static, can be transformed into JSON output, with just a single line of code. In my simple test application I've set up a PL/SQL region that opens a ref cursor based on user input, and then passes the cursor to the write() procedure.NOTE: Using dynamic SQL based on unsanitized user input makes your application vulnerable to SQL injection. If possible, you should always use static SQL instead of dynamic SQL. Also, never trust user input and always validate it (see, for example, the dbms_assert package) before using it in a dynamic SQL statement. The example code below does not follow these security best practices, in order to keep the example simple.

Here's the result in the running application. Note that the output even supports nested queries (via the CURSOR statement), which makes it possible to generate complex/nested JSON from a single SQL statement.

Lastly, the APEX_JSON package also supports output of JSON via XML, by passing an xmltype value to the write() procedure. This is very useful in itself, but can also be combined with other PL/SQL features, such as the ability to convert any database object type to XML via the XMLType constructor.

Consider this example, where I create a database object type called T_CAR, with multiple attributes. The example SQL statement shows I can instantiate a T_CAR object and then convert the object into XML.

I can then pass the resulting XML into the write() procedure of the APEX_JSON package to generate a JSON representation of the T_CAR object type.

Here's the output:

This quick look at the APEX_JSON package from Apex 5.0 shows that this new package gives developers a lot of power and flexibility in terms of parsing and generating JSON from PL/SQL.

Monday, June 30, 2014

As I mentioned a year ago, it looks like Apex 5.0 will include a new package called APEX_JSON for parsing and generating JSON. In this blog post, I will take a quick look at the parsing, and the next blog post will be about generating JSON output.

Since Apex 5.0 is still in Early Adopter (beta) mode and not yet released for download, we can run a query against the data dictionary (ALL_SOURCE) to view the specification of the APEX_JSON pacakge, like this:

To test the new APIs, I created a very simple test application in Apex 5.0. Here is the setup of the page in the new page designer:

And here is the running application, which allows you to enter some arbitrary JSON string and specify what part of it you want to parse out:

For those who still prefer (or need) to process the data as XML instead of JSON, there is a useful to_xmltype() function in the APEX_JSON package that can be used to turn any JSON into XML.

Simple example:

That was a quick look at the JSON parsing capabilities of Apex 5.0, yet another reason to look forward to its production release! :-)

Thursday, June 26, 2014

Anton Scheffer wrote a PL/SQL package called AS_PDF3 that covers all the basics: Different fonts (including TrueType), images, and tables.

There's the commercial PL/PDF package that has more features, such as the ability to use existing PDF documents as templates for new documents, compression, encryption, and more. There's also an extra Toolkit component which allows you to extract pages from existing PDFs, combine multiple PDFs into one document, manipulate document metadata, and so on.

Using the above solutions, the PDF document must be generated via (your own) PL/SQL code. If you are looking for a visual report editor, check out PL-JRXML2PDF from Andreas Weiden.

Also, if you are using Apex 4.2 or later with the Apex Listener (aka Oracle REST Data Services), you can output report regions to PDF, and customize the layout via XSL-FO templates as explained by Mark Sewtz in his blog post.

Wednesday, June 25, 2014

After downloading the recently released Oracle Express Edition for 64-bit Windows, I decided to test it out on a Windows Server 2008 R2 running in the Azure cloud (recently renamed from "Windows Azure" to "Microsoft Azure", I guess because you can run Linux on it, as well).

Here are the high-level steps needed to get this up and running:

Sign up for Azure and login to the management portal.

Create a new Virtual Machine, select Windows Server 2008 R2. The provisioning of the VM takes around 5 minutes. (I chose to use an A1 instance type, which costs around USD 50 per month, and has 1 core and 1,75GB of memory. Note that XE is limited to use only 1GB of memory, leaving some extra memory for the OS itself and for IIS).

When you create the VM, you specify a DNS name for it, for example "myxetest". This becomes the subdomain of Microsoft's "cloudapp.net" domain, for example "myxetest.cloudapp.net". If you want, you can use your own domain name by adding a CNAME record to your domain and pointing it towards the cloudapp.net subdomain.

Use Remote Desktop to login to the new server.

Download Oracle XE for 64-bit Windows. The file is around 300MB, so this takes less than a minute to download.

Unzip the XE file and run setup.exe. Specify a password for the SYS and SYSTEM users, and let the installer do its work. The whole setup process took about 10-15 minutes on my server.

Check that the database is running using sqlplus on the command line on the server.

Oracle XE 11g comes with Apex 4.0 preinstalled, and running via the Embedded PL/SQL Gateway (DBMS_EPG) on port 8080 (localhost only). You can verify that it's up and running by going to http://127.0.0.1:8080/apex/apex on the server. If you want EPG to work for remote hosts, you have to execute the dbms_xdb.setlistenerlocalaccess procedure and pass FALSE to enable non-local access. (You can also change the port used by EPG via the dbms_xdb.sethttpport procedure.)

You need to modify the Windows Firewall settings on the server to allow outbound traffic on port 8080. Finally, you also need to open up port 8080 for the VM in the Azure management portal (you'll find this setting under "Endpoints" for the VM.)

A note on production deployments

The Embedded PL/SQL Gateway (DBMS_EPG) is intended for development environments, and is not recommended for production. Since you are running on a Windows server, you probably want to leverage the excellent built-in Internet Information Server (IIS), with the Thoth Gateway which is an open source ASP.NET implementation of the OWA interface which is what the EPG, mod_plsql and the Apex Listener (now called Oracle REST Data Services, or ORDS) use to communicate with the database. You'll need to enable/install IIS first, as this is not enabled by default on a Windows 2008 server. Then download the Thoth Gateway and follow the installation instructions in the downloaded zip file.

Tuesday, June 24, 2014

As announced by Kris Rice (who is the product manager for SQL Developer, Apex Listener and Oracle XE), the Oracle Express Edition (XE) Database version 11g is now (finally!) available for 64-bit Windows.

It's been a looong wait; the 64-bit version for Windows was expected to be released around the same time as the 32-bit version, some 3 years ago.

About Me

I have been working as a consultant and software developer for 18 years (since 1997), with a special passion for relational databases. I have done a fair share of work using Microsoft tools (including SQL Server, VB, ASP, .NET and C#) and other tools (most notably Delphi), but my favorite tool is the Oracle database with PL/SQL and Apex.