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_ran ges
where object_id = (select object_id from tdm_db_site where tdm_name =
(select global_name from
global_name));

site_base tdm_db_site_ran ges.tdm_base_ob j_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]

Re: PL/SQL code

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

++ mcs

"Doron" <doron_almog@ms n.comwrote in message
news:995517bc.0 407201325.16ac4 e6a@posting.goo gle.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_ran ges
| where object_id = (select object_id from tdm_db_site where tdm_name =
| (select global_name from
| global_name));
|
| site_base tdm_db_site_ran ges.tdm_base_ob j_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@yahoo.com]

Re: PL/SQL code

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/database/oracle-docs/appdev.920/a96624/11_dynam.htm#13131

Padma


doron_almog@msn .com (Doron) wrote in message hnews:<995517bc .0407201325.16a c4e6a@posting.g oogle.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_ran ges
where object_id = (select object_id from tdm_db_site where tdm_name =
(select global_name from
global_name));
>
site_base tdm_db_site_ran ges.tdm_base_ob j_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 ;