Under the OWB engine

My, it has been a long time since I have posted shame on me. Today we are going to show you a little under the engine of OWB. More specifically, some queries to extract details of a map from the repository. First, I make no guarantees that the following is 100% correct. At least these queries may help you get a foothold on how OWB is storing maps, projects, and properties.

---================================================================
--- To get the project ID
-------------------------------------------------------------------
select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where S2_1 = 'CMPWBProject'
and S2_3 = 'DSS_' 'FIN'

---RESULT---------------------------------------------------------
8184190
---================================================================


---================================================================
--- To get the install oracle folder in the project
--------------------------------------------------------------------------------

select * --I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
WHERE R_19 = 8184190
and S2_1 = 'CMPWBDataWarehouse'
and S1_8 = 'Production'

---RESULT---------------------------------------------------------
8344258

---NOTE-----------------------------------------------------------
--- Other options
--- CMPWBDataWarehouse
--- CMPProcessInstalledModule process flow modules
--- CMPWBFileInstalledModule flat file folder
---================================================================


---================================================================
--- To get the expected files list
-------------------------------------------------------------------
select S3_4
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258
and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILENAME'

---RESULT---------------------------------------------------------

f_recip_alt_id.dat
f_clm_hdr.dat
f_hdr.dat
f_note.dat
f_pybl.dat
f_pybl_recv.dat
f_recv.dat
---================================================================


---================================================================
--- To get the filename and stored location name
-------------------------------------------------------------------
SELECT a.S3_4,b.S3_4 FROM
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILENAME'
) A
INNER JOIN
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILELOC'
) B
ON A.R_11 = b.R_11

---RESULT---------------------------------------------------------

f_recip_alt_id.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_clm_hdr.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_hdr.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_note.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_pybl.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_pybl_recv.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_recv.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE

---================================================================

---================================================================
--- To get the filename and directory
-------------------------------------------------------------------
SELECT a.S3_4, E.S3_4

FROM
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILENAME'
) A
INNER JOIN
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILELOC'
) B
ON A.R_11 = b.R_11
INNER JOIN
"OWB_REPOSOWNER".CMPFCOCLASSES C
ON C.S2_5 ':' C.S2_3 = B.S3_4
INNER JOIN "OWB_REPOSOWNER"."CMPSCOPRPCLASSES" E
ON E.R_15 = C.I_1

---RESULT---------------------------------------------------------

f_recip_alt_id.dat J:\FIN\
f_clm_hdr.dat J:\FIN\
f_hdr.dat J:\FIN\
f_note.dat J:\FIN\
f_pybl.dat J:\FIN\
f_pybl_recv.dat J:\FIN\
f_recv.dat J:\FIN\
---================================================================


---================================================================
--- To get the database connection details. This is good to verify that the map
--- will run into the correct user schema and the correct oracle instance.
-------------------------------------------------------------------

select A "table_name"
, MAX(DECODE(B,'CMPLocation_Host',C)) as "HOST"
, MAX(DECODE(B,'CMPLocation_ConnectAsUser', C)) as "USER_SCHEMA"
, MAX(DECODE(B,'CMPLocation_ServiceName',C)) as "SERVICE_NAME"
from
(

SELECT a.S3_4 a,E.S4_1 b, E.S3_4 c
FROM
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = 'DATAENTITY.PARENT_OBJECT_NAME'
) A
INNER JOIN
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPING.ENTITY.LOCATION'
) B
ON A.R_11 = b.R_11
INNER JOIN
"OWB_REPOSOWNER".CMPFCOCLASSES C
ON C.S2_5 ':' C.S2_3 = B.S3_4
INNER JOIN "OWB_REPOSOWNER"."CMPSCOPRPCLASSES" E
ON E.R_15 = C.I_1

) g

group by A
order by A

---RESULT---------------------------------------------------------

table_name HOST USER_SCHEMA SERVICE_NAME
-------------- ------- -------------- ----------------
FIN_BEN_ALT_ID raca ODS0_FIN dss.jp.db.com
FIN_CLM_HDR raca ODS1_FIN dss.jp.db.com
FIN_HDR raca ODS1_NET dss.jp.db.com
FIN_NOTE raca ODS1_REC dss.JP.db.COM
FIN_PYBL racd ODS2 dss.jp.db.com
FIN_PYBL_RECV racd ODS2 dss.jp.db.com
FIN_RECV racd ODS2 dss.jp.db.com

