stored procedure vs anonymous block - same data, different results

Question

I am having trouble getting my head around different results I get when running the same code using anonymous block and stored procedure. They both take some details from data dictionary, but it looks like the stored procedure cannot pull full data:

CREATE OR REPLACE PROCEDURE testing IS
    lv_schema VARCHAR2(10) := 'some_schema';
    total NUMBER;
    CURSOR tab_cur IS 
      SELECT    table_name
              , column_name
      FROM    all_tab_columns 
      WHERE   OWNER = lv_schema;
    in_record tab_cur%ROWTYPE;
  BEGIN
    total := 0;
    OPEN tab_cur;
    LOOP
      FETCH tab_cur INTO in_record;
      EXIT WHEN tab_cur%NOTFOUND;
      total := total + 1;
    END LOOP;
    CLOSE tab_cur;
    dbms_output.put_line(total);
  END testing;

For my own schema it gives me correct results (small schema, finds 13 results). For one of our smaller apps it gives incomplete result (50, instead of expected 83), while for another one (big) it lists 0, instead of 5181).

I have dismissed the idea this is because of privileges (I can view all the results correctly when I query data dictionary directly with SELECT), and thought it may be size-related (cursor failing when there is too many results, dunno), but I get all correct results when I run the very same code in anonymous block:

DECLARE
  lv_schema VARCHAR2(10) := 'some_schema';
  total NUMBER;
  CURSOR tab_cur IS 
    SELECT    table_name
            , column_name
    FROM    all_tab_columns 
    WHERE   OWNER = lv_schema;
  in_record tab_cur%ROWTYPE;
BEGIN
  total := 0;
  OPEN tab_cur;
  LOOP
    FETCH tab_cur INTO in_record;
    EXIT WHEN tab_cur%NOTFOUND;
    total := total + 1;
  END LOOP;
  CLOSE tab_cur;
  dbms_output.put_line(total);
END;

The only changes here being "DECLARE" instead of "CREATE OR REPLACE FUNCTION testing IS" and removal of function name from END line... Anyone could point me towards some explanation?

Thanks!

Answer 1

William Robinson's comment pointed me to a solution.

Here is a relevant quote from Configuring Privilege and Role Authorization in the Oracle documentation:

Roles Used in Named Blocks with Definer's Rights

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights.

My access to data dictionary table used (all_tab_columns) was granted through a role. That is why it was not taken into account when I ran the query in a stored procedure. The solution here is to create a procedure and force it to use invoker rights rather than definer rights (which is the default) by using the AUTHID CURRENT_USER clause when creating the procedure.