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;
/

• SHR: Thanks for reply. I added the sqlplus transcript to show how I can run the query from sqlplus but fail to add the same row when execute from proc.