---================================================================

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

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;

What is the last day of last month and more

To get the first day of the month last month use this:
select TRUNC(ADD_MONTHS(SYSDATE, - 1), 'MONTH')FROM DUAL

To get the last day of the month, use this:
select LAST_DAY(ADD_MONTHS(SYSDATE, - 1))FROM DUAL

What is the name of the Schedule that is running a Process Flow

One of my main irks with Oracle Warehouse Builder (OWB)

One of my main irks with Oracle Warehouse Builder (OWB) is the lame messages you receive. I mean seriously, they can leave you with a sense of what hell does that mean and frustration to the point of pulling your hair out.

My case in point today was I had a function with a parameter that returns back a number that is to be used in a wait activity (figure 1). Sound simple enough doesn’t? Wrong. So you can see in figure2 the parameter for the function. And in Figure 3 you can see the binding of wait time to this return parameter.

figure 1

figure 2




















figure 3

Validation of the map was ok. I then deployed the map but that failed. The reason?

GEAP_WRK CreateError RPE-02040: Internal error: P_NWAIT_BETWEEN_EXTRACTS cannot be converted to a constant value. Please correct the value. If the problem persists then please contact Oracle Support with the stack trace and details on how to reproduce it.

See what I mean, this helps no one understand what the problem is or how to fix it. Fortunately I have run across this problem before so I pretty much know what to do. In this case you need to create a process flow variable and bind the parameter to this value. Then you need to bind the wait time property to this variable.

Error messages when there are no errors in OWB

It appears that oracle warehouse builder (OWB) will tell you have an error even though there is no error. Case in point, while creating a process flow, I had a Stored Procedure from a package returning back a Boolean value. I use this return value to determine the transition to the next activity as seen in figure 1.
















figure 1



I set the complex condition "PFUNC_RACS_AVAILABLE"."P_BAVAILABLE" for end with success. I then set the end with errors with NOT "PFUNC_RACS_AVAILABLE"."P_BAVAILABLE" . Finally I set no condition for end with warnings. But when I did a validation on the condition, OWB said I had an error in the expression (figure2). The error was PLS-00530: Illegal type used for object.
















figure 2



Now since I have been working in OWB for a while, I have learned oracle not only gives bad error messages, but incorrect ones as well. Therefore I question most messages from Oracle and decided to test the process flow to make sure. In the figure below, the test shows the flow did work correctly. The second test I simply reversed the conditions between success and errors.









figure 3



As another test I checked the results in the control center to see what was returned from the procedure. Sure enough, a Boolean value was returned. I even reversed what was return to make sure I would be getting a false value (see figure 4).











figure 4

q used to define a quote delimiter for PL/SQL

I don't know why I can never remember the 'q' for a quote delimiter, so I am putting it here for myself so I can easily find it.



q'';

DECLARE
s1 VARCHAR2(20);
s2 VARCHAR2(20);
s3 VARCHAR2(20);
BEGIN
s1 := q'[Isn't this cool]';
s2 := q'"Isn't this cool"';
s3 := q'|Isn't this cool|';

dbms_output.put_line(s1);
dbms_output.put_line(s2);
dbms_output.put_line(s3);
END;


source: http://www.psoug.org/reference/string_func.html

EVAL function

In several other languages I have written in, there is a native function that will evaluate an expression. Unfortunately, Oracle does not have this natively. From another web site I found a simple way to achieve this function.


create or replace function eval (expr varchar2)
return varchar2
as

ret varchar2(4000);

begin
execute immediate 'begin :result := ' || expr || '; end;'
using out ret;
return ret;
end;


source: http://www.adp-gmbh.ch/blog/2005/may/5.html

So, you want to create a zip file in oracle?

My current project has me building a generic based extraction process in Oracle using OWB (Oracle Warehouse Builder). One of the issues is when an extract is so large it really needs to be compressed before any FTP activity occurs. The good news is you can. YEAH!!!!
The approach I took was utilizing Java, which can be used in Oracle. Being more of a C# developer, using Java is not that much of a stretch, but the debugging can be a real pain. I used SQL Developer and JDeveloper to create and Debug the code, which really gives poor error messages, if any at all. I am sure there must be a better way, and I will take some time in the future to find out how. Below is the code I used to take a path and filename and create a zip file in the same folder. There are a lot of write ups on this subject, but for me the best was located at http://www.javafaq.nu/java-example-code-224.html

