Oracle: Query gives different answer when running from procedure

Question

I guess my problem is somehow related with grant permissions.

I have an "ADMIN" user, I grant it "dba" permissions like this:

grant dba to admin

Now in the "ADMIN" schema I have 2 tables:

The 1st table called TABLES_LIST with columns:

SCHEMA_NAME varchar2;
TABLE_NAME varchar2;
PRIMARY KEY(SCHEMA_NAME,TABLE_NAME);

And the 2nd table called TABLES_PKEYS with the following columns:

SCHEMA_NAME varchar2
TABLE_NAME varchar2
COLUMN_NAME varchar2
PKEY_INDEX  NUMBER 

Now I have a procedure created by the same user, in manner to be used from trigger and update the 2nd table whenever the 1st been updated:

CREATE PROCEDURE INSERT_NEW(IN_SCHEMA_NAME varchar2, IN_TABLE_NAME varchar2)      
AS
BEGIN
  BEGIN
    WRITE_LOG ('INSERTED ' || IN_SCHEMA_NAME || '.' || IN_TABLE_NAME );
    INSERT INTO TABLES_PKEYS (SCHEMA_NAME,TABLE_NAME,COLUMN_NAME,PKEY_INDEX) 
      (SELECT cons.owner, cols.table_name,cols.column_name,cols.position)
       FROM all_constraints cons, all_cons_columns cols
       WHERE cols.owner=IN_SCHEMA_NAME
       AND   cols.TABLE_NAME=IN_TABLE_NAME
       AND   cons.constraint_type = 'P'
       AND   cons.constraint_name = cols.constraint_name
       AND   cons.owner = cols.owner );   
  EXCEPTION WHEN OTHERS THEN
    WRITE_LOG ('ERROR WHEN INSERTING ' || SQLERRM );
  END;
END INSERT_NEW;

When I call my procedure with argument from my "ADMIN" schema, it works fine, (for example, when i give the first table it fills the 2 keys,) but when I try to give it input from other schema, the select in the procedure get empty result.

When I copy the whole insert query and run it (with the same user) from sql developer, it works and add the keys successfully.

Is there any additional permission I need to add in manner to run the query from procedure or from trigger?

Thanks.

EDIT

C:\Users\Administrator>sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Apr 11 13:23:47 2016

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Enter user-name: admin
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
ADMIN                          CREATE SESSION                           NO
ADMIN                          CREATE TABLE                             NO
ADMIN                          UNLIMITED TABLESPACE                     NO


SQL> insert into demo.test1 values(1,'aaa');

