Application Root objects conversion for new objects
This is script is used in converting the new DL objects during patch-set based on the deployment model of the application during installation.
Syntax
/* Script for Shared Object Conversion for patch-set */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Upgrade New object Conversion
SPOOL "&SPOOL_PATH"
DECLARE
l_count NUMBER;
l_app_deployment VARCHAR2(30);
BEGIN
SELECT count(*)
INTO l_count
FROM user_objects
WHERE sharing = 'NONE' --to get the new set of DL approot objects if any
AND object_name IN
(SELECT DISTINCT a.object_name
FROM cstm_approot_objects a
WHERE sharing = 'DL'
AND UPPER(object_type) = 'TABLE'
AND EXISTS (SELECT 1
FROM user_objects b
WHERE b.object_name = a.object_name)
AND EXISTS (SELECT 1
FROM cstm_approot_functions_menu c
WHERE c.function_id = a.function_id
AND c.modifiable IN ('Y', 'S')));
dbms_output.put_line('l_count: ' || l_count);
IF l_count > 0 THEN
dbms_output.put_line('New DL objects are available');
SELECT param_val
INTO l_app_deployment
FROM cstb_param
WHERE param_name = 'MULTI_TENANT_DEPLOYMENT_MODEL';
dbms_output.put_line('l_app_deployment: '||l_app_deployment);
IF l_app_deployment IS NOT NULL AND l_app_deployment = 'SAUA' THEN
UPDATE smtb_menu menu
SET menu.approot_flg = 'Y'
WHERE menu.function_id IN
(SELECT function_id
FROM cstm_approot_functions_menu
WHERE modifiable = 'S'
UNION
SELECT summary_fn_id
FROM cstm_approot_functions_menu
WHERE modifiable = 'S'
AND summary_fn_id IS NOT NULL) --SMS function id 'S'
AND menu.approot_flg <> 'Y'; --excluding the already modified approot function ids in menu.
ELSIF l_app_deployment IS NOT NULL AND l_app_deployment = 'SASDD' THEN
UPDATE smtb_menu menu
SET menu.approot_flg = 'Y'
WHERE menu.function_id IN
(SELECT function_id
FROM cstm_approot_functions_menu
UNION
SELECT summary_fn_id
FROM cstm_approot_functions_menu
WHERE summary_fn_id IS NOT NULL)
AND menu.approot_flg <> 'Y'; --excluding the already modified approot function ids in menu.
ELSIF l_app_deployment IS NOT NULL AND l_app_deployment = 'SASDC' THEN
/*Assumption new table cstm_approot_menu_custom_movedtopdb will be available
and is populated with the function ids which are moved to PDB as part of custom deployment
It has 2 columns FUNCTION_ID and SUMMARY_FN_ID*/
UPDATE smtb_menu menu
SET menu.approot_flg = 'Y'
WHERE menu.function_id IN
(SELECT function_id
FROM cstm_approot_functions_menu
UNION
SELECT summary_fn_id
FROM cstm_approot_functions_menu
WHERE summary_fn_id IS NOT NULL)
AND menu.function_id NOT IN --excluding the function ids moved to PDB already.
(SELECT function_id
FROM cstm_approot_menu_movedtopdb
UNION
SELECT summary_fn_id
FROM cstm_approot_menu_movedtopdb
WHERE summary_fn_id IS NOT NULL)
AND menu.approot_flg <> 'Y'; --excluding the already modified approot function ids in menu.
END IF;
BEGIN
FOR I IN (SELECT 'BEGIN ' || chr(10) ||
'DBMS_PDB.SET_DATA_LINKED(''&P_APPROOT_USER''' || ',''' ||
Object_Name || ''',' || Namespace || '); ' || chr(10) ||
'EXCEPTION ' || chr(10) ||
'WHEN OTHERS THEN ' || chr(10) ||
'DBMS_OUTPUT.PUT_LINE(''ERROR ->''|| SQLERRM); ' ||
chr(10) || 'END;' sqlobject
FROM user_objects
WHERE sharing = 'NONE' --to get the new set of DL approot objects if any
AND object_name IN
(SELECT DISTINCT a.object_name
FROM cstm_approot_objects a
WHERE sharing = 'DL'
AND UPPER(object_type) = 'TABLE'
AND EXISTS (SELECT 1
FROM user_objects b
WHERE b.object_name = a.object_name)
AND EXISTS
(SELECT 1
FROM cstm_approot_functions_menu c
WHERE c.function_id = a.function_id
AND c.modifiable IN ('Y', 'S')))) LOOP
DBMS_OUTPUT.PUT_LINE(chr(10));
EXECUTE IMMEDIATE I.sqlobject;
DBMS_OUTPUT.PUT_LINE(I.sqlobject);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error --->' || SQLERRM);
END;
ELSE
dbms_output.put_line('No new DL objects available');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error --->'||SQLERRM);
END;
/
SET ERRORLOGGING OFF
SPOOL OFF