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