Stored procedure not returning correct result

Question

I have a stored procedure like this

CREATE OR REPLACE PROCEDURE schema_name.CHECKS
IS
   tbl_name        VARCHAR2 (50);
   constraint_nm   VARCHAR2 (100);
   CURSOR cur_constraint
   IS
      SELECT DISTINCT table_name, constraint_name
        FROM all_constraints
       WHERE     constraint_type = 'R'
             AND STATUS = 'ENABLED'
             AND R_OWNER = 'owner1'
             AND r_constraint_name = 'constraint1';
BEGIN
   DBMS_OUTPUT.put_line ('Constraint Name');
   OPEN cur_constraint;
   LOOP
      FETCH cur_constraint
      INTO tbl_name, constraint_nm;
      EXIT WHEN cur_constraint%NOTFOUND;
      DBMS_OUTPUT.put_line (constraint_nm||'~~'||tbl_name);
   END LOOP;
   close cur_constraint;
END CHECKS;

And I execute this procedure by

set serveroutput on

BEGIN
   schema_name.CHECKS ();
END;

And the output I get is

Procedure created.
Constraint Name
PL/SQL procedure successfully completed.

It's not returning any result but ideally it should be returning a row (the select query used to define the cursor will return a row).

When I execute the above code as a PL/SQL Block like this

DECLARE
   tbl_name        VARCHAR2 (50);
   constraint_nm   VARCHAR2 (100);
   CURSOR cur_constraint
   IS
      SELECT DISTINCT table_name, constraint_name
        FROM all_constraints
       WHERE     constraint_type = 'R'
             AND STATUS = 'ENABLED'
             AND R_OWNER = 'owner1'
             AND r_constraint_name = 'constraint1';
BEGIN
   FOR i IN cur_constraint
   LOOP
      EXIT WHEN cur_constraint%NOTFOUND;
      DBMS_OUTPUT.put_line (i.constraint_name||' is in '||i.table_name);
   END LOOP;
END;

It is returning one row as expected.

Please help me understand why it is behaving strange when the logic is same except the way I execute it.

Answer 1

I imagine this is because your schema has access to some 'owner1' schema objects only via a role and not granted directly. Roles are not taken into account by stored procedures. See this AskTom thread for more details.

As Gary Myers says, you can change the procedure to:

CREATE OR REPLACE PROCEDURE schema_name.CHECKS
AUTHID CURRENT_USER
IS
...

and it will then show constraints that the user running it can see.

Answer 2

ALL_CONSTRAINTS is a bit like a mirror. Every user will see something different in there based on grants to that user. When executed as a DEFINE RIGHTS stored procedure it shows only what the owner of the procedure can see as a result of privileges granted directly to the owner (not through a role).

When executed as an anonymous block, it will show what the running user can see as a result of privileges granted to the user directly OR through a currently active role.

An invoker rights stored procedure (google AUTHID CURRENT_USER) will show what the invoking user can see as a result of grants to the user directly or through a currently active role.