Creating an Excel file from a select with parameters using pure PL / SQL
It so happened that only after I wrote the first part I realized that creating an Excel file was not the focus, although it was purely professional, it was quite difficult. The first article consisted of two parts:
To be honest, I thought that the second part is more important, for the sake of it I wrote the article.
Now I think the topic of the select language is more important.
I searched the Internet and found, without much difficulty, 6 or 7 solutions for creating an Excel file from a select. I have no doubt that there were better solutions than mine. But it turns out that without a preprocessing language it costs nothing, in my opinion. The system, which I built in many ways intuitively, is only possible if we want to replace such a platform as Oracle * Reports (of course, we are talking only about output files in various Excel formats).
It is necessary to prepare the text of the select that responds to user requests.
This is the analogy of the report (rdf) file with the query, formulas and triggers. I wrote, you can be sure, any rdf can be reduced to a single select (or, in extreme cases, in the “before” PL / SQL block, implement logic and write to a certain table and then make select * from ...).
The beauty of rdf is that it has parameters and means for modifying the query depending on the values ​​set by the user at runtime.
This means that the parameters are already specified in the stored select and there are rules ( language ) for what and how to change in the select before execution.
')
I wrote about this in the first part .
Since the parameters are now processed only before execution, it is necessary to define them in some formalized form. That is to ask
Only all together it will give a good effect, which I observed in our company. Here are examples of screens that can be written.
This is written in Oracle Forms, but you can also write it on any other platform. Two screens (Administration and Execution) and two PL / SQL packages in the database, several tables for storing data.
We almost abandoned Reports. Analysts use Excel, and we build selects on request. This can take a lot of time, and this is debugging of the select, and logic and optimization. But then, within 20 minutes we define it in the system and the user starts working with it. In my opinion, this is simpler than Business Objects or Oracle Discoverer systems. Yes, it requires the work of the programmer and DBA, but on the other hand, selects are sharpened and efficient. And about Excel, as a powerful analytical tool, I will not speak (not an expert).
- An exemplary description of the select pre-processing language
- Problems that were solved in the process of writing
To be honest, I thought that the second part is more important, for the sake of it I wrote the article.
Now I think the topic of the select language is more important.
I searched the Internet and found, without much difficulty, 6 or 7 solutions for creating an Excel file from a select. I have no doubt that there were better solutions than mine. But it turns out that without a preprocessing language it costs nothing, in my opinion. The system, which I built in many ways intuitively, is only possible if we want to replace such a platform as Oracle * Reports (of course, we are talking only about output files in various Excel formats).
It is necessary to store the text of selekt
It is necessary to prepare the text of the select that responds to user requests.
This is the analogy of the report (rdf) file with the query, formulas and triggers. I wrote, you can be sure, any rdf can be reduced to a single select (or, in extreme cases, in the “before” PL / SQL block, implement logic and write to a certain table and then make select * from ...).
Work with parameters
The beauty of rdf is that it has parameters and means for modifying the query depending on the values ​​set by the user at runtime.
This means that the parameters are already specified in the stored select and there are rules ( language ) for what and how to change in the select before execution.
')
Excel creation itself
I wrote about this in the first part .
Single form prompting the user to enter data
Since the parameters are now processed only before execution, it is necessary to define them in some formalized form. That is to ask
- datatype
- input format
- prompt for user input
- preliminary checks
- with which result set to check the parameter
- and so on
Only all together it will give a good effect, which I observed in our company. Here are examples of screens that can be written.
This is written in Oracle Forms, but you can also write it on any other platform. Two screens (Administration and Execution) and two PL / SQL packages in the database, several tables for storing data.
We almost abandoned Reports. Analysts use Excel, and we build selects on request. This can take a lot of time, and this is debugging of the select, and logic and optimization. But then, within 20 minutes we define it in the system and the user starts working with it. In my opinion, this is simpler than Business Objects or Oracle Discoverer systems. Yes, it requires the work of the programmer and DBA, but on the other hand, selects are sharpened and efficient. And about Excel, as a powerful analytical tool, I will not speak (not an expert).
Source: https://habr.com/ru/post/264171/
All Articles