OraFAQ Forum: SQL & PL/SQL » DBMS_LOB defined in a View - Error: ORA-01031: insufficient privileges

Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_LOB defined in a View - Error: ORA-01031: insufficient privileges (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_LOB defined in a View - Error: ORA-01031: insufficient privileges [message #617520]

Tue, 01 July 2014 04:31

sivakumar121
Messages: 29
Registered: April 2009

Junior Member

Hi -

I have a view in Schema A

View is like this -

CREATE OR REPLACE FORCE VIEW A.VIEW1
(
COL1,
COL2,
COL3
)
AS
   (SELECT COL1,
           COL2,
           DBMS_LOB.SUBSTR (COL3, 3000, 1) COL3
      FROM TABLE1);

View has select grants to Schema B
Also DBMS_LOB execute grants exists for both schema A and B.

When I login Schema B
and access the view - it says Error: ORA-01031: insufficient privileges
Where as if I excute the view query directly in Schema B - it works perfectly.

Let me know if any grants missing or is it because of CLOB object and we cannot view from different user?

Report message to a moderator