Oracle: Turning a select statement into an Excel file

I don't know how many times a CFO has approached me and asked I know, the data for XYZ is somewhere in the database. Could you quickly get me XYZ and send it to me as Excel.

Usually, getting the data out of the database was fairly easy with a SQL select statement, yet, bringing the data into an excel worksheet was sort of a recurring PITA: I would start SQL Developer, execute the select statement, copy the result set (ctrl-c), open Excel, paste the result set (ctrl-v) then I'd adjust the widths of the columns, and only then I'd save the resulting excel sheet.

Not that these steps are too hard, but I always felt that should be easier. So, I have written the procedure xlsx_writer.sql_to_xlsx. This procedure takes an SQL statement and the name of an Excel file to be written, executes the SQL statement and writes the Excel file.

In SQL*Plus, that would look like:

Of course, this can be written in one line, I have used four lines because of the width limit in this blog.

Links

Source code on github

xlsx_writer on my homepage

Paginating SQL queries with Oracle 12c

Oracle 12c not only allows to create TOP N queries with an understandable syntax, the row limiting clause also makes it possible to paginate a result set:

create table tq84_table ( id number, txt varchar2(10) ); insert into tq84_table values ( 2, 'two' ); insert into tq84_table values ( 1, 'one' ); insert into tq84_table values ( 6, 'six' ); insert into tq84_table values ( 8, 'eight'); insert into tq84_table values ( 3, 'three'); insert into tq84_table values ( 7, 'seven'); insert into tq84_table values ( 4, 'four' ); insert into tq84_table values ( 9, 'nine' ); insert into tq84_table values (10, 'ten' ); insert into tq84_table values ( 5, 'five' );

select id, txt from tq84_table order by id offset 4 rows -- skip first 4 records fetch next 3 rows only -- fetch next 3 records ;

returns

ID TXT ---------- ---------- 5 five 6 six 7 seven

Links

SQL statement on github

TOP N select queries with Oracle 12c

With Oracle 12c, it's possible, finally, to do TOP n select queries with an easily understandable syntax.

Here's a table to demonstrate it:

create table tq84_table ( id number, txt varchar2(10) ); insert into tq84_table values ( 2, 'two' ); insert into tq84_table values ( 1, 'one' ); insert into tq84_table values ( 6, 'six' ); insert into tq84_table values ( 8, 'eight'); insert into tq84_table values ( 3, 'three'); insert into tq84_table values ( 7, 'seven'); insert into tq84_table values ( 4, 'four' ); insert into tq84_table values ( 0, 'zero' ); insert into tq84_table values ( 9, 'nine' ); insert into tq84_table values (10, 'ten' ); insert into tq84_table values ( 5, 'five' ); commit;

Now, a select statement with fetch first row only:

select id, txt from tq84_table order by id fetch first row only;

selects

ID TXT ---------- ---------- 0 zero

Links

Paginating SQL queries with Oracle 12c

SQL on github

Little things that make live easier #2: sys.ora_mining_number_nt

With Oracle, sys.ora_mining_number can be used to turn a set of numbers to a result set:

SQL> select * from table(sys.ora_mining_number_nt(4, 20, 15)); COLUMN_VALUE ------------ 4 20 15

Why is there no == operator in SQL?

Why is there no == operator in SQL that yields true if both operands are either null or have the same value?

Here's the truth table for the = operator:

=	42	13	null
42	true	false	null
13	false	true	null
null	null	null	null

This has some implications. The statement

select * from t where col1 = col2

won't return a record where both col1 and col2 are null.

I suspect that in most cases this is not what the author of such a statement wants. Therefore, they will rewrite the query so:

select * from t where ( a is null and b is null) or ( a = b)

Now, if there were a == operator with this truth table:

==	42	13	null
42	true	false	false
13	false	true	false
null	false	false	true

it would definitely make my life easier (and would not cost the database companies too much money to implement).

Maybe I am all wrong and there is such a thing somewhere. If you know of such an operater in any database product, please let me know!

Oh that pesky Oracle outer join symbol (+)

Here are three tables, named A, A2Z and Z that I want to outer join from left to right:

The first table, A, contains the (primary keys) 1 through 7. I want my select to return each of these, that's why I use an outer join. A's column i is outer joined to A2Z's column ia:
A.i = A2Z.ia (+).
The (+) symbol indicates that a record should be returned even if there is no matching record. Note, the (+) is on the side of the = where "missing" records are expected.

Now, the records in A2Z should be joined to Z if A2Z's column flg is equal to y (colored green in the graphic above). For example, for the 1 in A, I expected the query to return the a in Z, for the 2 in A I expect no matching record in Z since the corresponding flg is either null or not equal to y.
This requirement can be implemented with a
A2Z.flg (+) = 'y'
Note, the (+) is on the side where missing records (or null values) are expected. Since y is neither missing nor null, it goes to the other side.

Finally, A2Z needs to be joined to Z:
A2Z.iz = Z.i (+)

Complete SQL Script

create table A (i number(1) primary key); create table Z (i char (1) primary key); create table A2Z ( ia not null references A, flg char(1) not null, iz char(1) not null ); insert into A values (1); insert into A values (2); insert into A values (3); insert into A values (4); insert into A values (5); insert into A values (6); insert into A values (7); insert into Z values ('a'); insert into Z values ('b'); insert into Z values ('c'); insert into Z values ('d'); insert into A2Z values (1, 'y', 'a' ); insert into A2Z values (1, 'n', 'b' ); insert into A2Z values (2,null, 'c' ); insert into A2Z values (2, 'q', 'd' ); insert into A2Z values (3, 'y', 'e' ); insert into A2Z values (4, , 'f' ); insert into A2Z values (5, 'y', null); insert into A2Z values (6, 'v', null); select A.i a_i, Z.i z_i from A, A2Z, Z where A.i = A2Z.ia (+) and A2Z.flg (+) = 'y' and A2Z.iz = Z.i (+) order by A.i; drop table A2Z purge; drop table Z purge; drop table A purge;

When run, the select returns the following records:

       A_I Z
---------- -
         1 a
         2
         3
         4
         5
         6
         7

Links

Source code on github