MacLochlainns Weblog

Playing around with illustrating date math, I noticed that Oracle 11g still hasn’t fixed the TO_YMINTERVAL function. The function should let you to subtract months, years, or a combination of month and years from any date. However, it doesn’t like leap years or going from the last day of a longer month to the last day of a shorter month.

You can add or subtract a year from a date in the middle of the month:

SELECT TO_DATE('15-JAN-08') - TO_YMINTERVAL('01-00') FROM dual;

You can also add or subtract a month from a date in the middle of the month:

SELECT TO_DATE('15-FEB-08') - TO_YMINTERVAL('00-01') FROM dual;

You can’t subtract a year from the last day of February in a leap year. There is also no logic to let you add a year to 28-FEB-07 and get 29-FEB-08 (like a Boolean for last_day). Likewise, you can’t add a month to the last day of a month where the next month has fewer days in it; or subtract a month from the last day of a month where the last day is greater than the prior months last day without raising an ORA-01839 error.

Here’s what happens subtracting a year from this years leap day:

SELECT TO_DATE('29-FEB-08') - TO_YMINTERVAL('01-00') FROM dual
                            *
ERROR at line 1:
ORA-01839: DATE NOT valid FOR MONTH specified

You can’t complain about it though, it’s apparently compliant with the SQL92 standard according to metalink.

If you implement street address as a nested table (or collection), the problem is printing an address book using only a SQL statement. The problem comes from matching up the multiple line return from the TABLE function with the rest of the row. That’s not a problem when you denormalized the list into known columns.

A PL/SQL function can convert the list into a scalar value. It’s an easy way to leverage a nested table. You can find the full code here. You might think that this is a neat opportunity to use a result cache function. I did, but the behavior isn’t presently supported. On compilation of a function with the RESULT_CACHE clause and a scalar collection input parameter, you raise a PLS-00999 error.

The message tells you:

RESULT_CACHE IS disallowed ON subprograms WITH IN parameter OF (OR containing) nested TABLE TYPE

You may like the standard syntax of listing tables as comma-delimited lists, rather than using an inner, left, right, full, natural, and cross join syntax. I do! The former method eliminates matching result sets and generally is less tedious.

The natural join is my least favorite of these because it can create problems long after the query is written. The problem is an outcome of its design. A natural join matches rows from one table to another by using all matching column names. This query makes a simplifying assumption: Nobody includes a column of the same name in two tables where they may be joined that isn’t a foreign key.

The following is quick example that creates two tables. The VEHICLE table uses the VEHICLE_ID column as a primary key. The WORK_ORDER table uses the VEHICLE_ID column as a foreign key.

CREATE TABLE vehicle
( vehicle_id NUMBER
, vin VARCHAR2(20));
 
CREATE TABLE work_order
( work_order_id NUMBER
, vehicle_id NUMBER);
 
INSERT INTO vehicle VALUES (1,'VIN_NUMBER1');
INSERT INTO vehicle VALUES (2,'VIN_NUMBER2');
INSERT INTO work_order VALUES (1, 1);
INSERT INTO work_order VALUES (2, 2);
INSERT INTO work_order VALUES (3, 1);

The following queries resolve the relationship by using an explicit join:

SELECT * FROM vehicle v, work_order wo WHERE v.vehicle_id = wo.vehicle_id;
SELECT * FROM vehicle v JOIN work_order wo ON v.vehicle_id = wo.vehicle_id;
SELECT * FROM vehicle v JOIN work_order wo USING(vehicle_id);

A natural query also resolves the relationship by implicitly finding the columns that share column names, as follows:

SELECT * FROM vehicle v NATURAL JOIN work_order wo;

All of these return a three row result set. The unnatural part of the natural query arrives during maintenance programming when somebody adds a VIN column name to the WORK_ORDER table (let’s say to simplify the ETL to the data warehouse). The code using a natural join isn’t invalidated but its runtime behavior is altered because it now joins both the VEHICLE_ID and VIN columns. Continuing the example, you’d change the table by:

ALTER TABLE work_order ADD (vin VARCHAR2(20));

The explicit queries still work after the change. The implicit (natural) query now returns no rows because the VIN column contains all null values, and it is automatically added to the implicit join statement. After updating the VIN column, the query resolves when the data matches but not when the VIN column contains different data. What if the VIN column in WORK_ORDER only contains the make and model portion of the vehicle identification number while the VIN column in VEHICLE contains the complete unique vehicle identifier. You would receive no rows selected from the natural query.

It appears the best thing to do, is avoid natural joins.

I subsequently wrote a post about SQL join semantics. You can find it here.

Have you ever wondered how to decrement index values in a PL/SQL range for-loop. You’ve probably heard that you can’t. That’s part true and part false.

