1

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.
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;
/
|improve this answer|||||
  • 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. – SHR Apr 11 '16 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.