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.
- 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.
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.
Call the procedure:
CALL table_array_view(array_construct('CUST', 'CUSTOMER','EMPLOYEE','TASK_DEMO_TBL','ENTRIES'));