CODEWITHSUNDEEP
oracle-databaseplsqldbms-output
LNL
LNL

Reputation: 5

PL/SQL looping through PDBs

I am trying to write a PL/SQL script to loop through all of the pluggable databases, perform a task and produce an output. The code below is what I've done so far. I am not sure what is wrong but the error I am getting is PLS-00306: wrong number or types of arguments in call to 'PUT_LINE' and I am not sure why. Can anyone please help?

declare
type names_t is table of v$pdbs.name%type;
names names_t;
--vname names_t;
type open_modes_t is table of v$pdbs.open_mode%type;
open_modes open_modes_t;
type privilege_user_t is table of dba_sys_privs.privilege%type;
privilege_user privilege_user_t;
begin
        select name, open_mode
        bulk collect
        into names, open_modes
        from v$pdbs
        where name not in ('PDB$SEED' , 'DCPDB01');
                for j in 1 .. names.count()
                loop
                if open_modes (j) <> 'MOUNTED'
                then
                execute immediate 'alter session set container= "' || names (j) || '"';
                end if;
                --select name bulk collect into vname from v$pdbs;
                select grantee bulk collect into privilege_user from dba_sys_privs
                where (privilege like '%ANY%' or privilege ='DBA') and grantee like 'U%'
                group by  grantee, privilege
                order by grantee;
                DBMS_OUTPUT.put_line(privilege_user);
        end loop;
end;
/

Upvotes: 0

Views: 1073

Answers (1)

Gary_W
Gary_W

Reputation: 10360

DBMS_OUTPUT.put_line() expects a string for an argument. You are passing it a type of a table of dba_sys_privs.privilege%type's.

Upvotes: 1

Related Questions