/* Pre-requisites: 
    a. Step 1 on template pdb and user creation is completed.
    b. Property file has to be created with SMS and Entity schema details as Template pdb.
    c. Objects has to be loaded in the template pdb from installer for respective product processer
    d. Template pdb schema should be checked for sanity with zero invalids.
*/
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 Approot and ApprootSeed Configuration
SPOOL "&SPOOL_PATH"
/* Inputs are recieved */
accept P_CDB_USER Prompt 'Enter CDB Schema Username: '
accept P_CDB_PWD Prompt  'Enter CDB Schema Password: '
accept P_CDB_HOST Prompt 'Enter CDB Schema Host: '
accept P_CDB_PORT Prompt 'Enter CDB Schema Port: '
accept P_CDB_NAME Prompt 'Enter CDB Schema Name: '
accept P_DB_MOUNTED_PATH Prompt 'Enter CDB mounted path for approot application seed creation[Eg: /scratch/db1800dat] :'

accept P_TEMPLATE_PDB Prompt 'Enter Template PDB Name: '
accept P_APPROOT_NAME Prompt 'Enter Approot Name: '
accept P_PDB_TO_APPPDB Prompt 'Please provide path for pdb_to_apppdb.sql: '
accept P_COMMON_USER Prompt 'Enter Common Username created in Template PDB: '

/* Connecting to cdb
conn sys/FC142SYS18C@fc142cbd as sysdba */
CONN &P_CDB_USER/&P_CDB_PWD@&P_CDB_NAME AS sysdba;

/* Creating the Approot */
CREATE pluggable database &P_APPROOT_NAME AS application container FROM &P_TEMPLATE_PDB file_name_convert=('&P_TEMPLATE_PDB','&P_APPROOT_NAME');
ALTER pluggable database &P_APPROOT_NAME open;

/* Connecting to Approot as sysdba*/
conn &P_CDB_USER/&P_CDB_PWD@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&P_CDB_HOST)(PORT=&P_CDB_PORT)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=&P_APPROOT_NAME))) as sysdba;

grant select on v_$session to &P_COMMON_USER container=all;
grant create session to &P_COMMON_USER container=all;
grant select on gv_$session to &P_COMMON_USER container=all;
grant select on gv_$session to &P_COMMON_USER container=all;
grant select on v_$database to &P_COMMON_USER container=all;

/*Creating Application Seed Manually*/
create pluggable database as seed from &P_APPROOT_NAME file_name_convert=('&P_DB_MOUNTED_PATH/&P_CDB_NAME/&P_APPROOT_NAME/','&P_DB_MOUNTED_PATH/&P_CDB_NAME/SEED&P_APPROOT_NAME/');

alter pluggable database &P_APPROOT_NAME$SEED open;
alter session set container = &P_APPROOT_NAME$SEED;

@&P_PDB_TO_APPPDB;
select cause, type, message, status, action from pdb_plug_in_violations;

SET ERRORLOGGING OFF
SPOOL OFF