1
0
During this post we will discuss how to handle Array in java script procedure. Consider the scenario where Business has presented the list of tables in an ARRAY form . As part of process we have to develop the javascript procedure to accept the ARRAY as an input argument. Once the argument received we need to split the ARRAY into individual tables based on the comma delimiter.
Once we get the list of tables supplied by business, a regular VIEW should be created on the top of every individual table automatically. Now to create the view we have two options:
- Run simple select * from <table> as underlying query and this will create the view with default Base table columns.
- Or we can extract the list of columns for input table and based on the business decision we can filter out the columns and create the VIEW.
To give more usability and flexibility, I am following the second approach. We will be using information_schema.COLUMNS view to get the list of columns for defined table. Later on will develop the dynamic query to create the VIEW based on these columns in procedure.
Learning outcome:
- How to pass Array while calling the procedure.
- Accept the Array as an input argument.
- Find the length of Array, Split and fetch the table names individually.
- Build the Columns list using LISTAGG for table.
Technical Implementation:
As per the below screenshot ,we can see there are no Regular VIEWS are present inside the DEMO_DB. We will passing the list of below tables as input argument.
[‘CUST’, ‘CUSTOMER’,’EMPLOYEE’,’TASK_DEMO_TBL’,’ENTRIES ]
The procedure should split this list and create the separate view for each table.
Procedure:
Call the procedure:
CALL table_array_view(array_construct('CUST', 'CUSTOMER','EMPLOYEE','TASK_DEMO_TBL','ENTRIES'));
Procedure Output:
DEMO_DB View:
