Wednesday, July 16, 2008
Fraud Analysis?
So we are pondering how to go about fraud analysis real time.
One thing currently holding us back is that all the key fields are encrypted (obviously). How do you go about doing fraud analysis with such seemingly high overhead?
My question to you is, do you know of any fraud related off-the-shelf tools or libraries out there? Preferably written in PL/SQL, but Java or C will do as well.
One thing currently holding us back is that all the key fields are encrypted (obviously). How do you go about doing fraud analysis with such seemingly high overhead?
My question to you is, do you know of any fraud related off-the-shelf tools or libraries out there? Preferably written in PL/SQL, but Java or C will do as well.
Labels: fraud, java, oracle, plsql
¶ 7/16/2008 09:55:00 PM 7 Comments Links to this post
Thursday, May 1, 2008
Validating a Process Part II
Continued from my previous post.
While discussing external tables with my feisty colleague some time back, I explained that I liked using them but I couldn't figure out how to change the file name to match that of what was defined in the table definition.
Colleage to the rescue:
As I mentioned before, I had 4 files types I had to read: 820 and 835, both of the x12 format and two custom file layouts (flat files essentially). Since no one in the group knows Java yet, I wanted to keep the Java portion of the application as small as possible. So with the two custom files, I decided to use external tables. I could then put into practice the above ALTER TABLE statement.
As I looped through the list of files to be processed, I would issue an EXECUTE IMMEDIATE so that I could then SELECT from the table in the next step. It worked like a charm.
As I was doing some testing, I would issue the ROLLBACK statement to clear the tables for the next run. When I verified, there was still data there. WTF?
Oh wait, there's an EXECUTE IMMEDIATE...which runs DDL...which COMMITs...barnacles!
So I couldn't use that new thing I learned, oh well. Fortunately UTL_FILE does have the ability to rename files so I picked a name like 'external_table_file_name.txt' and rename the incoming file to that, then SELECT. Works like a charm.
While discussing external tables with my feisty colleague some time back, I explained that I liked using them but I couldn't figure out how to change the file name to match that of what was defined in the table definition.
Colleage to the rescue:
Cool!
ALTER TABLE table_name LOCATION ( 'new_file_name.csv' );
As I mentioned before, I had 4 files types I had to read: 820 and 835, both of the x12 format and two custom file layouts (flat files essentially). Since no one in the group knows Java yet, I wanted to keep the Java portion of the application as small as possible. So with the two custom files, I decided to use external tables. I could then put into practice the above ALTER TABLE statement.
As I looped through the list of files to be processed, I would issue an EXECUTE IMMEDIATE so that I could then SELECT from the table in the next step. It worked like a charm.
As I was doing some testing, I would issue the ROLLBACK statement to clear the tables for the next run. When I verified, there was still data there. WTF?
Oh wait, there's an EXECUTE IMMEDIATE...which runs DDL...which COMMITs...barnacles!
So I couldn't use that new thing I learned, oh well. Fortunately UTL_FILE does have the ability to rename files so I picked a name like 'external_table_file_name.txt' and rename the incoming file to that, then SELECT. Works like a charm.
Labels: ddl, java, sql, utl_file
¶ 5/01/2008 11:40:00 PM 5 Comments Links to this post
Tuesday, April 29, 2008
Validating a Process
I mentioned sometime back that I would be posting the code. I can't post the entire set of code but I can post the relevant parts.
Inbound Remittance Advice files are loaded into our Operational Data Store (ODS). We have absolutely no control over this, it lies with another group. On occasion, those files are double, or even triple, loaded.
To provide the business (and ourselves) with a way to track when files came in and if the entire processed worked as expected (no lost dollars, no lost record counts).
1. Read the files when they first come in (on disk).
2. Query the appropriate tables at certain intervals to verify it matches the file amounts.
3. Load those results into a table and then fail the normal load process if we detect any incongruities.
1. Create a directory object on the Oracle server.
2. Copy inbound files to that directory.
3. Read files from that directory (Java anyone?)
4. Load files into CLOBs (so that I don't have to spend half my day finding the damn things, simple APEX app and I'm good to go).
5. Parse files to find relevant information (Java)
6. Query tables at various stages, blah blah blah.
Since the UTL_FILE doesn't have a function to read the contents of a directory, Java comes into play. I've done it before and I found the code originally on asktom (where else?).
For those of you too lazy (like me) to click the link, here's the important stuff:
I have to use Reader and CharacterStream, getting well beyond my knowledge of Java.
With the help of a fellow Java developer, I was pointed towards StreamTokenizer which works in a similar fashion to StringTokenizer, or so I thought. Apparently StreamTokenizer is one of the lower level classes...so I had to figure out the ASCII values of the character I wanted to split on (a tilde: ~). I think my Java friend was surprised I figured this out...
After I got that as a String, I could then use the StringTokenizer, which I knew.
Could I have split the CLOB using PL/SQL? Yes. Did I want to? Not really. I was already using Java so why not just use the Tokenizer?
The five classes were jarred and loaded into Oracle via the loadjava command. Wrap it all up in PL/SQL and life is easy!
Problem
Inbound Remittance Advice files are loaded into our Operational Data Store (ODS). We have absolutely no control over this, it lies with another group. On occasion, those files are double, or even triple, loaded.
Goal
To provide the business (and ourselves) with a way to track when files came in and if the entire processed worked as expected (no lost dollars, no lost record counts).
What do we do?
1. Read the files when they first come in (on disk).
2. Query the appropriate tables at certain intervals to verify it matches the file amounts.
3. Load those results into a table and then fail the normal load process if we detect any incongruities.
Solution (proposed)
1. Create a directory object on the Oracle server.
2. Copy inbound files to that directory.
3. Read files from that directory (Java anyone?)
4. Load files into CLOBs (so that I don't have to spend half my day finding the damn things, simple APEX app and I'm good to go).
5. Parse files to find relevant information (Java)
6. Query tables at various stages, blah blah blah.
Solution
Since the UTL_FILE doesn't have a function to read the contents of a directory, Java comes into play. I've done it before and I found the code originally on asktom (where else?).
For those of you too lazy (like me) to click the link, here's the important stuff:
snip...Since I'm using Java, I might as well use the StringTokenizer, makes like so much easier. But wait, since I'm reading it as a CLOB (and not a String), what do I do? I tried clob.toString(). Nope, it's just a pointer to the actual CLOB.
File file = new File( path );
list = file.list();
for ( int i = 0; i < list.length; i++ )
{
File indvidualFile = new File( path + list[i] );
if ( indvidualFile.isFile() )
{
element = list[i];
statement.setString( 1, element );
}
}
snip...
I have to use Reader and CharacterStream, getting well beyond my knowledge of Java.
With the help of a fellow Java developer, I was pointed towards StreamTokenizer which works in a similar fashion to StringTokenizer, or so I thought. Apparently StreamTokenizer is one of the lower level classes...so I had to figure out the ASCII values of the character I wanted to split on (a tilde: ~). I think my Java friend was surprised I figured this out...
Fun.
Reader characterStream = clob.getCharacterStream();
StreamTokenizer stream = new StreamTokenizer( characterStream );
stream.resetSyntax();
stream.wordChars( 32, 125 );
stream.parseNumbers();
After I got that as a String, I could then use the StringTokenizer, which I knew.
Could I have split the CLOB using PL/SQL? Yes. Did I want to? Not really. I was already using Java so why not just use the Tokenizer?
The five classes were jarred and loaded into Oracle via the loadjava command. Wrap it all up in PL/SQL and life is easy!
¶ 4/29/2008 07:51:00 PM 0 Comments Links to this post
PROCEDURE get_directory_contents( i_directory IN VARCHAR2 )
AS
LANGUAGE JAVA NAME 'com.hmocompany.dw.LoadFileNames.getFileNames( java.lang.String )';
Wednesday, February 27, 2008
EDI Fun
or Electronic Data Interchange, just a fancy phrase for sending and receiving files. We (IT) do love to complicate things don't we?
I've put the change data capture stuff on hold as my never-ending project goes into it's 12th week past deadline. It's at nine 9s: 99.9999999. It's finance related stuff and nothing less than 100% is acceptable. I'm tired.
Part of my project was to move from an already built in house table to the raw (files) tables. My feisty colleague took on that fun challenge for 6 months or so. He's heading up a new project though so he's had to pass the baton on to me. I've accepted it...reluctantly. ;)
Anyway, we store these inbound files all over the place (seemingly to me). I started writing a little Java application that would scan a directory, read these x12-820 files and tell me the interchange date, control number, total amount and some other useful information.
I plan on either putting this in the database and wrapping it up in PL/SQL or creating a service (Java Service Wrapper) and pushing this useful data to a table. So if you have to deal with the wonderful x12/820 formats, you may want to check back soon for the code. I can't promise it will be good, but it will work!
I've put the change data capture stuff on hold as my never-ending project goes into it's 12th week past deadline. It's at nine 9s: 99.9999999. It's finance related stuff and nothing less than 100% is acceptable. I'm tired.
Part of my project was to move from an already built in house table to the raw (files) tables. My feisty colleague took on that fun challenge for 6 months or so. He's heading up a new project though so he's had to pass the baton on to me. I've accepted it...reluctantly. ;)
Anyway, we store these inbound files all over the place (seemingly to me). I started writing a little Java application that would scan a directory, read these x12-820 files and tell me the interchange date, control number, total amount and some other useful information.
I plan on either putting this in the database and wrapping it up in PL/SQL or creating a service (Java Service Wrapper) and pushing this useful data to a table. So if you have to deal with the wonderful x12/820 formats, you may want to check back soon for the code. I can't promise it will be good, but it will work!
Labels: edi, java, plsql, tools, utilities, work
¶ 2/27/2008 08:25:00 PM 0 Comments Links to this post
