If a View is not whitelisted, it means that certain dependencies are missing for the object. For this, you need to create a one-time setup script to ensure that your SQL View gets whitelisted by following the steps given below:
Step 1:
Identify the dependencies of the table referred in the SQL query in the view builder. For example, if the view is created based on the table MV_LIST_POS_VALUES_B2003 use the below query to identify the dependencies.
SELECT
a.*
FROM
all_dependencies a
WHERE
name = 'MV_LIST_POS_VALUES_B2003' and a.referenced_type = 'PACKAGE';
Step 2:
Use the REFERENCED_NAME as object name and find the corresponding procedures or functions. For example, if the above query returns the REFERENCED_NAME as 'M_PCK_WIN3_VIEW_B2003', use the following query to identify the procedures/functions.
SELECT
procedure_name
FROM
all_procedures
WHERE
object_type = 'PACKAGE'
AND
object_name = 'M_PCK_WIN3_VIEW_B2003'
AND
owner = 'M_SYS';
Step 3:
Insert the dependency procedures/functions into the tables - m_sys.m_view_whitelists and m_sys.m_view_whitelist_nls using insert scripts.
SELECT * FROM m_sys.m_view_whitelists ;
SELECT * FROM m_sys.m_view_whitelist_nls;
Step 4:
You need to insert the scripts to whitelist your View. For example, if the query in Step 2 results in the procedures/functions such as GET_ORDER_SEQ B2003 and SET_G_LV_ID_EXISTS, use the sample scripts given below to create the data in m_sys.m_view_whitelists and m_sys.m_view_whitelist_nls tables. For the scripts, you need to understand the following:
-
For VWH_ID, you need to provide a unique ID number,
-
FUNCTION_NAME in the insert script should be used as PROCEDURE_NAME from step 2 query output. You need to insert all the procedures in the FUNCTION_NAME in the script given below.
-
For PACKAGE_NAME, you need to enter the table name such as M_PCK_WIN3_ VIEW_ B2003 in this example.
Script to create dependencies in WHITELISTS tables
Insert into M_SYS.M_VIEW_WHITELISTS (VWH_ID, FUNCTION_NAME, USR_ID, LMOD, INT_REV, PACKAGE_NAME) values (5202, 'GET_ORDER_SEQ_B2003' , 'M_SYS' , sysdate , 1, 'M_PCK_WIN3_VIEW_B2003');
Insert into M_SYS.M_VIEW_WHITELISTS (VWH_ID, FUNCTION_NAME, USR_ID, LMOD, INT_REV, PACKAGE_NAME) values (5204, 'SET_G_LV_ID_EXISTS' , 'M_SYS' , sysdate , 1, 'M_PCK_WIN3_VIEW_B2003' );
Script for NLS
Insert into M_SYS.m_view_whitelist_nls (short_desc, description, USR_ID, LMOD, INT_REV, nls_id, vwh_id) values ('GET_ORDER_SEQ_82003' , 'GET_ORDER_SEQ_82003' , 'M_SYS' ,sysdate, 1, 1, 5202) ;
Insert into M_SYS.m_view_whitelist_nls (short_desc, description, USR_ID, LMOD, INT_REV, nls_id, vwh_id) values ('SET_G_LV_ID_EXISTS' , 'SET_G_LV_ID_EXISTS' , 'M_SYS' ,sysdate, 1, 1, 5204) ;
Step 5:
Once insert scripts are run, ensure to commit the changes. This is a one-time setup to create a new View using the View Builder, but when using any view in your query, you should cross check this for a seamless build.