1 row created.
--So I can insert to table on other schema located on other tablespace (HOW? I don't know) 

SQL> select * from TABLES_PKEYS;

no rows selected

SQL> exec INSERT_NEW('DEMO','TEST1');

PL/SQL procedure successfully completed.

SQL> select * from TABLES_PKEYS;

no rows selected
--So adding from proc failed

SQL> SELECT cons.owner, cols.table_name, cols.column_name, cols.position
  2          FROM all_constraints cons, all_cons_columns cols
  3          WHERE cols.owner = 'DEMO'
  4          AND cols.table_name = 'TEST1'
  5          AND cons.constraint_type = 'P'
  6          AND cons.constraint_name = cols.constraint_name
  7          AND cons.owner = cols.owner;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

POSITION
----------
DEMO                           TEST1
TID
         1
--I can select the keys

SQL> INSERT INTO tables_pkeys (SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, PKEY_INDEX)
  2         (SELECT cons.owner, cols.table_name, cols.column_name, cols.position
  3          FROM all_constraints cons, all_cons_columns cols
  4          WHERE cols.owner = 'DEMO'
  5          AND cols.table_name = 'TEST1'
  6          AND cons.constraint_type = 'P'
  7          AND cons.constraint_name = cols.constraint_name
  8          AND cons.owner = cols.owner );

1 row created.
--I can even insert to my table

SQL> select * from TABLES_PKEYS;

SCHEMA_NAME                          TABLE_NAME
------------------------------------ ------------------------------------
COLUMN_NAME                                        PKEY_INDEX
-------------------------------------------------- ------------
DEMO                                 TEST1
TID                                                           1

-- conclusion: I can run the command manually, not from proc.

Answer 1

ALL_CONSTRAINTS describes constraint definitions on tables accessible to the current user.

If you want to select constraints of a table which belongs to another schema then you should have select privilege(even if you have dba role assigned to the user) on that table.

...When I copy the whole insert query and run it (with the same user) from sql developer, it works and add the keys successfully...

SQL> conn admin/admin
Connected.
SQL> INSERT INTO TABLES_PKEYS SELECT cons.owner,cols.table_name,cols.column_name,cols.position FROM all_constraints cons, all_cons_columns cols WHERE cols.owner='HR' AND cols.TABLE_NAME='JOBS' AND cons.constraint_type = 'P' AND cons.constraint_name =cols.constraint_name AND cons.owner = cols.owner;

0 rows created.

SQL> conn hr/hr
Connected.

SQL> INSERT INTO ADMIN.TABLES_PKEYS SELECT cons.owner,cols.table_name,cols.column_name,cols.position FROM all_constraints cons, all_cons_columns cols WHERE cols.owner='HR' AND cols.TABLE_NAME='JOBS' AND cons.constraint_type = 'P' AND cons.constraint_name =cols.constraint_name AND cons.owner = cols.owner;

1 row created.

SQL> conn admin/admin
Connected.
SQL> select * from tables_pkeys;

SCHEMA_NAME      TABLE_NAME COLUMN_NAME      PKEY_INDEX
-------------------- ---------- -------------------- ----------
HR           EMPLOYEES  EMPLOYEE_ID           1
HR           JOBS   JOB_ID                1

SQL> 

...but when I try to give it input from other schema, the select in the procedure get empty result...

SQL> conn admin/admin
Connected.
SQL> SELECT cons.owner,cols.table_name,cols.column_name,cols.position FROM all_constraints cons, all_cons_columns cols WHERE cols.owner='HR' AND cols.TABLE_NAME='JOBS' AND cons.constraint_type = 'P' AND cons.constraint_name =cols.constraint_name AND cons.owner = cols.owner;

no rows selected

SQL> conn hr/hr
Connected.
SQL> grant select on jobs to admin;

Grant succeeded.

SQL> SELECT cons.owner,cols.table_name,cols.column_name,cols.position FROM all_constraints cons, all_cons_columns cols WHERE cols.owner='HR' AND cols.TABLE_NAME='JOBS' AND cons.constraint_type = 'P' AND cons.constraint_name =cols.constraint_name AND cons.owner = cols.owner;

OWNER      TABLE_NAME COLUMN_NAME        POSITION
---------- ---------- -------------------- ----------
HR     JOBS       JOB_ID                1

SQL> exec insert_new('HR','EMPLOYEES');

PL/SQL procedure successfully completed.

SQL> select * from tables_pkeys;

no rows selected

SQL> conn hr/hr
Connected.
SQL> grant select on employees to admin;

Grant succeeded.

SQL> conn admin/admin
Connected.
SQL> set serveroutput on
SQL> exec insert_new('HR','EMPLOYEES');
INSERTED HR.EMPLOYEES

PL/SQL procedure successfully completed.

SQL> select * from tables_pkeys;

SCHEMA_NAME      TABLE_NAME       COLUMN_NAME          PKEY_INDEX
-------------------- -------------------- -------------------- ----------
HR           EMPLOYEES        EMPLOYEE_ID           1

EDIT
The procedure I have used is:

CREATE OR REPLACE PROCEDURE INSERT_NEW(IN_SCHEMA_NAME varchar2, IN_TABLE_NAME varchar2)      
AS
BEGIN
INSERT INTO TABLES_PKEYS SELECT cons.owner,cols.table_name,cols.column_name,cols.position FROM all_constraints cons, all_cons_columns cols WHERE cols.owner=IN_SCHEMA_NAME AND cols.TABLE_NAME=IN_TABLE_NAME AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner;   
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('ERROR WHEN INSERTING ' || SQLERRM );
END INSERT_NEW;
/