Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: Cross schema views

    Windows 2000
    Oracle 9.2

    Is it possible to do cross schema views in oracle?

    For example:

    -------------------------
    alter session set current_schema = base
    go
    create table comp1.a
    (
    aa nvarchar2(10)
    )
    go
    create table comp2.b
    (
    bb nvarchar2(10)
    )
    go
    insert into comp1.a values ('aaaaa')
    go
    insert into comp2.b values ('bbbbb')
    go
    create view v as
    select * from comp1.a
    union
    select * from comp2.b
    go
    select * from comp1.a
    go
    select * from comp2.b
    -------------------------

    All OK to this point but if I try and select from my view :

    select * from v

    I get :

    Error: ORA-04063: view "BASE.V" has errors
    (State1000, Native Code: FDF)


    Any help is most appreciated.

    Andy
    ahmATexel.co.uk

  2. #2
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Re: Cross schema views

    It looks like you can't do views referencing objects on different schemas full stop.

    Take this even simpler example:

    alter session set current_schema = base
    go
    create table comp1.a
    (
    aa nvarchar2(10)
    )
    go
    insert into comp1.a values ('aaaaa')
    go
    create view v as select * from comp1.a
    go
    select * from v

    Get:

    Error: ORA-04063: view "BASE.V" has errors
    (State1000, Native Code: FDF)

    select * from comp1.a - works fine...

    Andy
    ahmATexel.co.uk

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Cross schema views

    I don't know what you are using here, but it isn't Oracle as I know it. Your syntax looks more like SQL Server. Certainly you can create views based on tables in other schemas:

    PHP Code:
    SQLshow user;
    USER is "FOO"
    SQLcreate view v as select from tandrews.emp;

    View created.

    SQLselect from v;

         
    EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
          
    7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
          7499 ALLEN      SALESMAN        7698 20
    -FEB-1981       1600        300         30
          7521 WARD       SALESMAN        7698 22
    -FEB-1981       1250        500         30
          7566 JONES      MANAGER         7839 02
    -APR-1981       2975                    20
          7654 MARTIN     SALESMAN        7698 28
    -SEP-1981       1250       1400         30
          7698 BLAKE      MANAGER         7839 01
    -MAY-1981       2850                    30
          7782 CLARK      MANAGER         7839 09
    -JUN-1981       2450                    10
          7788 SCOTT      ANALYST         7566 19
    -APR-2087       3000                    20
          7839 KING       PRESIDENT            17
    -NOV-1981       5000                    10
          7844 TURNER     SALESMAN        7698 08
    -SEP-1981       1500          0         30
          7876 ADAMS      CLERK           7788 23
    -MAY-2087       1100                    20
          7900 JAMES      CLERK           7698 03
    -DEC-1981        950                    30
          7902 FORD       ANALYST         7566 03
    -DEC-1981       3000                    20
          7934 MILLER     CLERK           7782 23
    -JAN-1982       1300                    10

    14 rows selected


  4. #4
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Re: Cross schema views

    Found the reason for my problem - No select permission was granted to the user and so I could not use the view.

    ...I'll get my coat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •