5 Replies Latest reply on Dec 31, 2015 9:58 AM by Gayathri L-Oracle

    AD_ZD.GRANT_PRIVS fails to provide grant on table having partition

    Gayathri L-Oracle

      When user is trying to provide grant on table(with partition) present in apps to some other role, it gives following error:

       

      ORA-01422: exact fetch returns more than requested number of rows

      ORA-06512: at "APPS.AD_ZD", line 1130

      ORA-06512: at line 1

       

      Is there any limitation of using AD_ZD api for granting any privilege to partitioned table, since for other objects it works fine.

        • 1. Re: AD_ZD.GRANT_PRIVS fails to provide grant on table having partition
          Hussein Sawwan-Oracle

          Please post details of application release, database version and OS along with TXK/AD patchset level you're on.

           

          Also, please post the exact command you're trying to run and the table definition. Sharing those steps will allow us to test internally and see if we can reproduce the issue.

           

          Thanks,

          Hussein

          • 2. Re: AD_ZD.GRANT_PRIVS fails to provide grant on table having partition
            Gayathri L-Oracle

            Hello Hussein,

             

            application release- 11.5.0

            Oracle Linux 5

            AD    R12.AD.C.5

            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production'

             

            While executing the below from apps:

            EXEC AD_ZD.GRANT_PRIVS('SELECT','XXRBS_XLA_AC_BALANCES' ,'XXRBS_RBIFCM_APP_ROLE');

            where 'XXRBS_XLA_AC_BALANCES' is a table(which is having partition) created in custom schema and its synonym present in apps and we are trying to grant 'SELECT' from APPS to 'XXRBS_RBIFCM_APP_ROLE', it is giving below error:

            ORA-01422: exact fetch returns more than requested number of rows
            ORA-06512: at "APPS.AD_ZD", line 1130
            ORA-06512: at line 1


            Thanks,

            Gayathri

            • 3. Re: AD_ZD.GRANT_PRIVS fails to provide grant on table having partition
              Gayathri L-Oracle

              I have updated the requested information could you please check and update

              • 4. Re: AD_ZD.GRANT_PRIVS fails to provide grant on table having partition
                Bashar.

                You are running R12.2, not 11.5.0!

                This document may be useful even though it addresses a different issue:

                 

                Granting Privileges On An Object May Cause Invalidations (Doc ID 1987947.1)

                 

                Regards,

                Bashar

                • 5. Re: AD_ZD.GRANT_PRIVS fails to provide grant on table having partition
                  Gayathri L-Oracle

                  TESTING Performed in an Internal Instance
                  =================================

                  EXECUTE apps.AD_ZD.GRANT_PRIVS('select','FND_CONCURRENT_REQUESTS','PO');


                  SQL> EXECUTE apps.AD_ZD.GRANT_PRIVS('select','FND_CONCURRENT_REQUESTS','PO');

                  PL/SQL procedure successfully completed.

                  SQL> SHOW USER
                  USER is "APPS"


                    1* SELECT TABLE_NAME,PARTITIONED from DBA_TABLES WHERE PARTITIONED='YES' and table_name like 'AD%';


                  SQL> SELECT TABLE_NAME,PARTITIONED from DBA_TABLES WHERE PARTITIONED='YES' and table_name like 'AD%';

                  TABLE_NAME                     PAR
                  ------------------------------ ---
                  AD_PARALLEL_UPDATE_UNITS       YES

                  SQL> EXECUTE apps.AD_ZD.GRANT_PRIVS('select','AD_PARALLEL_UPDATE_UNITS','PO');

                  PL/SQL procedure successfully completed.

                  SQL> show user
                  USER is "APPS"


                  But user replied that


                  Please note that the FND_CONCURRENT_REQUESTS and AD_PARALLEL_UPDATE_UNITS are the synoyms based on view which is present in APPLSYS. So the below query returns only one row irrespective of partition:

                  select syn.table_owner, syn.table_name, obj.object_type, obj.edition_name
                  --into l_object_owner, l_object_name, l_object_type, l_edition_name
                  from dba_synonyms syn, dba_objects obj
                  where syn.owner = 'APPS'
                  and syn.synonym_name ='FND_CONCURRENT_REQUESTS'
                  and obj.owner = syn.table_owner
                  and obj.object_name = syn.table_name
                  and obj.namespace = 1;

                  where as for the object XXRBS_XLA_AC_BALANCES, the synonym in apps is based on table, so it the above query fetches multiple row due to partition.


                  --------------

                  Wanted to know whether this functionality will work or not?


                  Thanks,

                  Gayathri