-- ACL for Oracle DB 12
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  sUsername         VARCHAR2(50) := 'RVUSER';           -- Basis Username, if Container, script set C## in Front or the defined Value
  sCommonUserPrefix VARCHAR2(50) := '';                 -- Temporary Nedded, used to Store the defined User Prefix if Container Database
  c INT;
  v_count12 NUMBER;
  compile_error exception;
  pragma exception_init(compile_error, -06550);
BEGIN
  BEGIN
    DBMS_OUTPUT.put_line('- Check if CDB Database User needed');
    EXECUTE IMMEDIATE '
      DECLARE
        c INT;
        exCustom EXCEPTION;
        PRAGMA EXCEPTION_INIT(exCustom, -20001);
	  BEGIN
        SELECT COUNT(*) INTO c FROM V$DATABASE WHERE CDB=''YES'';
        IF c = 0 THEN
          DBMS_OUTPUT.put_line(''-- CDB Database: NO'');
          raise_application_error(-20001, ''CDB Database: NO'');
        END IF;
        DBMS_OUTPUT.put_line(''-- CDB Database: YES'');
	  END;
    ';
	-- It looks like it is a Server with Container Database.
	-- Get Parameter common_user_prefix if it is set, store it in sCommonUserPrefix
    SELECT VALUE INTO sCommonUserPrefix FROM V$PARAMETER WHERE NAME='common_user_prefix';
	-- If Parameter common_user_prefix is not set, set in the Variable sCommonUserPrefix the Oracle default Value C##
	IF sCommonUserPrefix IS NULL THEN
      sCommonUserPrefix := 'C##';
      DBMS_OUTPUT.put_line('-- No common_user_prefix is set, use default C##');
    ELSE
      DBMS_OUTPUT.put_line('-- Found common_user_prefix with Value: ' || sCommonUserPrefix);
    END IF;
	-- Add in Front of the sUsername the sCommonUserPrefix
    sUsername := sCommonUserPrefix || sUsername;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('-- No CDB_FEATURE_USAGE_STATISTICS');
  END;
  
  DBMS_OUTPUT.put_line('- Check if Oracle DB 12 or higher');
  SELECT COUNT(*) INTO v_count12 FROM V$INSTANCE WHERE regexp_like(VERSION,'^1[2-9]\.|^[2-9][0-9]\.');
  IF v_count12 >= 1 THEN
    EXECUTE IMMEDIATE q'<
        BEGIN
      DBMS_NETWORK_ACL_ADMIN.append_host_ace (
        host       => '*', 
        ace        => xs$ace_type(privilege_list => xs$name_list('resolve'),
                                  principal_name => '>' || sUsername || q'<',
                                  principal_type => xs_acl.ptype_db)); 
      dbms_output.put_line('  - Added ACL to Oracle DB 12 or higher');
        END;    
    >';
  ELSE
    dbms_output.put_line('  - Can not find Oracle DB 12 or higher');
  END IF;
EXCEPTION
  WHEN compile_error THEN
    dbms_output.put_line('  - Can not find Oracle DB 12 or higher');
END;
/