Skip to Main Content
  • Questions
  • ora-01720 when creating a view with sys.dual

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jeannette.

Asked: February 11, 2016 - 3:07 pm UTC

Last updated: May 21, 2018 - 12:24 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

I have this create view statement that is failing with ORA-01720: grant option does not exist for 'SYS.DUAL' on line 7

create or replace view v_decisions
(Sort_No, Name, Abbreviation, Include_In_EM, WPSetup_ID, Include_In_MBP, Web_Name )
as
select Sort_No, Name, Abbreviation, Include_In_EM, WPSetup_ID, Include_In_MBP, Web_Name
from decision
union
select -2, 'Inquiry', 'INQ', 'N', null, 'N', null from dual
union
select -1, 'Applicant', 'APP', 'N', null, 'N', null from dual
;

The user was created by the sys user as sysdba and given the select on sys.dual with this statement.
grant select on sys.dual to newuser with grant option;

I am running Oracle 12c release 1201000100.

This code runs without problems in 10g.

What do I need to do to create this view in 12c?

Jeannette

and Chris said...

You shouldn't need to grant select on dual to your user explicitly - it should already have access:

create user tu identified by tu;
grant connect, create view to tu;

conn tu/tu

create or replace view v_decisions ( sort_no, name, abbreviation, include_in_em, wpsetup_id, include_in_mbp, web_name )
as
  select -2, 'Inquiry', 'INQ', 'N', null, 'N', null from dual
  union
  select -1, 'Applicant', 'APP', 'N', null, 'N', null from dual
  ;
select * from v_decisions;

   SORT_NO NAME      ABB I WPSETUP_ID I WEB_NAME
---------- --------- --- - ---------- - --------
        -2 Inquiry   INQ N            N         
        -1 Applicant APP N            N

You'll need to take this up with support.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jeannette - try a drop

Connor McDonald, February 12, 2016 - 1:15 am UTC

Hi Jeannette,

Some versions of the database had an issue with "replacing" a view.

Try dropping the view, then creating it from scratch. See if that helps.

Cheers,
Connor

of course if you replace the view :)

Laurent Schneider, February 12, 2016 - 7:57 am UTC

SQL> create table t (x number);

Table created.

SQL> create view v as select * from t;

View created.

SQL> grant delete on v to public;

Grant succeeded.

SQL> create or replace view v as select * from dual;
create or replace view v as select * from dual
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.DUAL'

Connor McDonald
February 12, 2016 - 8:20 am UTC

Thanks for the addition Laurent.

Works for me

Miles Smith, May 17, 2016 - 6:53 pm UTC

thanks Connor - dropping the view and not having "or replace" in the ddl did the trick. It worked with a "union all" but when I added the "union all" it would fail until I tried your suggestion.
Chris Saxon
May 18, 2016 - 1:53 am UTC

Glad it worked out for you

Zahir, May 18, 2018 - 4:53 pm UTC

Why would Oracle issue a error ( ORA-01720) when I am recreating a view with CREATE / REPLACE .

But the same view gets created , when I dropped it first and create it again .

DROP
CREATE

Works.

Bur not CREATE / REPLACE.
Chris Saxon
May 21, 2018 - 12:24 pm UTC

In the example from Laurent, you're granting delete on the view to public. So you also need delete on the underlying objects. Which you can't grant on sys.dual (unless you're sys).

Dropping the view removes any privileges. So this no longer an issue.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library