Why does my query return no records inside a program (PL/SQL), but does when ran manually in SQL?

user_15251441 0
Viewed 72

Hi everyone and thank you for taking the time to help me.

I have the following query:

SELECT   owner, object_name
FROM     all_objects
WHERE    owner IN ('EDI')
ORDER BY object_type, object_name;

Manual Query Results

As you can see in the screenshot it returns some values.

When I call the query from inside a program, it is not returning any values (see second screenshot).

Stored Procedure Results

Test code to illustrate this is:

CREATE OR REPLACE PROCEDURE my_test
AS
BEGIN
    DBMS_OUTPUT.put_line('Pre-Loop');

    FOR indx IN (SELECT   owner, object_name
                 FROM     all_objects
                 WHERE    owner IN ('EDI')
                 ORDER BY object_type, object_name)
    LOOP
        DBMS_OUTPUT.put_line('Object: ' || indx.owner || '.' || indx.object_name);
    END LOOP;

    DBMS_OUTPUT.put_line('Post-Loop');
END;
/

BEGIN
    my_test();
END;
/

The EDI schema is brand new, so I suspect this is a grants/privileges issue, but I can't seem to find what I may be missing in order for this to work. I have tried running this as both the EDI user and SYS.

EDIT after getting an answer:

I mentioned in a comment about finding an alternative to the official answer to this question and wanted to make sure it was shared for anyone reading this later so they can weigh the decision the same.

Applying grants like EXECUTE ANY PROCEDURE or SELECT ANY TABLE to the user that is expected to run the code will work, but I am sure there are reasons not to give such wide open grants.

plsql oracle sql
2 Answers

Your stored procedure is a definer's rights stored procedure. That means that it doesn't have access to privileges that are granted via roles only those privileges that are granted directly to the owner of the procedure. Ad hoc SQL, on the other hand, runs with the privileges of whatever roles are enabled for the current session in addition to the user's direct grants. Most likely, the owner of the procedure has access to the tables in question via roles rather than via direct grants.

You can test this by running

set role none;

and then running the ad hoc SQL statement. If my wager is right, the ad hoc SQL will now return 0 rows since you've disabled all the roles for the session.

Depending on what you are going to do with the procedure, you may be able to solve the problem by turning it into an invoker's rights stored procedure.

CREATE OR REPLACE PROCEDURE my_test
  AUTHID CURRENT_USER
AS

That will cause the procedure to run with the privileges of the invoker's session (including privileges granted through roles) rather than those of the definer. Assuming that all the users you want to call the procedure will have access to the EDI tables, that should be sufficient.

Justin_Cave_103970

This is what you have now - no result at all:

SQL> CREATE OR REPLACE PROCEDURE my_test
  2  AS
  3  BEGIN
  4      DBMS_OUTPUT.put_line('Pre-Loop');
  5
  6      FOR indx IN (SELECT   owner, object_name
  7                   FROM     all_objects
  8                   WHERE    owner IN ('SCOTT')
  9                     AND    rownum < 5               -- you don't have that
 10                   ORDER BY object_type, object_name)
 11      LOOP
 12          DBMS_OUTPUT.put_line('Object: ' || indx.owner || '.' || indx.object_name);
 13      END LOOP;
 14
 15      DBMS_OUTPUT.put_line('Post-Loop');
 16  END;
 17  /

Procedure created.

SQL> BEGIN
  2      my_test();
  3  END;
  4  /

PL/SQL procedure successfully completed.

But, if you enable serveroutput, here it is!

SQL> set serveroutput on                       --> this!
SQL>
SQL> BEGIN
  2      my_test();
  3  END;
  4  /
Pre-Loop
Object: SCOTT.BONUS
Object: SCOTT.DEPT
Object: SCOTT.EMP
Object: SCOTT.SALGRADE
Post-Loop

PL/SQL procedure successfully completed.

SQL>
user_90979060
Related