The true part is two fold. First, range for-loops move forward from a starting low value to high value. Second, the loop exits immediately when your starting value is high and ending value low. If you use the REVERSE keyword, the loop moves backward from an ending point to the starting point. This happens because a PL/SQL range for-loop is a guard on entry loop. The starting value is compared against the ending value and exits when the starting value is greater than the ending value.

The false part happens when you want to traverse a collection backward. Oracle collections are varrays, tables, or associative arrays. Associative arrays are also known as PL/SQL tables. You overcome the ascending only limitation by using the REVERSE keyword or leveraging the Oracle Collection API library functions.

This code works when the index is numeric:

DECLARE
  TYPE collection IS TABLE OF VARCHAR2(20);
  fellowship COLLECTION := collection('Frodo','Gandalf','Aragorn');
BEGIN
  -- Increasing iterator values.
  FOR i IN REVERSE 1..fellowship.COUNT LOOP
    DBMS_OUTPUT.put('Iterator ['||i||'] ');
    DBMS_OUTPUT.put('Value ['||fellowship(i)||']');
    DBMS_OUTPUT.new_line();
  END LOOP;
END;
/

This code works when the index is a string in an associative array:

DECLARE
  CURRENT VARCHAR2(8);
  element VARCHAR2(20);
  TYPE collection IS TABLE OF VARCHAR2(20)
    INDEX BY VARCHAR2(8);
  fellowship COLLECTION;
BEGIN
  -- Assign character indexed values.
  fellowship('Hobbit') := 'Frodo';
  fellowship('Wizard') := 'Gandalf';
  fellowship('Human')  := 'Aragorn';
  -- Increasing iterator values.
  FOR i IN 1..fellowship.COUNT LOOP
    IF i = 1 THEN -- Set starting point and assign value.
      CURRENT := fellowship.LAST;
      element := fellowship(CURRENT);
    ELSE -- Change value until you read the first element.
      IF fellowship.PRIOR(CURRENT) IS NOT NULL THEN
        CURRENT := fellowship.PRIOR(CURRENT);
        element := fellowship(CURRENT);
      ELSE -- Exit when all elements read.
        EXIT;
      END IF;
    END IF;
    DBMS_OUTPUT.put('Iterator ['||i||'] ');
    DBMS_OUTPUT.put('Index ['||CURRENT||'] ');
    DBMS_OUTPUT.put('Value ['||element||']');
    DBMS_OUTPUT.new_line();
  END LOOP;
END;
/

Chris Neumüller suggested another example with a WHILE loop:

DECLARE
  TYPE collection IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(8);
  fellowship COLLECTION;
  CURRENT    VARCHAR2(20);
BEGIN
  -- Assign character indexed values.
  fellowship('Hobbit') := 'Frodo';
  fellowship('Wizard') := 'Gandalf';
  fellowship('Human')  := 'Aragorn';
  -- Increasing iterator values.
  IF fellowship.COUNT > 0 THEN
    CURRENT := fellowship.LAST;
    WHILE CURRENT IS NOT NULL LOOP
      DBMS_OUTPUT.put('Iterator ['||CURRENT||'] ');
      DBMS_OUTPUT.put('Value ['||fellowship(CURRENT)||'] ');
      DBMS_OUTPUT.new_line();
      CURRENT := fellowship.PRIOR(CURRENT);
    END LOOP;
  END IF;
END;
/

While the former works, you should consider changing the cursor that populates the collection by adding a descending ORDER BY clause when indexing with a string. The algorithm is a bit more work but it also supports sparsely populated indexes and string-indexed associative arrays. All you need do is swap the LAST for FIRST and PRIOR for NEXT to go forward through a sparsely populated index.

There were two broken links in earlier blogs because UTOUG moved their presentation download repository. I’ve updated those links for my Utah Oracle User Group Training Days 2008 presentations. You can also uses these new links: PL/SQL 11g New Features and an Oracle LOBs. You can learn more about UTOUG and prior presentations at http://www.utoug.org, including Tom Kyte’s Materialized Views presentation from the same 2008 spring conference.

Adrian Billington wrote an excellent article on how to use the Oracle 11g PL/SQL new cross session result cache. He uses Tom Kyte’s runstats tuning kit to show the best performance advantage of this new feature. The recommendation is converting functions that perform SQL lookups to result cache functions. A result cache function’s actual parameters and results are cached only once in the SGA and available across sessions.

A result cache function can only support SQL datatypes. This limits you to returning only a scalar or collection of a scalar datatype from your function. Collections of scalar datatypes are varray or nested table user-defined types (UDT). When you try to return an aggregate table from a result cache function, Oracle 11g Release 1 raises a PLS-00999 error. The error signals an implementation restriction that may be temporary. Let’s hope it changes in Oracle 11g Release 2.