You simple run this in SQLDeveloper to create a Java Class in the user schema. I place comments in the code to tell you what is happening. One of the things to really point out is the issue of parameters with an IN OUT. There is a great write up that helped me at http://www.cs.umbc.edu/help/oracle8/java.815/a64686/03_pub3.htm

create or replace and compile java source named "CreateZipFile"
as
import java.util.zip.ZipOutputStream;
import java.util.zip.ZipEntry;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.*;


public class CreateZipFile
{
public static void DoCreateZipFile( String pPathAndFileName ,String[] pErrorMessage ,int[] pReturn)
{

int ReturnResults = 0;

String oZipFileName ;
byte[] buf = new byte[1024];

FileInputStream FIS = null;
ZipOutputStream ZOS = null;
try
{
//Create the path and file name for the zip file
oZipFileName = pPathAndFileName.substring(0,pPathAndFileName.lastIndexOf(".")) + ".zip";

// build the file objects
File oInFile = new File( pPathAndFileName);
File oOutFile = new File( oZipFileName);

//Create the output zip streams
FileOutputStream FOS = new FileOutputStream(oOutFile);
ZOS = new ZipOutputStream(FOS);

//Set the highest level of compression
ZOS.setLevel(9);

//Create an entry for the single file we are zipping up
ZOS.putNextEntry(new ZipEntry( oInFile.getName()));

//create the infile input stream
FIS = new FileInputStream( oInFile);

//Read in chucks of the input file and insert into the output file
int len;
while((len = FIS.read(buf)) > 0)
{
ZOS.write(buf, 0, len);
}

//close up the single entry
ZOS.closeEntry();

//Close in and out files
FIS.close();
ZOS.close();


}

catch (Exception e)
{
ReturnResults = 1;
pErrorMessage[0] = e.getMessage();
FIS.close();
ZOS.close();
}
finally
{
return ;
}
}
}



Now that you have created and installed the Java code, congrads, you can now zip a file,… almost. First you must create a spec for this method via an Oracle Procedure or Function.


create or replace
PROCEDURE jproc_Zipfile(pPathAndFileName in varchar2, pErrorMessage in out varchar2, pReturn in out NUMBER)
as language
java name 'CreateZipFile.DoCreateZipFile(java.lang.String,java.lang.String[],int[]) ';


Now that you have a spec for this method, congrads, you can now zip a file,… not quite yet. Next you have to give your user permission to interact with the file structure.

BEGIN
DBMS_JAVA.grant_permission('GEAP_PROJECT', 'java.io.FilePermission', 'J:\GEAP_EXTRACTS\', 'read ,write, execute, delete');
END;


Now that you have give your user permission, congrads, you can now zip a file,… finally (make sure about OS file permissions as well).

DECLARE
PPATHANDFILENAME VARCHAR2(200);
PERRORMESSAGE VARCHAR2(200);
PRETURN NUMBER;
BEGIN
PPATHANDFILENAME := 'C:\TEMP\danny2.csv';
PERRORMESSAGE := NULL;
PRETURN := NULL;

JPROC_ZIPFILE(
PPATHANDFILENAME => PPATHANDFILENAME,
PERRORMESSAGE => PERRORMESSAGE,
PRETURN => PRETURN
);
DBMS_OUTPUT.PUT_LINE('PERRORMESSAGE = ' PERRORMESSAGE);
DBMS_OUTPUT.PUT_LINE('PRETURN = ' PRETURN);
END;


There are a couple of caveats to point out at this point. To the best that I can tell, you cannot add a password protection using just the Java library, it just isn’t there. From what I am readying, this has been on the request list now for eight years.
Secondly, when I was creating this, I was in a windows environment. If you happen to test by hard coding the path such as 'C:\TEMP\danny2.csv', remember Java required double slashes, such as 'C:\\TEMP\\danny2.csv'

Hopefully I was able to hit on all the issues I ran across and that this article will help you. It’s Friday, and almost Beer time.

Danny

EXPDP with QUERY

So I played with this for a couple of days before getting it to work for me... When using the QUERY clause in a EXPDP/IMPDP that includes dates you should not wrap them in a TO_DATE. I tried using a condition like below

QUERY=CLM_HDR:"WHERE HDR_PD_DT <= TO_DATE('04302001','MMDDYYYY')"

EXPDP would give me an error stating that a right parenthesis was missing, but it was obviously not. I did finally figure out that it wanted the dates in the default date format. So by changing it to this

QUERY=CLM_HDR:"WHERE HDR_PD_DT <= '04/30/2001'"

It worked.

Here is my entire parameter file used for the export job I was attempting.

DIRECTORY=datapump_dir
DUMPFILE=102000-042001_CLM_ARCHIVE_%U.dmp
LOGFILE=102000-042001_CLM_ARCHIVE.log
CONTENT=ALL
FILESIZE=2G
INCLUDE=TABLE:"IN ('CLM_HDR','CLM_HDR_ADDL_EOB','CLM_HDR_ADJ_VD','CLM_HDR_COND_CD',
'CLM_HDR_DIAG','CLM_HDR_HCFA1500_DENTAL','CLM_HDR_HH_CERT',
'CLM_HDR_ICD','CLM_HDR_OCC_CD','CLM_HDR_OCC_SPN','CLM_HDR_PREV_LOCN','CLM_HDR_PYR',
'CLM_HDR_RA','CLM_HDR_RLTD_HIST','CLM_HDR_UB92','CLM_HDR_VALU_CD','CLM_HDR_XOVR',
'CLM_LI','CLM_LI_AMB','CLM_LI_ATTACH','CLM_LI_BSE_CHNG','CLM_LI_EXC','CLM_LI_TPL_BLNG')"
QUERY=CLM_HDR:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_ADDL_EOB:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_ADJ_VD:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_COND_CD:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_DIAG:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_HCFA1500_DENTAL:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_HH_CERT:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_ICD:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_OCC_CD:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_OCC_SPN:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_PREV_LOCN:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_PYR:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_RA:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_RLTD_HIST:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_UB92:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_VALU_CD:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_HDR_XOVR:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_LI:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_LI_AMB:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_LI_ATTACH:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_LI_BSE_CHNG:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_LI_EXC:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')",
CLM_LI_TPL_BLNG:"WHERE HDR_PD_DT <= '04/30/2001' AND TCN_NUM NOT IN (SELECT TCN_NUM FROM ODS2.LIFETIME_CLMS WHERE HDR_PD_DT <= '04/30/2001')"

Java in Oracle, you need permissions!!!

One of the interesting things when you work with Java in Oracle is permissions, or better the lack of error messages telling you what permissions you need. If you have any IO actions in your Java code, you will need to give the user special permissions to do so. Oracle has one of their libraries dedicated to this need. It is called DBMS_JAVA.

