7 Replies Latest reply on Apr 15, 2020 12:49 PM by yoonas

    Database Job Fails To Execute PL/SQL Block Because An Object Is Missing

    Mussa

      Environment:
      Oracle EBS R12.2.5, database 12c

       

       

      I have one object that is missing and I don't know what it is.  There is a database job that is  failing to be executed successfully due to the fact the object is missing.  Bear with me

      and go through my whole post.  My question is at the end of this post.

      I didn't write that pl/sql block nor created the database job.  I came to find about it when I reviewed the database log today.  So, I tried to investigate.

       

      I found below errors in the database log:

       

      alert_SID.log file

      Mon Apr 13 07:07:56 2020

      Errors in file /u01/app/oracle/product/12.1.0/dbhome_1/admin/PROD_rippedbl1/diag/rdbms/prod/PROD/trace/PROD_j000_7587.trc:

      ORA-12012: error on auto execute of job 5077

      ORA-00942: table or view does not exist

      ORA-06512: at line 1

       

       

      As per the  trace file that is mentioned in the alert_SID.log file above:

       

      Trace file /u01/app/oracle/product/12.1.0/dbhome_1/admin/PROD_rippedbl1/diag/rdbms/prod/PROD/trace/PROD_j000_15705.trc

      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

      ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1

      System name:    Linux

      Node name:      <node_name_here>

      Release:        3.10.0-693.17.1.el7.x86_64

      Version:        #1 SMP Sun Jan 14 10:36:03 EST 2018

      Machine:        x86_64

      Instance name: PROD

      Redo thread mounted by this instance: 1

      Oracle process number: 71

      Unix process pid: 15705, image: oracle@<hostname_here> (J000)

       

       

      *** 2020-04-12 22:35:31.213

      *** SESSION ID:(687.47834) 2020-04-12 22:35:31.213

      *** CLIENT ID:() 2020-04-12 22:35:31.213

      *** SERVICE NAME:(SYS$USERS) 2020-04-12 22:35:31.213

      *** MODULE NAME:() 2020-04-12 22:35:31.213

      *** CLIENT DRIVER:() 2020-04-12 22:35:31.213

      *** ACTION NAME:() 2020-04-12 22:35:31.213

       

       

      ORA-12012: error on auto execute of job 5077

      ORA-00942: table or view does not exist

      ORA-06512: at line 1

       

       

       

      In trace file, I couldn't interpret the file well.  However, I have the job ID that is failing which is 5077.

       

      So I went to query from dba_jobs

       

      select log_user,schema_user,next_date,next_sec,broken,failures,what from dba_jobs where job=5077

      Here is the output I got as shown below:

       

       

      As per the above screenshot, "what" column shows the plsql body and what it does.  As per the error, it shows that the

      job is trying to grant select on object GL_CONS_INTERFACE_1713271  to apps_query.

      The error in the trace file says table or view doesn't exist.   So I tried to query GL_CONS_INTERFACE_1713271

      to make sure it exists

       

      SQL> select count(*) from dba_objects where object_name='GL_CONS_INTERFACE_1713271';

        COUNT(*)

      ----------

               0

       

      No records was returned.

       

      That is the reason the job is failing with the following:

      ORA-12012: error on auto execute of job 5077

      ORA-00942: table or view does not exist

      ORA-06512: at line 1

       

       

       

      The question is:  What is object "GL_CONS_INTERFACE_1713271"  in Oracle EBS as I am unable to find it via Google?Is it standard or custom object?

        • 1. Re: Database Job Fails To Execute PL/SQL Block Because An Object Is Missing
          yoonas

          Hi,

           

          Have look at this note

          Consolidation Interface Tables : When is GL_INTERFACE used and when is GL_CONS_INTERFACE is used ? (Doc ID 1280484.1)

           

           

          GL_CONS_INTERFACE_1713271 , this name is dynamically supplied or its static in the job ?

           

          Any related job is run before this job ?

           

           

          Regards,

          Yoonas

          • 2. Re: Database Job Fails To Execute PL/SQL Block Because An Object Is Missing
            Mussa

            Hi 

             

             

            I  read that Doc yesterday. Thank you for sharing though.  As you see in the screenshot above  The name GL_CONS_INTERFACE_1713271 is written in the "execute immediate"  block which I assume it is static.  Though, can you share how to find it out along with any job related?

             

            Thanks  

            • 3. Re: Database Job Fails To Execute PL/SQL Block Because An Object Is Missing
              yoonas

              I was assuming there is another job which runs consolidate journal entries program which inturn will create a table GL_CONS_INTERFACE_<> with auto generated number.

               

               

              The job in question will take that table name from above step and give privilege to apps_query user.

               

               

              As its harded coded there is no chance for that now.

               

               

              May be there was job which was deleted but forgot to delete this job, or someone was testing and forgot, etc...

               

               

              can you just query and see if you have many table with GL_CONS_INTERFACE

               

               

              select * dba_objects from like object_name like 'GL_CONS_INTERFACE_%'

              1 person found this helpful
              • 4. Re: Database Job Fails To Execute PL/SQL Block Because An Object Is Missing
                Mussa

                What you said is a possibility.    I have 51 objects that starts with 'GL_CONS_INTERFACE_%'.

                 

                select count(*) from dba_objects where object_name like 'GL_CONS_INTERFACE_%';

                  COUNT(*)

                ----------

                         51

                 

                 

                 

                However, I have 17 tables that start with 'GL_CONS_INTERFACE_%'

                select count(*) from dba_objects where object_name like 'GL_CONS_INTERFACE_%' and object_type='TABLE';

                  COUNT(*)

                ----------

                        17

                 

                 

                Can I know the thinking process as to why you asked about the number of tables that start with 'GL_CONS_INTERFACE_%' in order to think collaboratively?

                • 5. Re: Database Job Fails To Execute PL/SQL Block Because An Object Is Missing
                  yoonas

                  Just to see the object and its creation date so that we can at least know when was created , these are getting created .

                   

                  Then just to see which other objects are referring these tables.

                  If objects are created on these tables we can check the definition and get some clue

                  1 person found this helpful
                  • 6. Re: Database Job Fails To Execute PL/SQL Block Because An Object Is Missing
                    Mussa

                    Hi

                     

                    I have a list of tables as shown above with creation date (recent one is dated last month).  I tried to find a way to get all objects that effects these tables but I couldn't find any.  Do you know what view is helpful in this situation?   There is dba_dependecies but it doesn't give the desired output when I query as following:

                    select * from DBA_DEPENDENCIES where name='GL_CONS_INTERFACE_1709971'

                     

                     

                    Can you help with providing a view to query from?

                    • 7. Re: Database Job Fails To Execute PL/SQL Block Because An Object Is Missing
                      yoonas

                      I don't have a ready made query with me, I would also do the same thing if i were in your situation look  on the internet and find info and continue.

                       

                       

                      select * from DBA_DEPENDENCIES where name='GL_CONS_INTERFACE

                      I think you will have to use REFERENCED_NAME to find all objects referencing this table instead of name

                       

                      The job was actually granting privilege to apps_query, so you could query dba_tab_privs to see list of tables with GL_CONS_INTERFACE_ prefix and has select privilege for apps_query user

                       

                      select * from dba_tab_privs where upper(table_name) like 'GL_CONS_INTERFACE%' and upper(grantee) = 'APPS_QUERY'

                       

                      Also see what kind of data is available in the table, does it make any sense to have access to these data for any user. May be there are reports running against this table which you can't get from dependency table. I think this can be tracked by querying v$sql table for object with GL_CONS_INTERFACE%