Skip to main content
Database Administrators

Return to Answer

added 608 characters in body
Source Link
atokpas
atokpas
  • 8.7k
  • 1
  • 17
  • 27

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

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;
/
Source Link
atokpas
atokpas
  • 8.7k
  • 1
  • 17
  • 27

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