The SQL function TRIM has been around since Oracle 8i and maybe earlier. TRIM enables you to trim characters from a character string. The following examples demonstrate its usage and show you a few little known features of this simple function.

Remove leading and trailing blank spaces:

SQL> SELECT TRIM (’ mystring ‘) FROM dual;

TRIM(’MY ——– mystring

Remove […]

In part 1, we reviewed HTTPURIType and DBURIType. Let’s now go over a few examples of more advanced XML using the SQL functions XMLTable and XMLQuery, Oracle’s native support for XQuery.

SQL functions XMLQuery and XMLTable are defined by the SQL/XML standard as a general interface between the SQL and XQuery languages. XMLQuery and XMLTable let […]

Here are some cool and useful things you can do with XML using pure SQL in an Oracle database.

HTTPURIType

The HTTPURIType provides support for the HTTP protocol. It uses the UTL_HTTP package underneath to access the HTTP URLs. With HTTPURIType, you can create objects that represent links to remote Web pages (or files) and retrieve those […]

David Aldridge’s post about OVERLAPS caught my attention. I did not know that you could do something like this in Oracle:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 12 22:06:24 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect eddie/awad Connected. SQL> SELECT * 2 FROM dual 3 […]

Amazing what you can do with ANYTYPE, ANYDATA, ANYDATASET, DBMS_TYPES, DBMS_SQL and pipelined table functions implemented using the interface approach. You can end up with a dynamic sql mashup like you’ve never seen before, and pivot like you’ve never pivoted before. Of course, if you’re on Oracle DB 10g or below, you can never pivot […]

Participation in the Obfuscated SQL Code Contest expired on April 1st. The SQL code that was submitted was not only obfuscated but also beautiful and artful.

When the contest started, I thought that we could just vote on the entries and select the most popular as the winner. But after seeing the entries, I believe that […]

The document below contains patterns of bad SQL and PL/SQL code that Gojko Adzic has repeatedly found in various applications and databases. Some of the bad practices include:

Use of WHEN OTHERS in exception handling. Embedding complex SQL inside PL/SQL code. Poor PL/SQL error handling. Hardcoding the size of PL/SQL variables. Not using bind variables. Storing ROWIDs for later reference. Storing […]

Do you think there is a point in arranging an Obfuscated SQL Contest, modeled after the infamous Obfuscated C Contest? Chen Shapira asked. I replied: Great idea! So, welcome to the first ever Oracle Community contest.

To learn more, or to participate, proceed to this page on OracleCommunity.net.

As mentioned on OracleCommunity.net, Oracle SQL Developer 1.5 Early Adopter 1 (EA1) is now available for download. As with any new release, there are new features and bug fixes. But, they are not yet detailed in the release notes.

However, I know that the one feature that I have been eagerly waiting for is finally here, […]

That’s what Stephane Faroult claims in the third part of his three part video series about SQL database performance best practices (shown below). Do you agree with him?

For sure, there exist young database developers whose level of expertise is still very shallow. But, isn’t that how we all started? As you grow older and learn […]