PL/SQL code

[Doron]

Hi,

I have a piece of code (attached bellow) that grabs the names of
several tables in my schema. I now want to grab a maximum value of one
of the columns from these tables (same column name in all the tables).
Is there any way other then spooling the output (as shown below) into
a file and running that file?

As you can see from the code I'm a beginner at this so any help will
be much appreciated.

Thanks,
Doron



Declare

CURSOR SITE_BASE_CUR IS select tdm_base_obj_id from tdm_db_site_ranges
where object_id = (select object_id from tdm_db_site where tdm_name =
(select global_name from
global_name));

site_base tdm_db_site_ranges.tdm_base_obj_id%type ;

cursor up_tab is select table_name from tdm_class where class_id in
(select class_id from tdm_last_object_id where object_id>0);

update_table tdm_class.table_name%type ;

begin
open site_base_cur;
fetch site_base_cur into site_base;

open up_tab;
loop
fetch up_tab into update_table;
exit when up_tab%notfound;
dbms_output.put_line(‘select max(column_1) from' update_table);
end loop;
end ;

[Mark C. Stock]

you probably want to use dynamic SQL -- check out EXECUTE IMMEDIATE in the
PL/SQL docs

++ mcs

"Doron" <do*********@msn.com> wrote in message
news:99**************************@posting.google.c om...
| Hi,
|
| I have a piece of code (attached bellow) that grabs the names of
| several tables in my schema. I now want to grab a maximum value of one
| of the columns from these tables (same column name in all the tables).
| Is there any way other then spooling the output (as shown below) into
| a file and running that file?
|
| As you can see from the code I'm a beginner at this so any help will
| be much appreciated.
|
| Thanks,
| Doron
|
|
|
|
|
|
|
| Declare
|
| CURSOR SITE_BASE_CUR IS select tdm_base_obj_id from tdm_db_site_ranges
| where object_id = (select object_id from tdm_db_site where tdm_name =
| (select global_name from
| global_name));
|
| site_base tdm_db_site_ranges.tdm_base_obj_id%type ;
|
| cursor up_tab is select table_name from tdm_class where class_id in
| (select class_id from tdm_last_object_id where object_id>0);
|
| update_table tdm_class.table_name%type ;
|
|
|
|
|
| begin
|
|
| open site_base_cur;
| fetch site_base_cur into site_base;
|
| open up_tab;
| loop
| fetch up_tab into update_table;
|
|
| exit when up_tab%notfound;
| dbms_output.put_line('select max(column_1) from' update_table);
| end loop;
| end ;

[padmaja_kad]

Hi Doron,

You can use dynamic sql. It will let you build the sql statement and
execute it.
The following link has good examples.

http://www.cise.ufl.edu/help/databas...ynam.htm#13131

Padma
do*********@msn.com (Doron) wrote in message hnews:<99**************************@posting.google .com>...

Hi,

I have a piece of code (attached bellow) that grabs the names of
several tables in my schema. I now want to grab a maximum value of one
of the columns from these tables (same column name in all the tables).
Is there any way other then spooling the output (as shown below) into
a file and running that file?

As you can see from the code I'm a beginner at this so any help will
be much appreciated.

Thanks,
Doron



Declare

CURSOR SITE_BASE_CUR IS select tdm_base_obj_id from tdm_db_site_ranges
where object_id = (select object_id from tdm_db_site where tdm_name =
(select global_name from
global_name));

site_base tdm_db_site_ranges.tdm_base_obj_id%type ;

cursor up_tab is select table_name from tdm_class where class_id in
(select class_id from tdm_last_object_id where object_id>0);

update_table tdm_class.table_name%type ;

begin
open site_base_cur;
fetch site_base_cur into site_base;

open up_tab;
loop
fetch up_tab into update_table;
exit when up_tab%notfound;
dbms_output.put_line(?select max(column_1) from' update_table);
end loop;
end ;