Title: RE: create view using DBMS.SQL
Do you have execute on dbms_sql granted to you? You probably do since you can compile your procedure.
What Raj was eluding to is that you need the privilege create view granted to the procedure owner directly. See below.
CREATE OR REPLACE
Title: RE: create view using DBMS.SQL
When you are compiling procedures/packages/functions (any stored code), your roles are turned off for that duration. As you are doing dynamic SQL, Oracle has no way of resolving required privileges to CREATE VIEW until runtime. Thusly your procedure gets
t * from tbl_file_definitions;
View created.
SQL> select count(*) from mytest;
COUNT(*)
--
641
From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: Re: create view using DBMS.SQL
Date: Thu, 02 Jan 2003 08:34:56 -0800
> select count(*) from mytest;
COUNT(*)
--
641
SQL>
From: "Koivu, Lisa" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: create view using DBMS.SQL
Date: Thu, 02 Jan 2003
Title: RE: create view using DBMS.SQL
Right. Forgot that...
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02,
2003 12:19 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: create view using
DBMS.SQL
ummm directly
Title: RE: create view using DBMS.SQL
ummm directly?
Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot
com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD
Title: RE: create view using DBMS.SQL
Is the create view privilege granted to the procedure owner?
-Original Message-
From: Donate Clothes [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 02, 2003 10:30 AM
To: Multiple recipients of list ORACLE-L
Subject: create view using
Donate:
Does the owner of the procedure have system privilege granted directly to
them to create a view? Not with a role , but with the system privilege
granted directly to the procedure owner. Remember, roles are disabled
inside a stored procedure.
RWB
"Donate Clothes" <[EMAIL PROTECTED]>