You can cheat the limitation by returning a collection of tokenized strings but I’m not sure it’s worth the effort. If you think it’s worth the effort, you can check out the code.

Pipelined functions let you retrieve record structures from PL/SQL Tables as rows in SQL statements. PL/SQL tables are also known as associative arrays from 10g forward. While pipelined functions are slower than other approaches, they are easy to implement. The alternative uses a complex query. The query is complex because it typically uses selective aggregation and nested CASE logic.

Read more …

Somebody asked me how you include a colon in a Native Dynamic SQL (NDS) statement when it’s not related to a placeholder. A colon without a placeholder raises an ORA-00947 error when parsing the statement. The error means you’ve failed to submit enough bind variables. You can substitute a CHR(58) where you need to insert the standalone colon. The NDS or DBMS_SQL parsing phase ignores a CHR(58), which translates during actual SQL statement parsing as a colon.

Let’s say you want to insert a column value with an ASIN (Amazon Standard Identification Number) code in the format: ASIN: B000VBJEEG

Using NDS, you have two choices. You can let the entry person type the full string and pass that string as a bind variable, or you can substitute CHR(58) for the colon and enter only the ASIN code. The example (Oracle Database 11g PL/SQL Programming, pp. 386-387) implements the latter:

CREATE OR REPLACE PROCEDURE insert_item
( asin VARCHAR2
, item_type VARCHAR2
, item_title VARCHAR2
, item_subtitle VARCHAR2 := ''
, rating VARCHAR2
, agency VARCHAR2
, release_date DATE ) IS
 
  -- Local variable for a dynamic SQL statement.
  stmt VARCHAR2(2000);
 
BEGIN
 
  -- Create a dynamic statement with bind variables.
  stmt := 'INSERT INTO item '
  || '( item_id'
  || ', item_barcode'
  || ', item_type'
  || ', item_title'
  || ', item_subtitle'
  || ', item_desc'
  || ', item_blob'
  || ', item_photo'
  || ', item_rating'
  || ', item_rating_agency'
  || ', item_release_date' 
  || ', created_by'
  || ', creation_date'
  || ', last_updated_by'
  || ', last_update_date ) '
  || 'VALUES '
  || '( item_s1.nextval'
  || ',''ASIN''||CHR(58)||:asin'
  || ',(SELECT common_lookup_id'
  || ' FROM common_lookup'
  || ' WHERE common_lookup_type = :item_type)'
  || ', :item_title'
  || ', :item_subtitle'
  || ', empty_clob()'
  || ', NULL, NULL'
  || ', :rating'
  || ', :agency'
  || ', :release_date'
  || ', 3, SYSDATE, 3, SYSDATE)';
 
  -- Print debug statement.
  dbms_output.put_line(stmt);
 
  -- Execute dynamic statement with bind variables.
  EXECUTE IMMEDIATE stmt
  USING asin, item_type, item_title, item_subtitle, rating, agency, release_date;
END insert_item;
/

There’s quite a nifty or nasty trick inside the dynamic SQL statement. You’ll notice that the colon is concatenated to the ASIN and a bind variable. It is critical that you don’t encapsulate the bind variable inside quotes, or you’ll raise an ORA-01006 exception (check Table 11-2 in the Oracle Database 11g PL/SQL Programming book for more detail). You can’t enclose a string in single quotes because the string is substituted as a string, and it trips up the parser.

You can test this dynamic SQL statement with the following anonymous PL/SQL block:

BEGIN
  insert_item('B00005JPO1'
             ,'DVD_WIDE_SCREEN'
             ,'Indiana Jones and the Crystal Skull'
             ,'2-Disc Special Edition'
             ,'PG-13'
             ,'MPAA'
             ,'14-OCT-08');
END;
/

While writing Oracle Database 11g PL/SQL Programming book last year, I collaborated with Lee Barney on a PHP/AJAX framework. Our Oracle Database AJAX & PHP Web Application Development book covers the framework. You can find a new and improved version at SourceForge.net. Lee maintains the code and continues to improve it. I’m sure he’d welcome suggestions and collaboration. We see it as very useful to AJAX web development, and to the Open Source community.

You can do base type and subtype comparisons with the IS OF or IS NOT OF operators. They work like the typeof operator in Java. They also eliminate null values when you use them in a WHERE clause or an IF block. They work against all SQL user-defined object types. The prototypes are:
 

object_variable IS OF (object_type1 [, object_type2, [object_type(n+1)]])
object_variable IS NOT OF (object_type1 [, object_type2, [object_type(n+1)]])

 
The left operand should be an object column or element of a collection. The IS OF returns true when the object_variable matches the object type or a member of the list of object types. The IS NOT OF returns true when the object_variable doesn’t match, and both return false when the object_variable is a null value.