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 Apr 13, 2020 11:59 AM

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(*)

----------

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 Apr 13, 2020 5:48 PM (in response to Mussa)

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
Like Show 1 Likes(1)

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

Mussa Apr 14, 2020 7:30 AM (in response to yoonas)

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
Like Show 0 Likes(0)

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

yoonas Apr 14, 2020 8:37 AM (in response to Mussa)

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
Like Show 1 Likes(1)

Actions
4. Re: Database Job Fails To Execute PL/SQL Block Because An Object Is Missing

Mussa Apr 14, 2020 10:44 AM (in response to yoonas)

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?
Like Show 0 Likes(0)

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

yoonas Apr 14, 2020 11:04 AM (in response to Mussa)

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
Like Show 1 Likes(1)

Actions
6. Re: Database Job Fails To Execute PL/SQL Block Because An Object Is Missing

Mussa Apr 15, 2020 11:03 AM (in response to yoonas)

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?
Like Show 0 Likes(0)

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

yoonas Apr 15, 2020 12:49 PM (in response to Mussa)

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%
Like Show 0 Likes(0)

Actions

Go to original post