How to achieve listagg effect in pre-11g Oracle database and listagg example

Version 1

select department_id, rtrim(first_name,',') enames
from ( select department_id, first_name, rn
       from employees
       model 
       partition by (department_id)
       dimension by (
         row_number() over (partition by department_id order by first_name) rn
       )
       measures (cast(first_name as varchar2(300)) first_name)
       rules (
        first_name[any] order by rn desc = first_name[cv()]||','||first_name[cv()+1]
        )
)
where rn = 1
order by department_id
;

Version 2

select department_id, substr(max(sys_connect_by_path(first_name,',')),2) members
from (select department_id, first_name, 
    row_number() over (partition by department_id order by department_id) rn
    from employees)
start with rn = 1
connect by prior rn = rn-1
and prior department_id = department_id
group by department_id
;

Version 3

-- 11g Orace listagg function
select department_id, listagg(first_name, ',') within group (order by department_id) members
from employees
group by department_id
;

Version 4

-- 19c new features: distinct clause to remove duplicates
select department_id, listagg(distinct first_name, ',') within group (order by department_id) members
from employees
group by department_id
;

Default privileges granted to PDB_DBA (19c)

Without pdb_dba_roles clauses in the create pluggable database DDL, following are default privilege granted to PDB_DBA.

  GRANT CREATE SESSION TO "PDB_DBA";

  GRANT CREATE PLUGGABLE DATABASE TO "PDB_DBA";

  GRANT SELECT ON "SYS"."PDB_PLUG_IN_VIOLATIONS" TO "PDB_DBA";

  GRANT SELECT ON "SYS"."PDB_ALERTS" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_SAMEAS_REC" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_SAMEAS_LIST" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."RDFSA_LABELGEN" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_INDEXTYPE_IM" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_STATS" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_RDF_REL2RDF_INTERNAL" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_RDF_INTERNAL" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_PERF" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_CTX_SESSION" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_CTX" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_LOG" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_UTL" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_INF_INTERNAL" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_INFH" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_INFI" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_VARS_TABLE" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_CONST" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RULE" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_NODE" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_NODES_ARRAY" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RULE_NODE" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_GOAL_NODE" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RULES_TABLE" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_GRAPH" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."RDF_APIS_INTERNAL" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."RDF_APIS_USER" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RDFSA_IR" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."RDF_MATCH_IMPL_T" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_HTTP_CTX" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SPARQL_SERVICE" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_MATCH_NL_IMPL_T" TO "PDB_DBA";

  GRANT EXECUTE ON "SYS"."RDF_NETWORK_CREATOR_PRIVS" TO "PDB_DBA";

  GRANT EXECUTE ON "SYS"."GRANT_RDF_OWNER_DR_PRIVS" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_CLI" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."RDF_APIS_OPER_INTERNAL" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_OPERATOR" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_UPGRADE_TO_11" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_DOWNGRADE" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_RDF_MIG" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SDO_SEM_DOWNGRADE_UTL" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RDFCTX_DR" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RDFCTX_IR" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEMCONTEXTINDEXMETHODS" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RDFCTX_OPER" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RDFSA_CONST" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_OLS_DR" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RDFSA_CONST_INTERNAL" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RDFSA_PRIV" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."SEM_RDFSA_DR" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."RDF$LBACLAB" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."RDF$DOMSALABEL" TO "PDB_DBA";

  GRANT EXECUTE ON "MDSYS"."RDFSA_DOMLBL" TO "PDB_DBA";