Re: How to Use DBMS_PIPE

Connor McDonald wrote:
>
> Anil Sam wrote:
> >
> > I am trying to use DBMS_PIPE. Whenever I give the following commands from
> > SQL prompt I get the following error message. If anybody aware how to
> > overcome this problem, please let me know.
> >
> > select dbms_pipe.create_pipe('Tapas') from dual;
> >
> > ORA-06571: Function create_pipe does not gurantee not to update the
> > database.
> > Whenver I try to use any function it come up with the same error.
>
> The problem with using ANY packaged procedure in SQL is that Oracle
> cannot
> tell if the proc within the package will do anything nasty - for
> example,
> like updating the table that you are selecting from!.

Obviously, DBMS_PIPE should not be called from within SQL as above but should be done from within PL/SQL as the reply suggests. However, the error message you were getting when trying this is a bug that there is a patch for. Basically, all stored procedures (whether they are in a package or not) should have a PRAGMA RESTRICT REFERENCES clause on them which lets the database know if they intend to read, write, etc. from/to the database. There are new versions of all the packages from Oracle with these clauses so that you can use the procedures in a SQL statement if you wish. You will encounter the same problem if you write your own stored function and try to use it in a SQL statement if the function has a RAISE_APPLICATION_ERROR in it. This is a pretty common problem, I'd call Oracle support and get the patches.

Chris Halioris
Tactics, Inc.
hali_at_tacticsus.com Received on Fri Feb 28 1997 - 00:00:00 CST