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

Writing a blob into a file with PL/SQL with a single line of code.

I have updated my blob_wrapper so that it can write a blob with a single line:

blob_wrapper.to_file('c:\temp\abc.txt', my_blob);

This creates the file c:\temp\abc.txt and fills it with the content of my_blob

A varchar2 can be converted into a blob and then written into the file like so

begin blob_wrapper.to_file( 'c:\temp\two_params.txt', utl_raw.cast_to_raw('foo bar baz') ); end; /

Programming PL/SQL almost makes fun again.

Creating Excel (.xlsx) files with Oracle PL/SQL

The XLSX Excel file format is actually a zipped archive of some xml files with the suffix .xlsx rather than .zip This makes it possible to create XLSX files with (almost) any programming language that can create files and zip them. Since I am working with PL/SQL every now and then and I needed to create xlsx files for a reporting solution, I crated a small library: XLSX writer for Oracle. The source code for this library is on this github repository, some examples are on my homepage.

Using Apophysis to create background images

With Apophysis, it is possible to create wallpapers or background images such as the following image:

I have produced this image with a variation of this script. Unfortunatly, I lost the script that created the picture above, but this script sort of comes close.

Whether you like it or not, no one should ever claim to be a data analyst until he or she has done string manipulation.

I am reading Gaston Sanchez' book Handling and Processing Strings in R (pdf).

In the preface, I found the following quote, to which I wholeheartedly agree:

Perhaps even worse is the not so uncommon believe that string manipulation is a secondary non-relevant task. People will be impressed and will admire you for any kind of fancy model, sophisticated algorithms, and black-box methods that you get to apply. Everybody loves the haute cuisine of data analysis and the top notch analytics. But when it comes to processing and manipulating strings, many will think of it as washing the dishes or pealing and cutting potatos. If you want to be perceived as a data chef, you may be tempted to think that you shouldn’t waste your time in those boring tasks of manipulating strings. Yes, it is true that you won’t get a Michelin star for processing character data. But you would hardly become a good data cook if you don’t get your hands dirty with string manipulation. And to be honest, it’s not always that boring. Whether you like it or not, no one should ever claim to be a data analyst until he or she has done string manipulation.

Little things that make life easier #9: Using data.entry in r

With data.entry(), it's easy to visually fill (small) matrices in r.

Let's see it in action. First, I create a 4x3 matrix:

mx <- matrix(nrow=4, ncol=3) show(mx)

[,1] [,2] [,3] [1,] NA NA NA [2,] NA NA NA [3,] NA NA NA [4,] NA NA NA

The matrix is created with the cells' values being NA Now, in order to assign values to these cells, I use

data.entry(mx)

This opens a small window where I can enter the data.
This is how the cells were filled before my editing them:

And here's how they looked after my editing them just before I used File > Close:

Back in the shell, the matrix has indeed changed its values:

show(mx)

var1 var2 var3 [1,] 2 4 2 [2,] 12345 8 42 [3,] 5 6 489 [4,] 9 22 11

Pretty cool, imho.

Is a sequence incremented in a failed insert?

Here's a sequence

create sequence tq84_failed_insert_seq start with 1 increment by 1;

And an insert statement:

insert into tq84_failed_insert values( tq84_failed_insert_seq.nextval, lpad('-', i, '-') );

If the insert statement fails, is the sequence still incremented?

Let's try it with a test. The table:

create table tq84_failed_insert( i number primary key, j varchar2(20) );

Some insert statements:

insert into tq84_failed_insert values (5, lpad('-', 5, '-')); insert into tq84_failed_insert values (9, lpad('-', 9, '-'));

and an anonymous block:

begin for i in 1 .. 10 loop begin insert into tq84_failed_insert values( tq84_failed_insert_seq.nextval, lpad('-', i, '-') ); exception when dup_val_on_index then null; end; end loop; end; /

After running this anonymous block, the table contains:

select * from tq84_failed_insert order by i;

Returning:

I J ---------- -------------------- 1 - 2 -- 3 --- 4 ---- 5 ----- 6 ------ 7 ------- 8 -------- 9 --------- 10 ----------

So, the value of I is ascending in steps of 1, showing that the value of netxtval is "wasted" if the insert statement fails.

Links

Source code on github