Re: DBMS_PIPE Experts ! Help !^%$%

>Does anyone know why the following code when compiled
>comes out with errors. Afterall, it was in the Application
>Developer's Guide:A-23
 

>The errors are listed below:
 

>Connected to:
>ORACLE7 Server Release 7.0.16.4.0 - Production
>With the procedural, distributed, and Parallel Server options
>PL/SQL Release 2.0.18.1.0 - Production
   

>Warning: Procedure created with compilation errors.
>
>Errors for PROCEDURE DEBUG:
>
>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>4/5 PLS-00201: identifier 'DBMS_PIPE' must be declared
>4/5 PL/SQL: Statement ignored
>5/5 PL/SQL: Statement ignored
>5/19 PLS-00201: identifier 'DBMS_PIPE' must be declared
>Disconnected from ORACLE7 Server Release 7.0.16.4.0 - Production
>With the procedural, distributed, and Parallel Server options
>PL/SQL Release 2.0.18.1.0 - Production
 

I'm the first to jump all over Oracle when they inevitably mess up, but your problem here is clearly a case of failure to understand and research the error. If you look up PLS-00201 in the Messages and Codes Manual, you will find:

"Cause: You tried to reference an undefined variable, exception, procedure, function, or other object. Either you failed to declare the identifier or it is not within the SCOPE of the reference."

DBMS_PIPE is a SQL package just like any other. If the user trying to execute it doesn't have access to it, he/she will get this error. The same is true if a user tries to access a table or any other schema object that they don't have access to. You need EXECUTE access to the DBMS_PIPE package which is typically owned by SYS. I would urge you, along with Oracle's documentation, to build a wrapper function so that users don't have direct access to DBMS_PIPE, but rather can only access it through your controlled procedure or function - this is both a security and integrity suggestion. Whoever owns the wrapper package obviously needs execute on DBMS_PIPE.

                                                Dave Trahan
                                                BrainTree Technology, Inc
                                                p00775_at_psilink.com