Creating an Excel file from a select with parameters using pure PL / SQL, as an alternative to Oracle * Reports

Goals I wanted to achieve

• Excel, as a result of a select, whose text the procedure only learns in runtime
• The select before the modification is changed in accordance with the parameters that the procedure receives
• The procedure is passed the parameters of the file to be created.
• The ability to get the resulting file in Excel Workbook, CSV, HTML, XML

Stored PL / SQL procedure gets in parameters

• select text
• Excel file options
• execution parameters

and creates a full-fledged Excel with several tables (sheets).
I know that there are Crystal Reports and Oracle BI Publisher.
But, first of all, these are large products (with high prices ...), and Publisher, as far as I know, does not work as a separate module without Oracle Business Intelligence Enterprise Edition. And besides, it was a fairly narrow task of creating a file without layout.

At the end, I wrote one PL / SQL package that is in the database and can be called from any application. In the course of writing, I was faced with many restrictions and I want to talk about how to overcome them.

For those who doubt, I certainly could not have known this in advance, but for several years that the package has been working in a large company, I have not had problems reducing RDF of any complexity, with many triggers / formulas, in one select, praise Oraklu. On the contrary, since the select is a string parameter and can be built dynamically, this gives greater flexibility. In the parameters you can even set the name of the table.

First of all, do not judge strictly for the abundance of anglicisms (so, in my opinion, this is called), I have just been outside the Russian software community for a long time and I don’t know what these words are replacing.
')
Very often, applications written in Oracle Forms / Reports use Oracle * Reports to create an Excel file, because it is possible to use parameters and modify the selections before it is executed. Then in the trigger at the level of the output line write the output to the file. It turns out the csv file. Well, you can, of course.
If together with Excel you need to create a pdf, then there is no getting around, use Reports and do not complain about how this program got you. But after all, often you just need Excel and you don’t feel like dodging for this RDF.

So, to the point.

How to ensure the dynamism of the select with the parameters obtained in runtime

Pre-fulfillment language

You need a certain language in which you can write instructions on what to do depending on the values ​​of the execution parameters.

• We get the execution parameters.
• Compile the select text.
• Serve it for the next step.

Here is the language that at the end covered all my needs.

In the select text it looks like a comment (hint)

 /*!<HINT> [{]operand1[}] [ [{]operand2[}][{]operand3[}] ] ; !*/ 

The first word is hint, which defines the command
VAR CHAR | NUMBER | AS_IS | DATE [date format] {PL / SQL expression};
IF_CONTINUE {PL / SQL expression}
IF_EXECUTE {PL / SQL expression}
EXPR {PL / SQL expression};
IIF_EXPR {boolean expression} {String if true} {String if false};
IS_NOT_NULL {Bind variable} {String if Bind variable is not null};
IS_NULL {Bind variable} {String if Bind variable is null};
BOTTOM_SUM {Total bottom title} BC ... Z;
ROW_SUM {Total column title} BCD ... Z;
BEFORE {PL / SQL block};
TITLE {Title};
ALIAS {column_name} {alias};
AFTER {PL / SQL block};

The compilation step is that I find the command in the text, if one of the operands needs to be executed - I do it as a select (expression) from dual or as a PL / SQL block in execute immediate and replace the whole command with the result of the execution.


I described this in order for you to believe that these techniques allow you to write really effective selects.
No "and (: param1 is null or table_field =: param1)"

Parsing and execution

For the sake of this section, I started writing this article. Here I will write about the experience that I have acquired, and which certainly is not needed by someone who did not go on the limits of acceptable in Orakle. For example, everyone knows that the maximum length of a text field in a table is 4000, but how many people know that the limit for concatenating a string field in an oracle is also 4000 bytes.

Got select after precompilation with execution parameters. We have it in the variable l_Stmt.
Unfortunately, in PL / SQL there is no easy way to loop through select fields, as it could be done in Java. We will use the dbms_sql.parse procedure, which returns the select fields as a table, using which we will make a cycle in the future.
What do we want to do?
Perform parsing and get a list of fields with datatype.
For this we use


