Extract JSON from .PY file and Process using PL/SQL.

Implementation Logic Script Parser.

Purpose :

                 Logic Script Parser is a module which accepts python script file as input, extracts script parameters from JSON object of scripts and insert these parameter values into ADS database.

How it works :

                  Logic Script Parser follows the following steps:

1.  Upload script files into the apex application temporary file table.
2.  Select an uploaded file based on the file name for parsing.
3.  Extract JSON object assigned to processing schema from the selected script file.
4. Parse Extracted JSON and create a temporary Table by using JSON_TABLE( ) function of oracle apex.
5. Store parameters value to ADS table.

1. Uploading script files :

                                       In the parser, I used apex file browse page item to uploading script files into the apex application temporary file table for temporary storage. File browse page item stores file into apex_application_temp_files table.

2. Selecting an uploaded file for parsing : 

After step one, parser select uploaded file form apex_application_temp_files table by using the file name and validate that same script already exist for the same module or not, if a file already exists then it shows script already exists error.  following select query is used for selecting a script file. 

Select 

            FILENAME, MIME_TYPE, BLOB_CONTENT   

          into   

            l_FILENAME, l_MIME_TYPE, l_BLOB_CONTENT  

          from  

            apex_application_temp_files   

          where  

name = ‘SCRIPT_NAME’ ; 

3. Extraction of JSON from script file : 

                           Extraction of JSON  assigned to processing schema from a script file is done by using the regular expression and string pattern matching expression. extracted json is stored into clob varraible.

following regular expression is used for extracting JSON from a script :

             REGEXP_SUBSTR (l_clob,

                      '{([^}]+}){'||l_c_b||'}',

                      l_start_position,

                      1,

                      NULL

                      );

4. Parsing extracted  JSON and create temprory table :

                  Extracted JSON is parsed by using apex_json.parse() function which convert stored JSON into object, key , values. after parsing the JSON we craete temprory table by using JSON_TABLE( )

which create table of  each Object key as column and values as rows.

Example : 

JSON_TABLE(extracted_json,'$'

             columns(

                 script_description varchar2(255) path '$.magna_meta_description',

                 script_version varchar2(255) path '$.magna_meta_version',

                 script_module varchar2(255) path '$.magna_meta_processing_module_assignment'

         ))

5. Store parameter values into ADS tables:

                           finally we inserts acript data into ADS$TA_LOGIC_SCRIPT table and parameter values into ADS$TA_PARAMETER table of ADS databse from temprory JSON tables.

Example : 

ADS$TA_LOGIC_SCRIPT SET(LOGICSCRIPT_DESC ,LOGICSCRIPT_VERSION,LOGICSCRIPT_MODULE) =

        (SELECT script_description,script_version, CASE

           WHEN script_module = 'HIL'  THEN '1'  

                                          WHEN script_module = 'PP1'  THEN '2'

        END from 

         JSON_TABLE(v_json,'$'

             columns(

                 script_description varchar2(255) path '$.magna_meta_description',

                 script_version varchar2(255) path '$.magna_meta_version',

                 script_module varchar2(255) path '$.magna_meta_processing_module_assignment'

         )))                

How to Create PDF Report Using PL/SQL

Before writing and giving this example to create a PDF report using PL/SQL in Oracle, I researched a lot on Google, but I did not find any proper example. So I thought I should give an example to generate a PDF report file using PL/SQL. To do this first, you need to install pdf_builder_pkg database package in your schema.

you can download the package from pdf_build_pkg link.

Now you have already installed the database package. Now you can write the procedure or function to create a PDF report using PL/SQL. Here below is the example PL/SQL block.

Example Function which return pdf file

------------------------------------------------------------------------------------------------------------

create or replace function "DOWNLOAD_REPORT"
return BLOB
is
pdf_blob blob;
begin
 /* First line to initialize the package*/
 pdf_builder_pkg.init;

 /* Set the font to bold for heading*/
 pdf_builder_pkg.set_font ('helvetica', 'b');

 /* Write a line using pdf_builder_pkg.write procedure*/ 
 pdf_builder_pkg.write ('Employee Report');

 /* Set the font to normal */

 pdf_builder_pkg.set_font ('helvetica');
 pdf_builder_pkg.
 write ('Printed Date: ' || SYSDATE, -1, p_alignment => 'right');
pdf_builder_pkg.
 write (
 '________________________________________________________________________________');
pdf_builder_pkg.write ('<h1> Heading </h1>');
 /* Saving the PDF file by passing directory name and file name */

pdf_blob := pdf_builder_pkg.get_pdf();

return pdf_blob;

end;


------------------------------------------------------------------------------------------------------------

You can find the PDF file named emp_report.pdf at your directory location.  The PDF report will look like as shown below:

For more reference of PDF_build package visit https://technology.amis.nl/2012/04/11/generating-a-pdf-document-with-some-plsql-as_pdf_mini-as_pdf3/  


Thanks,
Happy Coding 👱