11 Replies Latest reply on Apr 6, 2018 12:10 AM by JMC1

How can I get the failed error log for a job and proceed with other failure steps

3529169 Mar 26, 2018 3:57 PM

Hi ,

I want to actually check the error log.Based on error log I want to route the mapping to different steps.

For example if I get table doesn't exist . I want to actually send my DBA saying create certain table and if the log says closed connection . I want to send a note to admin team saying "please restart ODI ".

I want all this to be automated . Can I actually read the log of the files and get the error and automate it . How difficult is it to achieve?

Thanks

1. Re: How can I get the failed error log for a job and proceed with other failure steps

rpc1 Mar 26, 2018 4:35 PM (in response to 3529169)

Where do you want to check logs. In package scope you can check every step with getPrevStep https://docs.oracle.com/middleware/1212/odi/ODIKD/odiref_reference.htm#ODIKD1242
Like Show 0 Likes(0)

Actions
2. Re: How can I get the failed error log for a job and proceed with other failure steps

3529169 Mar 26, 2018 5:24 PM (in response to rpc1)

How Could I use this method in a package ?Is it in a procedure I should be using this ?
Like Show 0 Likes(0)

Actions
3. Re: How can I get the failed error log for a job and proceed with other failure steps

rpc1 Mar 27, 2018 4:05 AM (in response to 3529169)

Yes it can be used in procedure or refresh variables,
You may create some test package and try it.
Like Show 0 Likes(0)

Actions
4. Re: How can I get the failed error log for a job and proceed with other failure steps

3529169 Mar 29, 2018 9:30 PM (in response to rpc1)

I am not getting how to use these in procedure. I can see that getpreviousstep in knowledge module . So how to use it in a procedure.
Like Show 0 Likes(0)

Actions
5. Re: How can I get the failed error log for a job and proceed with other failure steps

Pavan8u Mar 30, 2018 4:01 AM (in response to 3529169)

In a variable you can write as

Select odiref.getprevsteplog(message) from dual.

Please take care of formatting.

Regards,
Pavan
Like Show 0 Likes(0)

Actions
6. Re: How can I get the failed error log for a job and proceed with other failure steps

3529169 Apr 4, 2018 8:53 PM (in response to Pavan8u)

SELECT odiRef.getException getPrevStepLog("MESSAGE") : No previous Step FROM DUAL . This is the query that is getting executed in the log . I did exactly the way you did . But it throws me an error . Caused By: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

I have kept it just after the mapping .
Like Show 0 Likes(0)

Actions
7. Re: How can I get the failed error log for a job and proceed with other failure steps

Pavan8u Apr 5, 2018 3:53 AM (in response to 3529169)

This is the actual query.

SELECT '<%=odiRef.getPrevStepLog("MESSAGE")%>' FROM DUAL (remove quotes if didn't work)

There is nothing like odiRef.getException getPrevStepLog("MESSAGE")

Regards,
Pavan
Like Show 0 Likes(0)

Actions
8. Re: How can I get the failed error log for a job and proceed with other failure steps

3529169 Apr 5, 2018 2:25 PM (in response to Pavan8u)

SELECT '<%=odiRef.getPrevStepLog("MESSAGE")%>' FROM DUAL

When I use the same thing, I get this query executed when I check the log

SELECT odiRef.getException getPrevStepLog("MESSAGE") : No previous Step FROM DUAL

and throws the error .

I am using the same query as you gave .

Thanks
Like Show 0 Likes(0)

Actions
9. Re: How can I get the failed error log for a job and proceed with other failure steps

Adrian_Popescu-Oracle Apr 5, 2018 2:28 PM (in response to 3529169)

Hi,
This is because the procedure in which the query is executed is the first step in the package.
odiRef.getPrevStepLog refers to previous step of the package.
Adrian
Like Show 1 Likes(1)

Actions
10. Re: How can I get the failed error log for a job and proceed with other failure steps

Pavan8u Apr 5, 2018 3:27 PM (in response to 3529169)

I hope you are querying using ODI variable. Drag and drop the odi variable onto package and map other odi objects to this variable using KO.

If any failure occurs in the package then this variable gets executed and error message will be captured.

As Adrian_Popescu-Oracle explained odiref.getPrevStepLog will capture previous step error message.

Regards,
Pavan
Like Show 0 Likes(0)

Actions
11. Re: How can I get the failed error log for a job and proceed with other failure steps

JMC1 Apr 6, 2018 12:10 AM (in response to 3529169)

I've done this multiple times for clients and the best method is to leverage the Load Plan to orchestrate various execution paths based. From a maintenance cost perspective, this is also a good path to take.

Here's what you will need to approach this solution. I am not going to dictate exactly how you solve this.

1) A job control table. A table which gets written to as the ODI job proceeds. This is pretty much a given.
2) Global variables to test the conditions in question using job control table in #1
3) Introduce ODI CASE steps at the junctures in processing you need. They can be nested if needed
4) Create a series of exception steps in the load plan for your various scenarios
4) Based on the given step which is failing, you can select the specific error step to raise

Again, I have done this for a client who had to wrangle data from 14 different source systems - all which had various maintenance schedules to navigate around.

Hope this helps.
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.