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