Friday, July 11, 2008

Job Monitor

This is more of a tip, but instead of using the control center manager to watch the state of an executing map or process flow, you can use the job monitor.























Query for the audit Log file

You might be asking yourself “Man I would love to query out the log file instead of opening the file in a directory.” Well, today you get your wish.

After you run a map, get the AUDIT_EXECUTION_ID.(you can get that in the job monitor or the control center manager). Now open SQL Developer and switch to the user schema where your repository is located.


SELECT * FROM WB_RT_AUDIT_FILES WHERE AUDIT_EXECUTION_ID = 2895504

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Jul 10 13:08:27 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: J:\XXXXXXXXXX.ctlCharacter Set WE8MSWIN1252 specified for all input.
Data File: J:\XXXXXXXXXX.txt Bad File: c:\XXXXXXXXXX.bad Discard File: c:\XXXXXXXXXX.dsc (Allow 0 discards)
Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array: 200 rows, maximum of 50000 bytesContinuation: Concatenate every 1 physical recordsPath used: ConventionalSilent options: FEEDBACKTable "TMP_XXXXXXXXXX", loaded from every logical record.Insert option in effect for this table: APPENDTRAILING NULLCOLS option in effect
.
.
.


Cheers

Danny

Monday, July 7, 2008

Oracle IIF

Hello All,

One of the native functions I miss a lot from SQL Server is the IIF. (http://msdn.microsoft.com/en-us/library/27ydhh0d(VS.71).aspx). So I made one for myself for Oracle. Simple but very useful.

Enjoy,
Danny



DECLARE
v_Return VARCHAR2(200);
BEGIN

v_Return := IIF(
1=2,
'YES',
'NO'
);
DBMS_OUTPUT.PUT_LINE('v_Return = ' v_Return);
END;


create or replace
FUNCTION ODS_ETL.IIF
( param1 IN BOOLEAN
, param2 IN VARCHAR2
, param3 IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
IF param1
THEN
RETURN param2;
ELSE
RETURN param3;
END IF;
END IIF;