Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Red face Unanswered: How do I create a view using WITH READ ONLY option in 8i

    I'm trying to create a view that has a subquery in it using the with read only option in Oracle 8i. When I execute the DDL script I get no errors, but when I try to grant permissions to the newly created view within the same script (after issuing a commit) I get a mesage that the view or table is not found.

    The only way I am able to create the view is by removing the with read only option from the end of the view creation command.
    Regards,
    Terry

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: How do I create a view using WITH READ ONLY option in 8i

    Not sure what the problem is without looking at your code, but here is an example that works for me when I replace the placeholders with real values.

    create or replace view view_name as
    select column1, column2
    from table1
    where column3 = (select column4
    from table2
    where column5 = 'value')
    with read only;

    grant select on view_name to user_name/role_name;

  3. #3
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Exclamation Oracle known bug ...

    I found that Oracle knows this as a bug. There is already a TAR logged on the problem. The situation WILL happen to you if you try to create a read only view AND also try to use the ORDER BY clause (which was what I was trying to do. Thanks for the help.

    By the way, the work-around Oracle suggests (i.e. it doesn't look like they're going to work on solving the problem soon) is to create the view as read only and then create another view with the ordering -- a view of a view.
    Last edited by spraguetr; 06-16-03 at 09:48.
    Regards,
    Terry

Posting Permissions

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