Start Application upgrade
This script is run to initiate another application upgrade for object conversion.
Syntax
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 Application Upgrade initiation
SPOOL "&SPOOL_PATH"
DECLARE
l_app_name VARCHAR2(128);
l_app_currver VARCHAR2(30);
l_Sql VARCHAR2(256);
BEGIN
BEGIN
SELECT app_name
INTO l_app_name
FROM dba_applications
WHERE app_implicit <> 'Y'
AND app_name = (SELECT param_val FROM cstb_param WHERE Param_name = 'MULTI_TENANT_APP_NAME');
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Error1 Nodata--->'||SQLERRM);
WHEN OTHERS THEN
dbms_output.put_line('Error1 others--->'||SQLERRM);
END;
BEGIN
SELECT MAX(app_version)
INTO l_app_currver
FROM dba_app_versions
WHERE app_name = l_app_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Error2 Nodata--->'||SQLERRM);
WHEN OTHERS THEN
dbms_output.put_line('Error2 others--->'||SQLERRM);
END;
l_Sql := 'ALTER PLUGGABLE DATABASE APPLICATION ' || l_app_name||' BEGIN UPGRADE '''|| l_app_currver || ''' TO '''|| '&P_APPLICATION_NEXTVER' ||'''';
dbms_output.put_line('l_sql: ' || l_Sql);
EXECUTE IMMEDIATE l_Sql;
l_Sql := 'ALTER SYSTEM SET DEFAULT_SHARING = NONE';
dbms_output.put_line('l_sql: ' || l_Sql);
EXECUTE IMMEDIATE l_Sql;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error --->'||SQLERRM);
END;
/
SET ERRORLOGGING OFF
SPOOL OFF