Fibonacci numbers with Oracle WITH clause (SQL recursion)

While studying the Declaritive Programming section of Composing Programs, I stumbled accross a SQL example which calculated the fibonacci numbers using the SQL WITH clause. Since this is at work my major secret weapon to tackle complex queries I changed the example to run on Oracle SQL:

WITH compute_fib(previous, curr) AS
(
  SELECT 0, 1 FROM dual UNION ALL
  SELECT curr, previous+curr FROM compute_fib WHERE curr < 60
)
-- Oracles endless loop protection
CYCLE previous, curr SET is_cycle TO '1' DEFAULT 'Y' 

SELECT previous FROM compute_fib;

For those of you who wonder what this CYCLE expression stands for this is a good summary:

When a node is encountered for the second time, it will be included in the result set, its column value IS_CYCLE (a new column [...] with the statement above) is set to Y and the recursion stops then and there – so there will not be a second iteration starting at this node. Note however that any node where a cycle starts is included in the result set twice. [source]

This example is also introducing a nice aspect of the WITH clause I wasn't aware of so far - you can provide the column names of the subquery as arguments. See those two snippets for what I mean:

-- this is how I used to name the columns of a subquery.
-- the first SELECT of a UNION also defines the column names
WITH old_way AS
(
  SELECT 1 AS first_row, 
         2 AS second_row 
  FROM dual 
  UNION ALL
  SELECT 3, 4 FROM dual
)
SELECT * FROM old_way;


-- this is much clearer way where the subquery 
-- explicitly defines the column names
WITH new_way(first_row, second_row) AS
(
  SELECT 1, 2 FROM dual UNION ALL
  SELECT 3, 4 FROM dual
)
SELECT * FROM new_way;

Native character strings with Oracle Pro*C and C

There are times where you can't get get around the Oracle Pro*C precompiler. It allows you to access a Oracle database from within your C programs. However, having seen things like JDBC in Java, Pro*C is technology from a past century.
Yes, it's not directly a friendship between me and the precompiler. If you're allowed to introduce a third-party open source library, I highly recommend OCILIB for working with your Oracle database. It allows a more intuitive and C native way (without precompiling) to access the data.

Coming back to Pro*C. All code I came across was handling character strings with the Oracle supplied VARCHAR struct. This struct contains the actual data (data field) and it's length (len field). Before using the string one has to NULL-terminated manually like in this example:

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR string1[20];
VARCHAR string2[20];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE c cursor FOR
SELECT 'a test',
       ' an other test ' 
FROM dual;

EXEC SQL OPEN c;
EXEC SQL WHENEVER NOT FOUND DO break;

for (;;) {
    EXEC SQL FETCH c INTO :string1, :string2;

    // manually adding NULL to terminate string
    string1.arr[string1.len] = '\0';
    string2.arr[string2.len] = '\0';

    printf("..%s..\n", string1.arr);
    printf("..%s..\n", string2.arr);
}
EXEC SQL CLOSE c; 

After spending some hours with the Pro*C developers guide I discovered a more C like way to accomplish the same thing. Basically, it uses a new character map "STRING" which is either set as precompiler option or set inline like in the following example:

// either as precompiler option or inline in the code
EXEC ORACLE OPTION (CHAR_MAP=STRING);

EXEC SQL BEGIN DECLARE SECTION;
char *string1[20];
char *string2[20];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE c cursor FOR
SELECT 'a test',
       ' an other test ' 
FROM dual;

EXEC SQL OPEN c;
EXEC SQL WHENEVER NOT FOUND DO break;

for (;;) {
    EXEC SQL FETCH c INTO :string1, :string2;
    printf("..%s..\n", string1);
    printf("..%s..\n", string2);
}
EXEC SQL CLOSE c;

Both examples do the same thing, the last one reliefs you from dealing with NULL termination of strings yourself.

Cobol in 1984

I was just browsing through the Oracle C/C++ precompiler files which come with the standard database installation. In the header directory there is a file named "sqlca.cob" which is obviously some kind of COBOL prototypes or however it is called in "their" world.

The reason why I want that to share this with you is the date stamp in the file's header (12/06/84).  Looking at the pace of how technology comes and goes these days it's just nice to see that apparently the host people are not impressed by that at all. Would be interesting if Clare is still with Oracle ;-)