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

The goals I wanted to achieve


  • Excel, as a result of a select, the text of which the procedure recognizes only in runtime
  • The selection before modification is modified in accordance with the parameters that the procedure receives
  • The parameters of the file to be created are passed to the procedure.
  • Ability to get the resulting file in Excel Workbook, CSV, HTML, XML

The stored PL / SQL procedure gets in the parameters
  • select text
  • Excel file options
  • execution options
and creates a full-fledged Excel with several tables (sheets).
I know there are Crystal Reports and Oracle BI Publisher.
But, firstly, 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 rather narrow task of creating a file without layout.

In the end, I wrote one PL / SQL package, which is located in the database and can be called from any application. In the course of writing, I encountered many limitations and I want to talk about how I overcome them.

For those who are in doubt, I certainly couldn’t know this beforehand, but for the several years that the package works in a large company, I had no problem bringing RDF of any complexity, with many triggers / formulas, into one select, praise Oraklu. On the contrary, since the select is a string parameter and it can be built dynamically, this gives great flexibility. You can even specify a table name in the parameters.

First of all, do not judge strictly for the abundance of Englishisms (so, in my opinion, this is called), I just have long been outside the Russian program community and do not know what these words replace.

Very often, applications written in Oracle Forms / Reports use Oracle * Reports to create an Excel file, because there it is possible to use parameters and modify the selection before it is executed. Then they write the output to a file in the trigger at the output line level. It turns out the csv file. Well, you can and so, of course.
If you need to create pdf together with Excel, then you won’t get anywhere, use Reports and don’t complain how you got this program. But often you only need Excel and you don’t feel like fussing RDF for this.

So to the point.
File options
Everything is simple here,
 ltr/rtl  Y/NFILES|SHEETSY/N

About this set. I think everything is clear here. A few words:

LIMIT_ROWS, LIMIT_LEN allow you to divide the resulting file during the creation process as you reach the limit values ​​into several Excel correct ones.

LITERAL_PARAMS talks about how to use execution parameters - insert values ​​or perform a selection in dbms_sql with dbms_sql.bind_variable.

OUT_TYPE defines the format: Excel Workbook, CSV, HTML, XML


How to provide dynamic select with parameters obtained in runtime


Execution options
We pass the parameters like this:
 [ALPHANUMERIC|CHAR|DATE|NUMBER|AS_IS] [Date format]
    
              ..
  



Pre-Execution Language

We 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.
  • We compile the text of the select.
  • We submit it to 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)
/*! [{]operand1[}] [ [{]operand2[}][{]operand3[}] ] ; !*/

The first word is hint, which defines the
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 a command in the text if one of the operands requires execution - I execute it as select (expression) from dual or as a PL / SQL block in execute immediate and replace the entire command with the result of the execution.
for instance
/*! VAR :Max_salary_dep number {select department_id
                                  from (select ee.department_id,
                                               sum(ee.salary)
                                          from employee ee
                   /*! IS_NOT_NULL :emp_id {where ee.employee_id = :emp_id}; !*/
                                         group  by ee.department_id
                                         order by sum(ee.salary) desc)
                                 where rownum = 1} !*/
   /*! VAR :Debug_print char    'Y' ; !*/
  select e.first_name "First Name",
         e.last_name,
         d.name "Department  name",
         j.function,e.hire_date,e.salary,e.commission
 /*! IS_NOT_NULL :loc_id {,l.regional_group}; !*/
   from department d,employee e,job j
 /*! IS_NOT_NULL :loc_id {,loc l}; !*/
  where e.department_id=d.department_id
    and e.job_id=j.job_id
 /*! IS_NOT_NULL :loc_id { and l.loc_id=d.loc_id}; !*/
 /*! IS_NOT_NULL :hire_date { and hire_date >= :hire_date}; !*/
 /*! IS_NOT_NULL :function { and j.function=upper(:function)}; !*/
 /*! IIF_EXPR {:dep_id is not null} {and d.department_id = :dep_id}
                                 {and d.department_id = :Max_salary_dep}; !*/
 /*! IS_NOT_NULL :emp_id {and employee_id = :emp_id}; !*/
 /*! ROW_SUM {Total row} F G; !*/
 /*! BOTTOM_SUM Total F G /*! IS_NOT_NULL :loc_id I ; !*/
 /*! IS_NULL :loc_id H; !*/ ; !*/ 


