DBMS_PIPES

Claude A. Charles

unread,

Aug 29, 1997, 3:00:00 AM8/29/97

to

--------------29C2D713ED39496C05440939
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi

I am working with DBMS_PIPES and I am running through a number of
problems. If I try to call the procedures in the DBMS_PIPE package in my
procedures I get this error message: PLS-00201: identifier
SYS.DBMS_PIPE' must be declared. But if I run the
SQL*Plus utility and I type the following code sample below on the
command line the execution is successful.

BEGIN
DBMS_PIPE.PACK_MESSAGE('X')
END;
/

This is an example of how I am calling the DBMS_PIPE procedures in my
procedures:

CREATE OR REPLACE PROCEDURE TEST_PRC
IS
BEGIN
DBMS_PIPE.PACK_MESSAGE('X');
END TEST_PRC;
/

This gives me the error mentioned above. The system administration has
look at my account to verify that I have permission to use the system
resources. I also have EXECUTION privilege on the resources. The
DBMSPIPE.SQL and PRVTPIPE.PLB scripts were also executed by the system
administrator.

I am using Designer 2000 from Oracle 7. Any ideas what my problem(s)
could be and I can I rectify them?

Thanks.

Claude Charles
claude_...@mail.amsinc.com

--------------29C2D713ED39496C05440939
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>
Hi

I am working with DBMS_PIPES and I am running through a number of problems.
If I try to call the procedures in the DBMS_PIPE package in my procedures
I get this error message:  PLS-00201: identifier SYS.DBMS_PIPE'
must be declared. But if I run the
 SQL*Plus utility and I type the following code sample below on the
command line the execution is successful.

BEGIN
            
DBMS_PIPE.PACK_MESSAGE('X')
 END;
 /

This is an example of how I am calling the DBMS_PIPE procedures in my
procedures:

CREATE OR REPLACE PROCEDURE TEST_PRC
 IS
 BEGIN
         DBMS_PIPE.PACK_MESSAGE('X');
 END TEST_PRC;
 /

This gives me the error mentioned above. The system administration has
look at my account to verify that I have permission to use the system resources.
I also have EXECUTION privilege on the resources. The DBMSPIPE.SQL and
PRVTPIPE.PLB scripts were also executed by the system administrator.

I am using Designer 2000 from Oracle 7. Any ideas what my problem(s)
could be and I can I rectify them?

Thanks.

Claude Charles
 claude_...@mail.amsinc.com</HTML>

--------------29C2D713ED39496C05440939--

Jcen

unread,

Sep 14, 1997, 3:00:00 AM9/14/97

to

Hi,

I am also having this problem.
Anybody has any solution?
It's from the example in the Manuals.
Thanks.

E. A.

unread,

Sep 15, 1997, 3:00:00 AM9/15/97

to

The DBMS_PIPE package has been giving me all sorts of problems (strangely
enough not on all machines, though). Couple of things to try:
1) Run both scripts (dbmspipe.sql and prvtpipe.plb) again as sys
2) grant the user that needs to use the package the "execute any procedure"
privilege (the weird thing about this is that even if I grant dba to my
user, which includes "execute any procedure", I still have to grant this
*explicitly* on some machines to get it to work)

Cheers, E.

Jcen <ha...@programmer.net> wrote in article
<341b7e7f...@news.singnet.com.sg>...

Thomas Kyte

unread,

Sep 15, 1997, 3:00:00 AM9/15/97

to

You cannot create stored objects ( procedures, packages, views, etc) that access
objects you have access to via a ROLE. Roles are always disabled during
procedure/view execution.

To test if you have the need priveleges to access an object in a stored
procedure or view, you can (and should) do the following in SQL*Plus:

SQL> set role none;
SQL> describe OBJECT_YOU_WANT_TO_ACCESS
SQL> set role all;

If the describe succeeds, you have the correct priveleges. If the describe
fails, you will not be able to create a stored object that accesses that object.
You will need to GRANT execute/select/whatever on OBJECT to THAT_USER. You need
direct access, not via a role.

On Sun, 14 Sep 1997 06:05:00 GMT, ha...@programmer.net (Jcen) wrote:

>Hi,
>
> I am also having this problem.
> Anybody has any solution?
> It's from the example in the Manuals.
> Thanks.
>
>
>
>On Fri, 29 Aug 1997 15:42:31 -0400, "Claude A. Charles"
><claude_...@mail.amsinc.com> wrote:
>>I am working with DBMS_PIPES and I am running through a number of
>>problems. If I try to call the procedures in the DBMS_PIPE package in my
>>procedures I get this error message: PLS-00201: identifier
>>SYS.DBMS_PIPE' must be declared. But if I run the
>>SQL*Plus utility and I type the following code sample below on the
>>command line the execution is successful.
>>
>>BEGIN
>> DBMS_PIPE.PACK_MESSAGE('X')
>>END;
>>/
>>

Thomas Kyte
tk...@us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Dan Clamage

unread,

Sep 17, 1997, 3:00:00 AM9/17/97

to

Jcen <ha...@programmer.net> wrote in article
<341b7e7f...@news.singnet.com.sg>...

> I am also having this problem.
> Anybody has any solution?
> It's from the example in the Manuals.
> Thanks.
> On Fri, 29 Aug 1997 15:42:31 -0400, "Claude A. Charles"
> <claude_...@mail.amsinc.com> wrote:
> >I am working with DBMS_PIPES and I am running through a number of
> >problems. If I try to call the procedures in the DBMS_PIPE package in my
> >procedures I get this error message: PLS-00201: identifier
> >SYS.DBMS_PIPE' must be declared. But if I run the
> >SQL*Plus utility and I type the following code sample below on the
> >command line the execution is successful.
> >
> >BEGIN
> > DBMS_PIPE.PACK_MESSAGE('X')
> >END;
> >/
> >
>

Make sure the owner of the package, SYS, granted EXECUTE to PUBLIC. Then
the stored procedure should compile OK. Also create a public synonym for
the package.

- Dan Clamage dclamage AT idcomm DOT com