Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: A Real Dynamic Problem For Real Dynamic Developers!!
Are these tables related in any particular way? The reason I ask this is
that just because each table has this column (sounds like you want to know
who inserted or modified a row - a common requirement) doesn't mean that a
report like you suggest means anything. Do you want a report that lists
what users made changes or inserts to what tables? Then I suggest
dynamically writing a Union statement.
e.g.
(select 'Table_one',user_name from table_one group by user_name)
union
(select 'Table_two',user_name from table_two group by user_name)
union...
Now being the lazy guy I am I would do something like this in SQLPlus:
spool myQuery
select '(select '||table_name'|| table_name,user_name from '||table_name||'
group by user_name) union' from user_tables ut where exists
(select 1 from user_tab_col utc where ut.table_name=utc.table_name and
column_name='USER_NAME')
spool off
Then you have your query (remove the extra union at the end.
JIm
"Jaboos" <safena_at_hotmail.com> wrote in message
news:220529d7.0110061109.75469779_at_posting.google.com...
> Hi All,
> I'm facing a real problem here, and I would be gratefull if someone
> can help me. The problem is as follows:
>
> 1. I'm having a database application consist of 180 (One Hundred
> Eighty) tables!
> 2. There is a column that exist on each one of those 180 tables. This
> column called 'User_Name'.
>
> My question is: How can I retreive the data from that column
> (User_Name) from all the 180 tables, using Reports or Forms (PL/SQL)?
> Or maybe using a single (sort of) query!
>
> NB: It's tedious to write the same sql query for each of the 180
> tables.
>
> The result might look like this:
>
> Table_Name User_Name
> ---------------------------------------- ----------------------
> Table_One User_One
> Table_Two User_Two
> Table_Three User_Three
> Table_Four User_Four
> ...
> ...
> etc.
>
> Best Regards,
> Jaboos
Received on Sat Oct 06 2001 - 16:05:15 CDT