Select all the Rows ........ !!!!!

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 .

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.

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

Dear Friends,

I have writted PL/SQL programme for select all the records from all the tables of one particular user.

It says PL/SQL sucessfully completed but, it did not written any output.

Could any suggest a better procedure.


SET SERVEROUTPUT ON
declare
t varchar2(30);
begin
for t in (select table_name from user_tables) loop
execute immediate 'select * from ' || t.table_name;
end loop;
end;




Thanks in advance

gandhi