-- To give permissions to execute IO operations
BEGIN
DBMS_JAVA.grant_permission('ODS_ETL', 'java.io.FilePermission', 'C:\TEMP\20070908A\', 'read ,write, execute, delete');
END;

-- To remove permissions to execute IO operations

BEGIN
DBMS_JAVA.revoke_permission('ODS_ETL', 'java.io.FilePermission', 'C:\TEMP\20070908A\', 'read ,write, execute, delete');
END;


--to get a list of your java permissions
SELECT * FROM USER_JAVA_POLICY

Oracle versus SYS_GUID versus OS

GUID's are one of my favorite items. It must be something about a global uniqueness at my fingertips. But while working on the RACs using the OS of LYNX I noticed the GUIDs were not being generated randomly as I have come to expect. Noticed they are just incremented by one.

select SYS_GUID() FROM DUAL

RACS-LYNX 64 bit
----------------------
4c9381b29852307ae040190a82003040
4c9381b29853307ae040190a82003040
4c9381b29854307ae040190a82003040
4c9381b29855307ae040190a82003040
4c9381b29856307ae040190a82003040


Workflow Server-Windows (OWB)
-----------------------------------
ac600a534951477b9264a8bb0b3b09cb
73b347c1ed31402cb75be64506d44327
716533d75c4d433eb6ce7d74ca763754
ded34a7cfab64b2a976ad183d9725be4
fbabac678fa242949889fb9e9b4d24ca



I did run across this article to help explain the issue.
http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html

How to delete records where some duplication occurs

I have a table of information of beneficiaries and I need to have one record per SSN, but there were multiple records per SSN due to typos in name or different addresses. I really didn't care which record I kept, just need one record per SSN. This is how I did it:

First see how many SSNs have more than one record
SELECT DISTINCT
SSN
FROM
(SELECT SSN,COUNT(*) FROM SI08005101 GROUP BY SSN HAVING COUNT(*) > 1)

Then delete the unwanted records
DELETE FROM
table_name A
WHERE
A.rowid >
ANY (SELECT B.rowid
FROM
table_name B
WHERE
A.SSN = B.SSN
)

When was a table created in Oracle

Jez....I thought I would never figure this out, but it is so very simple.

select OBJECT_NAME
,CREATED
from ALL_OBJECTS
where OWNER = 'GEAP_PROJECT'
and OBJECT_TYPE = 'TABLE'
and OBJECT_NAME = 'TBL_PRG_DELIVERY_TYPE'

So I have found myself in a pickle, and you’re going to love this. So I have a function on a server called FuncThatSmells. On this server, I have a database link to RAC1. On RAC1 I have another function called TestThisCrap.



create or replace
FUNCTION TestThisCrap RETURN VARCHAR2 AS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ODS0_FIN.F_NOTE';
RETURN 'here';
EXCEPTION
WHEN OTHERS
THEN
NULL;

RETURN 'there';
END ;


create or replace
FUNCTION FuncThatSmells RETURN VARCHAR2 AS
v_Return VARCHAR2(4000);
BEGIN
v_Return := FUNCTEST@something.COM;
DBMS_OUTPUT.PUT_LINE('PSQLCODE = ' v_Return);
END;



Now you’re going to love this. The issue is when I run this I get a message

ORA-02064: distributed operation not supported



From the forums, http://forums.oracle.com/forums/thread.jspa?messageID=1977703, I get this
.
.
.
ORA-02064: distributed operation not supported
Cause: One of the following unsupported operations was attempted:
1. array execute of a remote update with a subquery that references a dblink, or
2. an update of a long column with bind variable and an update of a second column with a subquery that both references a dblink and a bind variable, or
3. a commit is issued in a coordinated session from an RPC procedure call with OUT parameters or function call.
Action: simplify remote update statement
.
.
.


BAND MY HEAD ON THE KEYBOARD…………

Oracle CR and LF

Oracle uses chr(13) to denote an 0D and chr(10) to denote an 0A. If you need to add or remove these from a column you can use these values in your query. I needed to remove them from a notes field so I used the following

replace(column,chr(10)) - this removed all 0As from the column.

OWB Global Variables

NUMBER_OF_ERRORS
NUMBER
Number of errors reported on completion of activity's execution.

NUMBER_OF_WARNINGS
NUMBER
Number of warnings reported on completion of activity's execution.

RETURN_RESULT
VARCHAR2(64)
Textual representation of result. For example, 'SUCCESS', 'WARNING', 'ERROR'.

RETURN_RESULT_NUMBER
NUMBER
Enumeration of RESULT_RESULT1 = SUCCESS2 = WARNING3 = ERROR.

RETURN_CODE
NUMBER
Integer 0-255, specific to activity, synonymous with an Operating System return code.

PARENT_AUDIT_ID
NUMBER
The audit id of the calling Process Flow.

AUDIT_ID
NUMBER
The audit id of the activity.

source: http://download.oracle.com/docs/cd/B31080_01/doc/owb.102/b28223/concept_processflows.htm

How to kill a run away process flow

To kill a process

Select Wf.Item_Key , Wf.*
From Wf_Item_Activity_Statuses_V Wf
Where Activity_Type_Code = 'PROCESS'
And Activity_Status_Code != 'COMPLETE'


begin
wf_engine.abortProcess('ODS0', 'ETL_ODS0_2007_02_25_2007_08_27_11_33');
end;

EXPDP to multiple files

If you know that your export job will span multiple GBs and you want to split it at a known file size you can do so by using a couple of switches.

%U - used in the file name to assign the file #
FILESIZE - allows you to specify the dumpfile size

Example
expdp brian@msdss directory=datapump_dir dumpfile=filename_U%.dmp logfile=filename.log content=ALL filesize=2G tables=table_name

What is the Least busiest RAC

While working on the latest project, I realized I needed away to know what was the least busiest RAC. Also I needed to exclude certain RACS as well as setting the maximum allowed CPU Utilization. This query is based on a call to the RAC via a database link. We needed to know not only the last stat but also the last five minutes of CPU Utilization.


SELECT tmpA.INST_ID
,tmpA.avgpast5mins
,tmpB.laststat
,tmpC.INSTANCE_NUMBER
,tmpC.INSTANCE_NAME
,tmpC.HOST_NAME
,tmpD.N_MAX_CPU_PER_SERVER
FROM
(select INST_ID ,AVG(VALUE) avgpast5mins
from GV$SYSMETRIC_HISTORY@DLK_RAC
where metric_name='Host CPU Utilization (%)'
AND BEGIN_TIME > sysdate - 5*(1/60)/24
GROUP BY INST_ID ) tmpA
INNER JOIN
(select A.INST_ID
,a.BEGIN_TIME
,VALUE laststat
from GV$SYSMETRIC_HISTORY@DLK_RAC A
INNER JOIN
(select INST_ID
, MAX(BEGIN_TIME) BEGIN_TIME
from GV$SYSMETRIC_HISTORY@DLK_RAC
where metric_name='Host CPU Utilization (%)'
GROUP BY INST_ID) B
ON A.INST_ID = B.INST_ID
and A.BEGIN_TIME = B.BEGIN_TIME
where A.metric_name='Host CPU Utilization (%)' ) tmpB
ON tmpA.INST_ID = tmpB.INST_ID
INNER JOIN GV$INSTANCE@DLK_RAC tmpC
ON tmpA.INST_ID = tmpC.INST_ID
INNER JOIN GEAP_PROJECT.TBL_PRG_CONFIGURATION tmpD
ON 1=1
WHERE tmpC.INSTANCE_NAME NOT IN
(SELECT INSTANCE_NAME
FROM "GEAP_PROJECT"."TBL_EXTRACT_RAC_EXCLUSION")
AND AVGPAST5MINS < N_MAX_CPU_PER_SERVER
AND LASTSTAT < N_MAX_CPU_PER_SERVER
ORDER BY LASTSTAT

OWB FOR_LOOP Activity

I finally figured out the FOR_LOOP activity for OWB. It is not the most documented activity, nor the most intuitive in my opinion.

First, create a process flow. Add in a FOR_LOOP and a WAIT activity as seem below.. Don't forget to set the 2 transitions leaving the FOR_LOOP, one for "Loop" the other as "Exit".

Now add in two variables to the process flow. I called one "INTERATOR" and the other "MAX_INTERATION". See the two following figures to see the settings for these variables.

I set the wait activity to 2 seconds. Now we set the properties of the FOR_LOOP

Hope this helps,

Danny

hierarchical queries

This summary is not available. Please click here to view the post.

V$ and GV$ views and permissions

QUOTED FROM" http://www.sapereonline.com/dictionary.html

"The final set of data dictionary objects are not intended for direct access by most users or commonprograms. Over 1,000 of these views (called fixed views) have names beginning with V$ or GV$. (TheV$ views provide information about the instance you are connected to while the GV$ views providea global view of all instances within a Real Application Clusters database environment.) Monitoringand tuning tools as well as SQL scripts developed by system DBAs will commonly select from theseV$ and GV$ views. Developers without access to these tools may find useful tuning information insome of these views such as V$SQLAREA and V$SYSSTAT. (Access to these views is, by default,restricted so developers will generally have to be granted the SELECT ANY DICTIONARY system privilegeby a DBA before they will be able to view this information.)"


Danny

Time zone and Header in Emails using utl_smtp

One of the problems I have had for what seems like ages is the incorrect time sent while using utl_smtp, or maybe I should say how I was using it. Everytime I would send an Email in OWB or any Stored Procedure, it would show a five hour difference (yes I live in the central time zone here in the USA). Now I knew it was something about the time zone, but for the life of me I just didn’t have the time to figure it out. Below is an example of what my Inbox said I got it versus when it was sent. (figure 1)




What I was saying earlier was how I was using it. When I created the message header, it looked like this.

--======================================
-- Build the envelope
--======================================
mesg:=
'Date: 'TO_CHAR( v_SystemDate, 'dd Mon yy hh24:mi:ss' ) crlf
'From: ' p_From_Alias crlf
'Subject: ' p_Subject crlf 'To: ';


Notice the TO_CHAR( v_SystemDate, 'dd Mon yy hh24:mi:ss' ). In the world of email, this is actually incorrect. It does not take into affects of time zones. If you use the SYSTIMESTAMP with the correct format, then your emails will show correctly. Here is an example of how to format the time

TO_CHAR(SYSTIMESTAMP,'DD Mon YYYY HH24:MI:SS TZHTZM')


Danny

SQL Developer version 1.2.1

I finally see the issue I was having with SQL Developer. When I compiled under the version 1.1.0.23, all I saw were warnings. From this I see the package has been compiled and ready to run.





When I compiled under the latest version 1.2.1, but note the difference in the compiler log at the last line. “Only first 20 issues are reported”




After some research, I found a preference that I need to set.

Now see the difference in the messages and compiler log.

I hope this sheds some light on the version differences and how it can help you.

Danny



SOURCE: http://forums.oracle.com/forums/thread.jspa?threadID=379327

Getting the audit execution id

One of the things I have always wanted was the Audit Execution ID at runtime. To get this value I only had two ways after the process begin: query the audit log or look at the control center (see figure 1). WTF Oracle!

figure1

I finally found a way to get this value. There is a global variable I never knew existed called audit_id. Not only that, there is a public function that will give you the parent audit id as well called WB_RT_GET_PARENT_AUDIT_ID
For an example, I have created a process flow with two output parameters and two assignments activities (see figure2).

figure2

For the first I bind the first output parameter to the first activity and give it a value of audit_id and set the literal to false (see figure3).. For the second assignment activity, I bonded it to the second output parameter with a value of WB_RT_GET_PARENT_AUDIT_ID(audit_id) and set the literal to false.

figure 3

I then deployed the process flow and executed. Once the process flow had completed, I open the control center and looked the properties of the execution (see figure5). When reviewing the output parameters, you can see the audit id for the assignment activity and the parent’s audit id, which was the process flow’s .

Out of my brain into the anals of blog land,

hope you can find use for this brain drain,

now where is my beer.

Danny

Problems with Convert

I have been working to to put the Oracle convert function in the output of my files. I have a couple problems using the OWB. In Warehouse builder, when you try to output to a fixed length file it uses RPAD to make sure the length is as defined in your file. The problem is no matter how you define the file it outputs it as ASCII even though I have it defined as EBCDIC. I have all the fields already padded with EBCDIC spaces but since it is RPAD inside the generated code it addes ASCII spaces because it doesn't translate some of my EBCDIC characters. Another problem I have is the generated code always adds a utls_file.new_line command after each output line. I have found no where in the OWB to stop this finctionality. So again an ASCII LF is added to everyline. I have created a procedure to do what I want it to do but can't get the OWB to replicate. This is the procedure.

CREATE OR REPLACE PROCEDURE "EXTRACTS"."TEST" AS
cursor sll_cur is
SELECT CONVERT(RPAD(BENE_ID,20,' '), 'UTFE') BENE, CONVERT(RPAD(TOTAL_BILLED_AMT,8,' '), 'UTFE') billed from MSIS_claimlt;
sll_rec sll_cur%ROWTYPE;
dat_file utl_file.file_type;
BEGIN
dat_file := utl_file.fopen('JSURS_CLAIMS', 'test_convert.txt', 'w',32767);
utl_file.put(dat_file,'');
utl_file.fclose(dat_file);
OPEN sll_cur;
LOOP
dat_file := utl_file.fopen('JSURS_CLAIMS', 'test_convert.txt', 'A');
FETCH sll_cur INTO sll_rec;
EXIT WHEN sll_cur%NOTFOUND;

utl_file.put(dat_file, sll_rec.BENE || sll_rec.billed);
--utl_file.fflush(dat_file);
utl_file.fclose(dat_file);
END LOOP;
END TEST;

You will notice I have to open the file as write to intialize the file and then open the file each time to output a line. This is because of the potential size of one line where it is not broken by a line feed. It keeps each line in the buffer until a new line is recieved.

Eric

Finally a place to keep this knowledge besides Danny's head

Well we finally created a blog to keep track of all of the tips/tricks that we have found for OWB (since there really isn't much documenation). We will start posting items here for us and others to use. Enjoy.