Stored procedure does not return the 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 perform this procedure

 set serveroutput on BEGIN schema_name.CHECKS (); END; 

And I get the conclusion

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

It does not return any result, but ideally it should return a string (the select query used to determine the cursor returns a string).

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 returns a single row as expected.

Please help me understand why it behaves strangely when the logic is the same, except for how I execute it.

Answer 1

ALL_CONSTRAINTS is a bit like a mirror. Each user will see in it something else, based on grants for this 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 the role).

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

The call rights stored procedure (google AUTHID CURRENT_USER) will show what the calling user can see as a result of providing grants to the user directly or through an active role.

Answer 2

I assume that this is due to the fact that your scheme has access to some objects of the "owner1" scheme only through the role and is not provided directly. Stored procedures are not taken into account. See this AskTom thread for more details .

As Gary Myers says, you can change the procedure as follows:

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

and then the restrictions that the user executing it can see will be shown.