| single query by which data can fetched from table which name generated from a query [message #615013] |
Thu, 29 May 2014 08:40  |
 |
evivekj
Messages: 13
Registered: May 2014
Location: india
|
Junior Member |
|
|
Hi,
Need Help in writing a single query by which data can fetched from a table name which we get from a another query
ex :
SELECT object_name
FROM all_objects
WHERE object_type='TABLE'
AND owner='ABC' ;
let ABC is the user who have only one table and in my case each user have only one table and each tables for diffrent user
will have same primary key column empid
ex:
SELECT empid FROM tab_abc
So how do can i can find diffrent empolyee id data for differnt owner input
thank you
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: single query by which data can fetched from table which name generated from a query [message #615121 is a reply to message #615100] |
Fri, 30 May 2014 07:37  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
cookiemonster wrote on Fri, 30 May 2014 05:10No you can't do it in a single query, you would have to use dynamic SQL.
Yes, you can (assuming table has no LONG columns - there is a bug in XML with LONGs):
SELECT owner,
table_name,
empno
from all_tab_columns,
xmltable(
'/ROW/EMPNO'
passing xmlquery(('ora:view("' || table_name || '")') returning content)
columns
empno number path '.'
)
where column_name = 'EMPNO'
and owner = 'SCOTT'
order by owner,
table_name
/
OWNER TABLE_NAME EMPNO
------------------------------ ------------------------------ ----------
SCOTT EMP 7369
SCOTT EMP 7499
SCOTT EMP 7521
SCOTT EMP 7566
SCOTT EMP 7654
SCOTT EMP 7698
SCOTT EMP 7782
SCOTT EMP 7788
SCOTT EMP 7839
SCOTT EMP 7844
SCOTT EMP 7876
OWNER TABLE_NAME EMPNO
------------------------------ ------------------------------ ----------
SCOTT EMP 7900
SCOTT EMP 7902
SCOTT EMP 7934
SCOTT EMP_INFO 101
15 rows selected.
SQL>
SY.
[Updated on: Fri, 30 May 2014 07:41] Report message to a moderator |
|
|
|
|
|
| Re: single query by which data can fetched from table which name generated from a query [message #615144 is a reply to message #615141] |
Fri, 30 May 2014 10:59 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
In 10G we could still use xmltable just substitute ora:view with dbms_xmlgen:
SQL> select *
2 from v$version
3 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> SELECT owner,
2 table_name,
3 empno
4 from all_tab_columns,
5 xmltable(
6 '/ROWSET/ROW/EMPNO'
7 passing dbms_xmlgen.getxmltype('select empno from '||owner||'.'||table_name)
8 columns
9 empno number path '.'
10 )
11 where column_name = 'EMPNO'
12 and owner = 'SCOTT'
13 order by owner,
14 table_name
15 /
OWNER TABLE_NAME EMPNO
------------------------------ ------------------------------ ----------
SCOTT EMP 7369
SCOTT EMP 7499
SCOTT EMP 7521
SCOTT EMP 7566
SCOTT EMP 7654
SCOTT EMP 7698
SCOTT EMP 7934
SCOTT EMP 7788
SCOTT EMP 7839
SCOTT EMP 7844
SCOTT EMP 7876
OWNER TABLE_NAME EMPNO
------------------------------ ------------------------------ ----------
SCOTT EMP 7900
SCOTT EMP 7902
SCOTT EMP 7782
14 rows selected.
SQL>
SY.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
