newbie: dynamic cursor query 
Author Message
 newbie: dynamic cursor query

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;
/



Mon, 21 Feb 2005 05:54:29 GMT
 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!


Quote:
> 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;
> /



Mon, 21 Feb 2005 12:20:44 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. cursor for dynamic query

2. dynamic query and cursor

3. fill cursor from dynamic query

4. Cursors and dynamic queries

5. Dynamic Query in Cursor Loop

6. Cursors from dynamic queries

7. Opening cursors with dynamic queries

8. Cursor with dynamic query

9. Reports 2.5 - Newbie question re: dynamic changes to query

10. Newbie Q: Dynamic Query?

11. Newbie - Creating a cursor, and running a SELECT on the cursor

12. Dynamic Query w/ Dynamic Table Name (with a Northwind Example)


 
Powered by phpBB® Forum Software