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'

         )))