Re: newbie: dynamic cursor query

Have a look at this piece of code;

begin

for tname in (select owner||'.'|| table_name table_name

from all_tables

where table_name like '%T%') LOOP

dbms_output.put_line(tname .table_name);

END LOOP; end;

PS. Be nice and put a real email address. Just remember, you're asking for HELP! "kellmeister" <fuzzbutt4ever_at_hotmail.com> wrote in message news:e4aec187.0209041354.6ae3c4e1_at_posting.google.com...
> Hi,
> I'm trying to write a pl/sql program that will return multiple rows.
> I was able to return a single row query, no problem, but I seem to be
> stumped on what I need to incorporate for multi-rows. Below is the
> query, non-working of course, I'm trying to get a result containing
> the table name(s), and corresponding counts for different job
> descriptions.
>
> SET SERVEROUTPUT ON;
>
> declare
> CURSOR c1 IS
> select owner||'.'|| table_name table_name
> from all_tables
> where table_name like '%emp_tb%';
> employee_tables all_tables%rowtype;
> sqlSelect varchar2(255);
> kount number;
> job varchar2(255);
> begin
> For employee_tables in c1 loop
> sqlSelect := 'select job, count(*) from'
> ||employee_tables.table_name ||
> ' group by job;
> execute immediate sqlSelect into kount, job;
> DBMS_OUTPUT.PUT_LINE('Number of rows in
> '||employee_tables.table_name||'.'
> ||kount||'.'||job||'.');
> end loop;
> end;
> /
Received on Wed Sep 04 2002 - 23:20:44 CDT