Dear All,
I can CREATE and EXECUTE others procedures however can not execute procedure
with DBMS_SQL. I'm created procedure has parameters to create a view using
DBMS_SQL. Oracle gave me an error insufficent privileges. Can give some
hints how make it works or any example.
TIA
Truong.
SQL
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
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
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: Any clod can have
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
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 10:02:16 -0800
Right. Forgot
;
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
Donate:
Does the owner
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
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 PROCEDURE
