RE: dbms_pipe

  • From: "Reardon, Bruce (CALBBAY)" <Bruce.Reardon@xxxxxxxxxxxxxxxxxxxxxxx>
  • To: <davidb1588@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 15 Sep 2004 08:40:25 +1000

Hi David,
How did you upgrade?
Did you use an export by chance (which would exclude sys grants)?
Privileges granted through a role (eg DBA execute any proc) don't work =
in stored code but do from SQL / anonymous PL SQL
so, that (I believe) is why you can execute from anonymous code but not =
from a stored procedure.

In your test system, try logging on as sysdba and granting execute on =
sys.dbms_pipe to account_owner.

IF that works, keep a separate script(s) of all the sys grants - these =
would need to be rerun if rebuilding from an export

Do you still have a version of your 8174 database available?
You could see if this had been done by using
 select * from dba_tab_privs where grantee=3D'account_owner'
select * from dba_tab_privs where grantor =3D 'SYS'
and grantee IN ( select username from dba_users)

Bruce Reardon

-----Original Message-----
From: David Boyd
Sent: Wednesday, 15 September 2004 6:53 AM

We upgraded a database from to recently.  The database =
for one application only.  So the account for the application was =
all the system privileges directly in 8i.  A procedure in a package =
owned by=20
the account calls DBMS_PIPE.  In 8i the account was not granted =
on dbms_pipe' explicitly.  After the upgrade, the package body became=20
invalid.  I got "PLS-00201: identifier 'DBMS_PIPE' must be declared" =
when I=20
compiled the package.  Then I tried to grant the DBA role to the =
account. =20
An anonymous block that calls dbms_pipe ran successfully.  But I still =
not compile the package until I granted the 'execution on dbms_pipe' to =
account explicitly.  Does any one know if the privilege has been changed =
9i?  I cannot find any thing in the document.  Our DBA role is a default =

role from Oracle + 'select any dictionary'.


This e-mail and any attachments are private and confidential and=20
may contain privileged information.
If you are not an authorised recipient, the copying or distribution=20
of this e-mail and any attachments is prohibited and you must not=20
read, print or act in reliance on this e-mail or attachments.
This notice should not be removed.

Other related posts: