Hi, pardon me for asking this question (I know I can do this with the help of a PL/SQL function) but would like to ask just in case. I'm wondering if this doable in regular SQL statement without using a function? I'm trying to see if I can write a query without having to create a function.
select username
,('select count(*) from '||username||'.'||XYZ) CNT
from all_users
where username in ('SCOTT1','SCOTT2');Thank you,
Hector
So you're trying to count the rows for table name that exists in many schemas? Any particular reason you need to this dynamically?
Anyway, you can do this with a bit of XML
dbms_xmlgen.getxmltype accepts the text of a SQL query, runs it and returns the output as XML:
grant unlimited tablespace to u1 identified by u1;
grant unlimited tablespace to u2 identified by u2;
create table u1.t (
x int
);
create table u2.t (
x int
);
insert into u1.t values (1);
commit;
select username,
dbms_xmlgen.getxmltype('
select count(*) c
from ' || username || '.t
') x
from dba_users
where username in ('U1', 'U2');
USERNAME X
------------------------------ ------------------------------
U2 <ROWSET>
<ROW>
<C>0</C>
</ROW>
</ROWSET>
U1 <ROWSET>
<ROW>
<C>1</C>
</ROW>
</ROWSET>To get the values, all you need is a bit of XML manipulation:
with rws as (
select username,
dbms_xmlgen.getxmltype('
select count(*) c
from ' || username || '.t
') x
from dba_users
where username in ('U1', 'U2')
)
select username, ct
from rws, xmltable (
'/ROWSET/ROW' passing rws.x
columns ct integer path '/C'
);
USERNAME CT
U2 0
U1 1 Though ask yourself: do you really need to do this? Can't you use static SQL?