newbie: dynamic cursor query

From: kellmeister <fuzzbutt4ever_at_hotmail.com>
Date: 4 Sep 2002 14:54:29 -0700
Message-ID: <e4aec187.0209041354.6ae3c4e1@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 - 16:54:29 CDT