DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Select all the Rows ........ !!!!!

  1. #1
    Join Date
    Nov 2000
    Posts
    101
    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

  2. #2
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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

  3. #3
    Join Date
    Nov 2000
    Posts
    101
    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.

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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

  5. #5
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    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'

  6. #6
    Join Date
    Nov 2000
    Posts
    101
    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



  7. #7
    Join Date
    Nov 2000
    Posts
    101
    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

  8. #8
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Hi gandhi,

    You will have to execute the spooled script from sqlplus.

    SQL>@myscript.sql


    HTH
    Sanjay

  9. #9
    Join Date
    Nov 2000
    Posts
    101
    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.

  10. #10
    Join Date
    Nov 2000
    Posts
    101
    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
  •  


Click Here to Expand Forum to Full Width