view for 2 db
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: view for 2 db

  1. #1
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681

    view for 2 db

    Hi,

    I have two tables EMP residing on 2 diff servers, One is in
    Oracle 9i and one is on 10g. Our apps developer wants a union
    view for the two tables including their rowids.
    When I created the view i got this error:

    Create view emp_all as
    select rowid,empid,ename from emp@ora9i union all
    select rowid,empid,ename from emp@ora10g;
    *
    ora-1790 - expression must have tjhe same datatype as
    corresponding expression
    Its ok if i remove the rowid...but the programmer needs it.

    Pls help....

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    use to_char on the rowid then the datatype will be the same

  3. #3
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Thanks...I tried it but i got different error

    to_char(rowid) rowid1,ename....
    ora-932 inconsistent datatypes: expected NUMBER got ROWID


    Pls help
    Last edited by yxez; 08-30-2005 at 05:41 AM.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    change to_char to rowidtochar

  5. #5
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    thanks again davey,,,i try it tomorrow
    Last edited by yxez; 08-31-2005 at 04:22 AM.

  6. #6
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Yesss it worked you r a genius davey thanks a lot

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