We got the most important thing - the list of select fields in the PL / SQL table l_LogColumnTblInit.
This is a great DBMS_SQL package for us. Now we can organize a cycle on the select fields.
Anyone who writes in Java (including me today) will laugh at such a victory, there it has always been - the search of fields in PreparedStatement.
Now, knowing Java, I would write, maybe differently, but the basic things would not change.
By the way, here I met a limit on the size of 32K, not immediately, during operation, when they began to write serious selections. And here I was again pleased Orakl. It turns out that a long select can be divided into 256 byte portions, loaded into PL / SQL with the l_LongSelectStmt dbms_sql.varchar2s table and transferred to overload the dbms_sql.parse version.

  begin dbms_sql.parse(l_CursorId ,l_LongSelectStmt ,1 ,l_LongSelectStmt.count ,false ,1); exception when others then v_Msg := '--After parse long 2: ' || sqlerrm; raise ParsingException; end; 

Now it's time to think about output formats.
Let's say our select looks like this:

  select a,b from table1 where ... 

To output in CSV format you need to write

  select a||chr(9)||b from( select a,b from table1 where ... ) 

To output in HTML format you need to write

  select '<tr><td>'||a||'</td><td>'||b||'</td></tr>' from( select a,b from table1 where ... ) 

To create the most beautiful, but also the most complex Excel Workbook format, I had to experiment with Excel. Excel Workbook is not a binary, but a text file, you can look at it and understand how everything is arranged there.
There are CSS, Workbook definitions, Worksheet, table headers. I will not go deep, it is not very difficult to understand if you have met before HTML.
In Excel Workbook, the output line will look something like this.

  select '<Row> <Cell ss:StyleID="s24"><Data ss:Type="String">'||a|| '</Data></Cell>'|| '<Cell ss:StyleID="s25"><Data ss:Type="DateTime">'||b||'</Data></Cell>'|| '<Cell ss:StyleID="s24"><Data ss:Type="String">'||c||'</Data></Cell></Row>' from ( select a,b,c from table2 ) 

Here, as you can see, knowledge of data types from a virtual table obtained in dbms_sql.describe_columns may be useful to us .
If we compare the types of output, we can say the following:
CSV - small in size (this is a plus), ugly, there is no possibility of several tables (spreadshhets)
HTML - medium in size, quite sexy, there is no possibility of several tables
Excel Workbook - a large file, beautiful, you can create multiple tables

Work algorithm

Cycle through fields

Moving on the table of output fields, we wrap the next field in the appropriate format tags or simply add tabulation (CSV). Now you understand how I hit about these 4000 bytes. I had to check the length of the result before merging the lines and, if it was more than 4000, I started a new output field, like this:

  select '<Row> <Cell ss:StyleID="s24"><Data ss:Type="String">'||a|| '</Data></Cell>'|| '<Cell ss:StyleID="s25"><Data ss:Type="DateTime">'||b||'</Data></Cell>' 1, '<Cell ss:StyleID="s24"><Data ss:Type="String">'||c||'</Data></Cell></Row>' 2 from ( select a,b,c from table2 ) 

When is built, we execute it. If the select is large, then it must be loaded into the dbms_sql.varchar2s table and executed in dbms_sql. If your DBA says that it does not tolerate literals and requires that the parameters be bind variable, then you also need to use dbms_sql with dbms_sql.bind_variable.
Otherwise, if your select fits in 32K of your varchar2 variable l_Stmt you can open the ref cursor:

  begin open l_RefCursor for l_Stmt; exception when others then v_Msg := '--After open: ' || sqlerrm; raise ParsingException; end; 

Cursor cycle

We do fetch and write in utl_file. We monitor the number of lines and the size of the output file, if necessary, complete it (nicely, Excel correctly) and start the next one.
At the end, or, if it is an Excel Workbook in a separate sheet, we display the parameters with which the report was executed.

Well, probably all by and large.
Probably now you can show the result:


If anyone is interested, I can tell you how I wrapped this package in another one, which ziped the file, if it was large, sent it by mail as a link or as an attachment, but the main thing is the parameter definitions and the typical input screen.