Re: Oracle 7.34 Anonymous PL/SQL vs Stored Procedure ?

A copy of this was sent to Jeff Blakely <jblakely_at_powervision.com> (if that email address didn't require changing) On Thu, 03 Jun 1999 18:00:01 -0400, you wrote:

>Oracle workgroup server for NT v7.34
>
>We are experiencing different query results when selecting from
>all_objects. We are experiencing different behavior when the query is
>executed as an anonymous PL/SQL block vs. a stored object (stored
>procedure).
>
>SELECT COUNT(*)
> FROM all_objects
> WHERE object_name = 'TAB$';
>
>Results is 1. - ok
>
>CREATE OR REPLACE PROCEDURE SingleRowCursor(iCount OUT INTEGER)
>IS
>BEGIN
> SELECT COUNT(*)
> INTO iCount
> FROM all_objects
> WHERE object_name = 'TAB$';
>
> DBMS_OUTPUT.PUT_LINE(icount);
>END;
>/
>
>When the stored procedure is executed with the following anonymous
>PL/SQL block we get unexpected result of 0.
>DECLARE
> jOwner VARCHAR2(3) := 'SYS';
> jTable VARCHAR2(4) := 'TAB$';
> jCount NUMBER;
>BEGIN
> SINGLEROWCURSOR(jCount);
>
> DBMS_OUTPUT.PUT_LINE(jCount);
>END;
>
>Result output is 0.
>
>Can anyone explain what causes the different result?
>
>TIA,
>Jeff
>

roles are never enabled during the execution of a procedure.

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

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