Oracle 7.34 Anonymous PL/SQL vs Stored Procedure ?

From: Jeff Blakely <jblakely_at_powervision.com>
Date: Thu, 03 Jun 1999 18:04:52 -0400
Message-ID: <3756FC04.FC29F5A1_at_powervision.com>



[Quoted] 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 Received on Fri Jun 04 1999 - 00:04:52 CEST

Original text of this message