-
Dear Friends,
I need to find out some thing strange. Yes .
Is there any method of select all the rows from all the tables of one particular user.
I have uniform(same) column name in all the tables.
Could u help me in this issue .
gandhi
-
But first tell me why do u need such a thing .
Hi ,
Definetly u can select all the rows from all the tables from one or more user .
let me know if column names and data types are same in all the tables . then it will be simpler .
select col1 , col2 , col3 from tab1 ;
union
select col1 , col2 , col3 from tab2 ;
union
......
......
if table structures are different ( column names , datatypes , number of columns in a table ) then u have to follow different procedure .
1) use aliases for each column name .
2) put all datatypes column in one row.
3) see that each select statement has same number of columns when using UNION Clause .
ex :
select eff_dt date , empno NUM01 from tab1 ;
union
select dep_dt date , deptno NUM01 from tab2 ;
union
select ced_dt date , null from tab3 ;
....
....
Hope this helps you .
siva prakash
DBA
-
Hi Siva,
Thanks for the information.
I have more than 5000 tables in one particular user. All the tables have only two columns of same name and same datatype.
Then how can i select all the records.
bye
gandhi
OCP-DBA.
-
Code:
set serveroutput on;
spool myscript.sql
declare
stmt VARCHAR2(400);
cursor t_cursor is select table_name from user_tables;
begin
for t_rec in t_cursor loop
stmt:='SELECT * FROM '||t_rec.table_name ;
dbms_output.put_line(stmt);
dbms_output.put_line('union');
end loop;
end;
spool off;
verify the script and remove the unnecessary lines at end
Sanjay
-
I think I responded to this on another board, but depending on what you want to find out, you could export the user's schema including data then either grep (or if you have the right tools, edit/search) for the pattern you want.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
-
Sanjay,
Thanks for the script. It works fine when i go for the normal datatype.
I use Oracle9i and with XMLTYPE datatype.
When i use the script it is not able to UNION the two SQL statements.
It says:
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method
Could u Pls help on this issue.
Thanks in Advance
gandhi
OCP-DBA
-
Hi Sanjay,
Your script list down all the tables with
"select * from emp"
"select * from dept"
But, the command is not executing.
Is there any other way.
gandhi
OCP-DBA
-
Hi gandhi,
You will have to execute the spooled script from sqlplus.
SQL>@myscript.sql
HTH
Sanjay
-
Hi Sanjay,
This script works for normal tables with datatype as NUMBER,VARCHAR2,DATE etc.
But, i use XMLTYPE , internally Oracle will store it as LOB.
So, when i query the records i get the following errors:
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method
I used the similar one manually, by using UNION, it fails because of XMLTYPE.
More over, i need to select the rows table by table not by using UNION.
Thanks
gandhi
OCP-DBA.
-
Hi Friends,
Was there any answer for getting the output continouslly without manual interferance:
eg . FOR SCOTT
SELECT * FROM dept;
DEPTNO DNAME LOC
------- -------------- ------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM dept1;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
Is there any STORED PROCEDURE available .
Thanks in Advance
gandhi
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|