RE: dbms_pipe

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' OR
select * from dba_tab_privs where grantor =3D 'SYS' and grantee IN ( select username from dba_users)

HTH,
Bruce Reardon

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

We upgraded a database from 8.1.7.4 to 9.2.0.5 recently. The database = is=20
for one application only. So the account for the application was = granted=20
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 = 'execution=20
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 = could=20
not compile the package until I granted the 'execution on dbms_pipe' to = the=20
account explicitly. Does any one know if the privilege has been changed = in=20
9i? I cannot find any thing in the document. Our DBA role is a default =

role from Oracle + 'select any dictionary'.

NOTICE
=20

This e-mail and any attachments are private and confidential and=20 may contain privileged information.
=20

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.
=20

This notice should not be removed.

--
http://www.freelists.org/webpage/oracle-l