Oracle – Apply SQL to all schema in database

oracle

I'm trying to run the same SELECT against all the schema in my Oracle database.
I found an example of how to do this, but when I try to run it against my database I get the below error:

PLS-00306: wrong number or types of arguments in call to

This is my PLSQL

DECLARE
v_sql varchar2(128);
 cursor c1 is
    select username from dba_users WHERE account_status = 'OPEN' and DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM');
BEGIN
    for REC in c1 loop
        v_sql := 'select * from '||REC||'.accountdetails';
        EXECUTE IMMEDIATE v_sql;
    end loop;
end;
/

So this should in effect be doing this…

SELECT * FROM USER1.ACCOUNTDETAILS;
SELECT * FROM USER2.ACCOUNTDETAILS;
SELECT * FROM USER3.ACCOUNTDETAILS;
etc.

Any advice greatly appreciated

C

From Akina comments, I now have

DECLARE
cursor c1 is select username from dba_users WHERE account_status = 'OPEN' and DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM');
BEGIN
    for REC in c1 loop
        select * from REC.accountdetails;
    end loop;
end;
/

but i get 

PL/SQL: ORA-00942: table or view does not exist
PL/SQL: SQL Statement ignored

Best Answer

DECLARE
v_sql varchar2(4000);
 cursor c1 is
    select o.owner,o.object_name from dba_users u,dba_objects o  WHERE u.account_status = 'OPEN' and u.DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM')
    and u.username=o.owner and o.object_name='ACCOUNTDETAILS' and o.object_type='TABLE';
BEGIN
    for REC in c1 loop
        v_sql := 'select * from '||REC.owner||'.'||REC.object_name;
        EXECUTE IMMEDIATE v_sql;
    end loop;
end;
/
Related Question