Depending on the values ​​passed, you can get all sorts of selects
 select e.first_name "First Name",
         e.last_name,
         d.name "Department name",
         j.function,e.hire_date,e.salary,e.commission
   from department d,employee e,job j
  where e.department_id=d.department_id
    and e.job_id=j.job_id
and d.department_id = 20 

select e.first_name "First Name",
         e.last_name,
         d.name "Department  name",
         j.function,e.hire_date,e.salary,e.commission
  ,l.regional_group
   from department d,employee e,job j
  ,loc l
  where e.department_id=d.department_id
    and e.job_id=j.job_id
  and l.loc_id=d.loc_id
  and hire_date >= to_date('1985-09-08','yyyy-mm-dd')
  and j.function=upper('SALESPERSON')
 and d.department_id = 30 

or
 select e.first_name "First Name",
         e.last_name,
         d.name "Department name",
         j.function,e.hire_date,e.salary,e.commission
   from department d,employee e,job j
  where e.department_id=d.department_id
    and e.job_id=j.job_id
  and hire_date >= to_date('1985-09-08','yyyy-mm-dd')
  and j.function=upper('SALESPERSON')
 and d.department_id = 30 
and so on ...

I described this so that you believe that these techniques allow you to write truly effective selects.
None "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 gained, and which certainly is not needed for someone who did not go on the boundaries of what is permissible in Oracle. 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 a select in Oracle is also 4000 bytes.
Everybody knows
Or maybe I'm wrong, maybe it's just I did not know.

We got the select after precompilation with the execution parameters. We have it in the variable l_Stmt.
Unfortunately, in PL / SQL there is no easy way to organize a loop over 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, according to which we will loop in the future.
What do we want to do?
Parsing and getting a list of fields with datatype.
For this we apply
dbms_sql.parse
    l_CursorId := dbms_sql.open_cursor;
    begin
      dbms_sql.parse(l_CursorId, substr('select * from (' || l_Stmt || ')', 1, 32765),1);
    exception
      when others then
        v_Msg := '--After parse: ' || sqlerrm;
        put_str_to_output(substr('select * from (' || l_Stmt || ')',1,32765));
        raise ParsingException;
    end;

dbms_sql.describe_columns
    begin
      dbms_sql.describe_columns(l_CursorId, l_ColumnCnt, l_LogColumnTblInit);
    exception
      when others then
        v_Msg := '--After describe_columns: ' || sqlerrm;
        put_str_to_output(substr('select * from (' || l_Stmt || ')', 1, 32765));
        raise ParsingException;
    end;

We got the most important thing - a list of select fields in the PL / SQL table l_LogColumnTblInit.
This has done the great DBMS_SQL package for us. Now we can organize a cycle through the fields of the select.
Anyone who writes in Java (including me now) will laugh at such a victory, there it was always there - enumeration of fields in PreparedStatement.
Now, knowing Java, I would write, maybe in a different way, but the fundamental things would not have changed.
By the way, here I met a restriction on the size of the 32K select, not immediately, during operation, when serious selections began to be written. And here again I was pleased with Oracle. It turns out that a long selection can be divided into portions of 256 bytes, uploaded to the PL / SQL table l_LongSelectStmt dbms_sql.varchar2s, and passed the dbms_sql.parse version to overload.
        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 selection 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 ''||a||''||b||'' 
          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 works there.
There are CSS, definitions of Workbook, Worksheet, table headers. I will not go deep, it is not very difficult to understand if you have met HTML before.
In Excel Workbook, the output line will look something like this
 select ''||a|| ''||
                         ''||b||''||
                         ''||c||''
  from ( select a,b,c
                from table2
          )

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

Work algorithm


Field Cycle

Moving along the table of output fields, we wrap the next field in the tags corresponding to the format or just add a tab (CSV). Now you understand how I hit these 4,000 bytes. I had to check the length of the result before merging the lines and, if it was more than 4000, then I started a new output field, something like this:
 select ''||a|| ''||
                         ''||b||'' а1,
             ''||c||'' а2
  from ( select a,b,c
                from table2
          )

When the select is built, 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 will 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 (beautifully, Excel correctly) and start the next one.
At the end, or, if it is Excel Workbook in a separate sheet, we display the parameters with which the report was executed.

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

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

Also popular now: