Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Archive for May 2008

PL/SQL NDS Reference Cursor with Record Collection

leave a comment »


Somebody got to this blog looking for a way to write a Native Dynamic SQL (NDS) statement that returned a system reference cursor. I created an image file to show how to do that. I borrowed the code from Chapter 11 of Oracle Database 11g PL/SQL Programming. The only problem with this is that it’s a PL/SQL only solution typically. You can write it as a function or procedure, and then use a Java or PHP program to display the reference cursor in a web page. You can check this previous post for a PHP example.

 Native Dynamic SQL Reference Cursor

Written by maclochlainn

May 31, 2008 at 8:19 pm

Posted in Oracle

Tagged with , ,

Leap year, last day of the month the easy way?

leave a comment »


You could write an ugly SQL statement full of CASE statements to derive the leap year moving forward or backward in time. You could likewise write an uglier statement that allows for moving back from a 31 day month to a 30 day month or forward from a 30 day month to a 31 day month.

However, you can use the add_months(date_in,number_of_months), like:

Leap year back to a non-leap year (system clock set to 29-FEB-2008):

SQL> SELECT add_months(SYSDATE,-12) FROM dual;

From the last day of May to the last day of April (system clock set to 31-MAY-2008):

SQL> SELECT add_months(SYSDATE,-1) FROM dual;

From the last day of April to the last day of May (system clock set to 30-APR-2008):

SQL> SELECT add_months(SYSDATE,1) FROM dual;

You can read more here in the Oracle SQL Reference.

Written by maclochlainn

May 29, 2008 at 5:29 pm

Leap year not welcome by the TO_YMINTERVAL function

leave a comment »


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.

Written by maclochlainn

May 27, 2008 at 6:05 pm

Result cache functions disallow nested table input parameters

leave a comment »


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

Written by maclochlainn

May 25, 2008 at 11:15 pm

Unnatural outcome of natural joins

with one comment


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.

Written by maclochlainn

May 24, 2008 at 3:08 am

Reversing direction in a PL/SQL range for-loop

leave a comment »


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.

Written by maclochlainn

May 21, 2008 at 4:29 am

UTOUG web site updates links

leave a comment »


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.

Written by maclochlainn

May 13, 2008 at 4:00 am