OTN Discussion Forums : Get errors from OWB tables ...

This question is answered.

Reply to this Thread

Search Forum

Back to Thread List

Replies: 9 - Pages: 1 - Last Post: Jan 8, 2009 5:49 AM Last Post By: Luciene

Luciene

Posts: 167
Registered: 06/05/07

Get errors from OWB tables
Posted: Dec 2, 2008 2:08 PM

Hi,

How can I obtain the errors from the OWB repository tables? For example, which table or view do I have to query using PL/SQL?

If I load a mapping and I get 10 FK's violated, the Control Center shows the message error 10 times. I would like to obtain those message errors from the repository table. And also, I would like to know if OWB stores the record which causes the errors and therefore, weren't processed.

Thanks

Darthvader-647181

Posts: 949
Registered: 07/15/08

Re: Get errors from OWB tables
Posted: Dec 3, 2008 12:55 AM

in response to: Luciene

Helpful

what is your owb version.
how is mapping run, set based or row based.
if it is 10gR2 or higher then you can use shadow tables.

you can view errors in wb_rt_audit/wb_rt_audit_lines/ wb_rt_errors table wihting your owb repository owner schema

Luciene

Posts: 167
Registered: 06/05/07

Re: Get errors from OWB tables
Posted: Dec 4, 2008 9:09 AM

in response to: Darthvader-647181

Hi,

I have verified those tables and it will help me. The only problem is that I need the mapping Id to find its information, and I don't know how to get it.
For example, I can use the function WB_RT_GET_NUMBER_OF_ERRORS(audit_id) inside the mapping, but I don't know the audit_id. Is there any way to get it inside the mapping???

Thanks

Darthvader-647181

Posts: 949
Registered: 07/15/08

Re: Get errors from OWB tables
Posted: Dec 5, 2008 1:19 AM

in response to: Luciene

Helpful

can you use wb_rt_audit_execution table?

Carsten Herbe

Posts: 493
Registered: 07/06/06

Re: Get errors from OWB tables
Posted: Dec 5, 2008 1:33 AM

in response to: Luciene

Correct

Hi,

inside the mappings you may use get_audit_id to get the audit id of the mapping when it is running.
get_audit_id can be used in any expression.

Regards,
Carsten.

birdy

Posts: 33
Registered: 10/09/07

Re: Get errors from OWB tables
Posted: Dec 12, 2008 9:58 AM

in response to: Luciene

Helpful

Hi

try the query

select rte_dest_table,rte_sqlerrm,created_by,last_update_date
from rep_owner.wb_rt_errors order by last_update_date desc

birdy

Luciene

Posts: 167
Registered: 06/05/07

Re: Get errors from OWB tables
Posted: Dec 16, 2008 3:29 AM

in response to: Carsten Herbe

I have used get_audit_id and it really works.

My doubt is if it's possible to use that in a post mapping processing. I have created a procedure that based on the audit_id, get the mapping information from the repository tables (wb_rt_audit tables) and load a log table.The procedure has the audit_id as the input parameter, but I call this procedure in a post mapping processing, and it is not working.
Get_audit_id is avaiable only during the execution of the mapping???

Luciene

Posts: 167
Registered: 06/05/07

Re: Get errors from OWB tables
Posted: Dec 17, 2008 6:31 AM

in response to: Luciene

I could user the get_audit_id only in an expression. In a constant operator, I couldn't use.

dallan

Posts: 1,195
Registered: 01/10/01

Re: Get errors from OWB tables
Posted: Dec 17, 2008 10:33 AM

in response to: Luciene

Helpful

See the post here for some examples;

http://blogs.oracle.com/warehousebuilder/2007/08/control_flow_in_a_map.html

The constant will have its value initialized when the map is deployed so will be ... 0.
Cheers
David

Luciene

Posts: 167
Registered: 06/05/07

Re: Get errors from OWB tables
Posted: Jan 8, 2009 5:49 AM

in response to: dallan

Hi,

The view we can access to find the values which cause the error is the RAB_RT_EXEC_ERROR_SOURCES.
Now, I'm using the query below to detail the error. I have to know the AUDIT_ID (19298 in my case), which I can obtain inside the mapping (using get_audit_id) or in Control Center (control center jobs). Each RUN_ERROR_ID refers to a record which I tried to insert into the table.
I hope it can help you.

SELECT
ALL_RT_AUDIT_MAP_RUNS.EXECUTION_AUDIT_ID AUDIT_ID,
ALL_RT_AUDIT_MAP_RUNS.MAP_RUN_ID,
ALL_RT_AUDIT_MAP_RUN_ERRORS.RUN_ERROR_ID,
ALL_RT_AUDIT_MAP_RUN_ERRORS.RUN_ERROR_MESSAGE,
REPLACE(SUBSTR( ALL_RT_AUDIT_MAP_RUNS.MAP_NAME , 0, 50), '"', '') MAP_NAME,
RAB_RT_EXEC_ERROR_SOURCES.SOURCE_NAME TABELA,
RAB_RT_EXEC_ERROR_SOURCES.SOURCE_COLUMN COLUNA,
RAB_RT_EXEC_ERROR_SOURCES.SOURCE_COLUMN_VALUE VALOR,
ALL_RT_AUDIT_STEP_RUNS.END_TIME END_TIME,
SYSDATE DATA_ATUAL,
ALL_RT_AUDIT_STEP_RUNS.START_TIME START_TIME
FROM
OWB_ADM10G.ALL_RT_AUDIT_MAP_RUNS ALL_RT_AUDIT_MAP_RUNS,
OWB_ADM10G.ALL_RT_AUDIT_STEP_RUNS ALL_RT_AUDIT_STEP_RUNS,
OWB_ADM10G.ALL_RT_AUDIT_STEP_RUN_TARGETS ALL_RT_AUDIT_STEP_RUN_TARGETS,
OWB_ADM10G.ALL_RT_AUDIT_MAP_RUN_ERRORS ALL_RT_AUDIT_MAP_RUN_ERRORS,
OWB_ADM10G.RAB_RT_EXEC_ERROR_SOURCES RAB_RT_EXEC_ERROR_SOURCES
WHERE
( ALL_RT_AUDIT_MAP_RUNS.MAP_RUN_ID = ALL_RT_AUDIT_STEP_RUNS.MAP_RUN_ID ) AND
( ALL_RT_AUDIT_STEP_RUNS.STEP_ID = ALL_RT_AUDIT_STEP_RUN_TARGETS.STEP_ID ) AND
( ALL_RT_AUDIT_MAP_RUN_ERRORS.MAP_RUN_ID = ALL_RT_AUDIT_MAP_RUNS.MAP_RUN_ID) AND
( ALL_RT_AUDIT_MAP_RUN_ERRORS.MAP_STEP > 0 ) AND
( ALL_RT_AUDIT_MAP_RUN_ERRORS.RUN_ERROR_ID = RAB_RT_EXEC_ERROR_SOURCES.RUN_ERROR_ID) AND
( ALL_RT_AUDIT_MAP_RUNS.EXECUTION_AUDIT_ID = 19298 );

Pages: 1

Back to Thread List

Legend
	Guru : 2500 - 1000000 pts
	Expert : 1000 - 2499 pts
	Pro : 500 - 999 pts
	Journeyman : 200 - 499 pts
	Newbie : 0 - 199 pts
	Oracle ACE Director
	Oracle ACE Member
	Oracle Employee ACE
	Helpful Answer (5 pts)
	Correct Answer (10 pts)