Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Select on ALL_TABLES gives different results (SQL vs PROCEDURE)

fcjunicJun 14 2012 — edited Jun 14 2012
Hello,

I connected with user A and I ran this SQL :
SELECT COUNT(*)
FROM ALL_TABLES
WHERE Table_Name = 'SRMD'
AND Owner = 'DMI';
The count() returns a value of 1.

Then I created a procedure called TestAllTables with exactly the same statement :
CREATE OR REPLACE PROCEDURE A.TestAllTables(p_Table IN VARCHAR2, p_Schema IN VARCHAR2) IS
  l_Nb  NUMBER;
 
BEGIN
  SELECT COUNT(*) 
  INTO l_Nb
  FROM ALL_TABLES
  WHERE Table_Name = p_Table
    AND Owner = p_Schema;
    
  IF l_Nb = 0 THEN 
    dbms_output.put_line('KO !');
  ELSE
    dbms_output.put_line('OK...');
  END IF;
END;
...and I ran it still connected to A :
BEGIN
    TestAllTables('SRMD','DMI');
END;
/
...and it gives me that incomprehensible error : KO !

Please can someone explain me why ?

Thks.
This post has been answered by JustinCave on Jun 14 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 12 2012
Added on Jun 14 2012
4 comments
782 views