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
Wednesday, July 9, 2008
Pseudo Column ROWDATE?
I'm working a little bit in the datawarehouse again (by the way, is it two words or one?). A technical guy from Oracle showed up today to help us decide the best way to move forward on capturing changes. There were essentially 4 methods:
1. SQL and PL/SQL
2. Streams (CDC)
3. OWB/ODI
4. Logical Standby/Data Guard
As someone on the phone was talking, I started to wander...hmmm...what about some kind of pseudo column for that stored the last update (either INSERT or UPDATE) of a row?
Off the top of my head, I can think of rowid, rownum, and level. I'm sure there are others (feel free to comment).
Oracle, I'm sure, stores that information some where right?
I hadn't really given a thought as to the feasibility or the impact it might have, but that would make capturing changes a whole lot easier...
1. SQL and PL/SQL
2. Streams (CDC)
3. OWB/ODI
4. Logical Standby/Data Guard
As someone on the phone was talking, I started to wander...hmmm...what about some kind of pseudo column for that stored the last update (either INSERT or UPDATE) of a row?
Off the top of my head, I can think of rowid, rownum, and level. I'm sure there are others (feel free to comment).
Oracle, I'm sure, stores that information some where right?
I hadn't really given a thought as to the feasibility or the impact it might have, but that would make capturing changes a whole lot easier...
Labels: datawarehouse, oracle
¶ 7/09/2008 09:56:00 PM 9 Comments Links to this post
Wednesday, July 2, 2008
Compound Triggers
Seriously, I don't like triggers. But if you have to maintain them, you might as well make the best of it.
While trying to figure out my problem the other day, I ran across Compound Triggers. I hadn't read about it in the 11g New Features guide, but since I don't use triggers, I wasn't sure if it was new or not. Apprently it is...
In essence, you can combine multiple triggers into one. I won't go into the gory details (because I don't know the gory details), but I will provide the example from the docs for your perusal.
While trying to figure out my problem the other day, I ran across Compound Triggers. I hadn't read about it in the 11g New Features guide, but since I don't use triggers, I wasn't sure if it was new or not. Apprently it is...
In essence, you can combine multiple triggers into one. I won't go into the gory details (because I don't know the gory details), but I will provide the example from the docs for your perusal.
At the very minimum (if you have to use them), you might as well combine them into one and save a bit on maintenance/debugging!
CREATE TRIGGER compound_trigger
FOR UPDATE OF sal ON emp
COMPOUND TRIGGER
-- Declaration Section
-- Variables declared here have firing-statement duration.
threshold CONSTANT SIMPLE_INTEGER := 200;
BEFORE STATEMENT IS
BEGIN
...
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
...
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
...
END AFTER EACH ROW;
END compound_trigger;
/
Labels: database, oracle, triggers
¶ 7/02/2008 09:19:00 PM 1 Comments Links to this post
Monday, June 30, 2008
Fun With Triggers
I don't care a whole lot for triggers. About the only thing I see of use is for some sort of auditing.
That said, I don't know a whole lot about them either. I don't know the specifics of how they work. I was tested today...
My requirement is that I capture the update_user, specifically in the case where it is not supplied. And this is where I ran into a wall.
Ultimately, I scrapped the update of UPDATE_USER in the trigger. I did force the UPDATE_DATE = SYSDATE, and that was it.
Ideally, I would specify that in the package call, but we aren't there yet. In my opinion, no other user would have UPDATE, INSERT or DELETE privileges on the table. But that's what I have.
Can someone with more experience with triggers help me out? Or advise me on what's going on?
That said, I don't know a whole lot about them either. I don't know the specifics of how they work. I was tested today...
CREATE TABLE tEasy enough.
(
update_date DATE DEFAULT SYSDATE,
update_user VARCHAR2(30)
);
INSERT INTO t ( update_date )
VALUES ( ADD_MONTHS( SYSDATE, 100 ) );
INSERT INTO t ( update_date )
VALUES ( ADD_MONTHS( SYSDATE, -22 ) );
CJUSTICE@XE>SELECT * FROM T;
UPDATE_DA UPDATE_USER
--------- ------------------------------
31-OCT-16
31-AUG-06
My requirement is that I capture the update_user, specifically in the case where it is not supplied. And this is where I ran into a wall.
CREATE OR REPLACEEasy enough.
TRIGGER biu_t
BEFORE INSERT OR UPDATE
ON t
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :NEW.update_user IS NULL THEN
:NEW.update_user := USER;
END IF;
END;
/
show errors
UPDATE tOK, not too bad. Let's try specifying the UPDATE_USER.
SET update_date = ADD_MONTHS( SYSDATE, -12 );
CJUSTICE@XE>SELECT * FROM T;
UPDATE_DA UPDATE_USER
--------- ------------------------------
30-JUN-07 CJUSTICE
30-JUN-07 CJUSTICE
2 rows selected.
UPDATE tGood, everything's working as expected...or is it? Let's connect as another user:
SET update_date = ADD_MONTHS( SYSDATE, 12 ),
update_user = 'BOLLOCKS';
UPDATE_DA UPDATE_USER
--------- ------------------------------
30-JUN-09 BOLLOCKS
30-JUN-09 BOLLOCKS
CJUSTICE@XE>conn hr/testing@xeWhat? Why didn't the user get updated with HR? Let's add some dbms_output statements to the trigger:
Connected.
UPDATE cjustice.t SET update_date = SYSDATE - 100;
HR@XE>SELECT * FROM cjustice.t;
UPDATE_DA UPDATE_USER
--------- ------------------------------
22-MAR-08 BOLLOCKS
22-MAR-08 BOLLOCKS
CREATE OR REPLACESo UPDATE_USER was not updated with the value of HR, which I would expect. The :NEW and :OLD values are exactly the same...I'm guessing that Oracle makes a copy of the record first (puts it into a collection or something?) so UPDATE_USER would never be NULL.
TRIGGER biu_t
BEFORE INSERT OR UPDATE
ON t
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
dbms_output.put_line( 'New Value: ' || :NEW.update_user );
dbms_output.put_line( 'Old Value: ' || :OLD.update_user );
IF :NEW.update_user IS NULL THEN
:NEW.update_user := USER;
END IF;
END;
/
show errors
HR@XE>UPDATE cjustice.t SET update_date = SYSDATE + 100;
New Value: BOLLOCKS
Old Value: BOLLOCKS
New Value: BOLLOCKS
Old Value: BOLLOCKS
2 rows updated.
Elapsed: 00:00:00.04
HR@XE>SELECT * FROM cjustice.t;
UPDATE_DA UPDATE_USER
--------- ------------------------------
08-OCT-08 BOLLOCKS
08-OCT-08 BOLLOCKS
2 rows selected.
Ultimately, I scrapped the update of UPDATE_USER in the trigger. I did force the UPDATE_DATE = SYSDATE, and that was it.
Ideally, I would specify that in the package call, but we aren't there yet. In my opinion, no other user would have UPDATE, INSERT or DELETE privileges on the table. But that's what I have.
Can someone with more experience with triggers help me out? Or advise me on what's going on?
Labels: oracle, plsql, triggers
¶ 6/30/2008 08:55:00 PM 4 Comments Links to this post
Thursday, June 5, 2008
BULK COLLECT and FORALL
I have a task to tune a little bit of SQL. It's very ETL like, but the target is not (yet) a star schema, it's pretty much a table flattened out for reporting purposes.
As I reviewed it, I noticed it went row by row, with a COMMIT inside the LOOP. That's gotta go.
Can I do this in one SQL statement? No, there's other processing that needs to be done (UPDATE two other tables before and after). Hmmm...could I just return the appropriate records into a collection? I'll have to look at that to see if it's possible.
For now though, I am going to try and use BULK COLLECT with the LIMIT clause and FORALL for the processes that occur before and after.
Let's create some data:
So I can bulk bind, but I have to INSERT into the table as a whole. I can't be selective.
I updated my code to this:
BULK COLLECT and FORALL are great tools if you can't do it in a single SQL statement and if you want to avoid the row by row processing.
As I reviewed it, I noticed it went row by row, with a COMMIT inside the LOOP. That's gotta go.
Can I do this in one SQL statement? No, there's other processing that needs to be done (UPDATE two other tables before and after). Hmmm...could I just return the appropriate records into a collection? I'll have to look at that to see if it's possible.
For now though, I am going to try and use BULK COLLECT with the LIMIT clause and FORALL for the processes that occur before and after.
Let's create some data:
OK, now let's create an anonymous block, BULK COLLECTing the data from T into a PL/SQL table and then populated another table with that data:
CREATE TABLE t( x NUMBER, y NUMBER );
INSERT INTO t( x, y )
SELECT
TRUNC( dbms_random.value( 1, 99999999 ) ),
TRUNC( dbms_random.value( 1, 100000 ) )
FROM dual
CONNECT BY level < 1001;
And then I run it and I get the following:
DECLARE
TYPE t_record IS TABLE OF T%ROWTYPE;
l_table T_RECORD;
CURSOR c
IS
SELECT x, y
FROM t;
BEGIN
OPEN c;
LOOP
FETCH c
BULK COLLECT INTO l_table
LIMIT 100;
FORALL i IN 1..l_table.COUNT
INSERT INTO s ( x, y )
VALUES ( l_table(i).x, l_table(i).y );
EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE c;
END;
/
A quick google search and I end up here .
ERROR at line 18:
ORA-06550: line 18, column 16:
PLS-00436: implementation restriction: cannot reference
fields of BULK In-BIND table of records
ORA-06550: line 18, column 16:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 30:
PLS-00436: implementation restriction: cannot reference
fields of BULK In-BIND table of records
ORA-06550: line 18, column 30:
PLS-00382: expression is of wrong type
ORA-06550: line 18, column 16:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 17, column 7:
PL/SQL: SQL Statement ignored
So I can bulk bind, but I have to INSERT into the table as a whole. I can't be selective.
I updated my code to this:
I run it and it completes successfully. This is all on XE, so I wonder (hope) that 11g will allow me to do what I want (I'll be working on an 11g RAC system). I scroll down the list of google results and I find this one which then takes me to AskTom. The first post demonstrates that my first attempt will work on 11g.
DECLARE
TYPE t_record IS TABLE OF T%ROWTYPE;
l_table T_RECORD;
CURSOR c
IS
SELECT x, y
FROM t;
BEGIN
OPEN c;
LOOP
FETCH c
BULK COLLECT INTO l_table
LIMIT 100;
FORALL i IN 1..l_table.COUNT
INSERT INTO s
VALUES l_table(i);
EXIT WHEN C%NOTFOUND;
END LOOP;
CLOSE c;
END;
/
BULK COLLECT and FORALL are great tools if you can't do it in a single SQL statement and if you want to avoid the row by row processing.
Labels: development, howto, oracle, plsql, sql
¶ 6/05/2008 10:14:00 PM 7 Comments Links to this post
Monday, May 19, 2008
DIMENSION Objects
One of the very first things I did when I got my current job was to go through the Datawarehousing guide in the Oracle documentation.
Analytics, check.
Materialized Views, check.
OLAP, interesting, but not applicable yet.
Dimensions...hmm, dimensions.
I looked at all the objects in the database but couldn't find any of the type DIMENSION.
I searched AskTom, nothing (or at least nothing I could find).
I can't remember finding any articles on the DIMENSION objects either. Can that be right? Does any one our there use them? Mr. Rittman? Mr. Aldridge? Mr. Scott?
From my brief perusal of the documentation, you can set up the DIMENSION like this:
Does anyone out there have any experience using them?
Analytics, check.
Materialized Views, check.
OLAP, interesting, but not applicable yet.
Dimensions...hmm, dimensions.
I looked at all the objects in the database but couldn't find any of the type DIMENSION.
I searched AskTom, nothing (or at least nothing I could find).
I can't remember finding any articles on the DIMENSION objects either. Can that be right? Does any one our there use them? Mr. Rittman? Mr. Aldridge? Mr. Scott?
From my brief perusal of the documentation, you can set up the DIMENSION like this:
My best guess is that it's sort of like the heirarchical table format (FK referencing PK from the same table).
CREATE TABLE test_dim
(
test_key NUMBER(10,0) PRIMARY KEY,
source VARCHAR2(10) NOT NULL,
source_subcategory VARCHAR2(10)
);
INSERT INTO test_dim( test_key, source, source_subcategory )
VALUES ( 1, 'MEDICAID', NULL );
INSERT INTO test_dim( test_key, source, source_subcategory )
VALUES ( 2, 'MEDICARE', NULL );
INSERT INTO test_dim( test_key, source, source_subcategory )
VALUES ( 3, 'SSA', 'MEDICARE' );
INSERT INTO test_dim( test_key, source, source_subcategory )
VALUES ( 4, 'SPAP', 'MEDICARE' );
INSERT INTO test_dim( test_key, source, source_subcategory )
VALUES ( 5, 'MAPD', 'MEDICARE' );
CREATE DIMENSION test_dim
LEVEL source IS ( test_dim.source )
LEVEL source_subcategory IS ( test_dim.source_subcategory );
Does anyone out there have any experience using them?
Labels: datawarehouse, oracle
¶ 5/19/2008 08:10:00 PM 5 Comments Links to this post
Tuesday, May 13, 2008
ApEx: Oracle Marketing WTF?
At the time of writing, the score is 68 have not used ApEx leaving only 8 who have. Obviously this poll isn't scientific, but it does have to represent a small bit of the community. Perhaps Oracle Mix would be a better place to ask the question?
I guess my followup poll would be why? Why haven't you used it?
Did you not know about it?
If that's the case, then it's definitely an Oracle Marketing WTF.
Did you know about it but just never got around to trying it?
I'm completely dumb(quit snickering)-founded.
I believe the very first time I heard anything about the product was when it was referred to as Marvel (Project Marvel?) on AskTom. Am I the only one who read the site for enjoyment (probably, but it was the only thing I could read at work). That must have been 4 or 5 years ago. I started using it in May of 2005, now three years back.
Yes, Oracle has boatloads of products. I still don't know what the whole Fusion thing is (please don't say middleware, I don't know what that is either).
As someone pointed out in the comment section, it's one of the busiest forums on OTN.
I wonder if John and Dmitri run into this? Probably makes cold calling difficult.
As a [ApEx] community, what can we do to help promote it?
I guess my followup poll would be why? Why haven't you used it?
Did you not know about it?
If that's the case, then it's definitely an Oracle Marketing WTF.
Did you know about it but just never got around to trying it?
I'm completely dumb(quit snickering)-founded.
I believe the very first time I heard anything about the product was when it was referred to as Marvel (Project Marvel?) on AskTom. Am I the only one who read the site for enjoyment (probably, but it was the only thing I could read at work). That must have been 4 or 5 years ago. I started using it in May of 2005, now three years back.
Yes, Oracle has boatloads of products. I still don't know what the whole Fusion thing is (please don't say middleware, I don't know what that is either).
As someone pointed out in the comment section, it's one of the busiest forums on OTN.
I wonder if John and Dmitri run into this? Probably makes cold calling difficult.
As a [ApEx] community, what can we do to help promote it?
Labels: apex, database, oracle, wtf
¶ 5/13/2008 10:01:00 PM 4 Comments Links to this post
Monday, May 12, 2008
ApEx: What is it?
Surprisingly it seems, very few Oracle professionals know about ApEx. At WellCare, no one knew about it. Many others I have talked to have no idea what it is.
How can this be?
I'm hard-pressed to believe I am an early adopter. I like to think of myself as such, but perception and reality are two totally different things.
In a nutshell, ApEx (or Application Express, aka HTMLDB) is a rapid application tool that allows you to quickly build web based applications on top of an Oracle database. If I remember correctly, it was initially billed as the Microsoft Access killer.
It is also the front end for Oracle XE.
I've personally built 3 (professional, i.e. paid for) ApEx applications. One for my former day job with 350 pages (1.5 years to build and maintain), one for my fellow baseball alumni at the University of Florida, and one for my now defunct business, CABEZE.
I believe the question I get most often is: "Can you format it?"
Which I take to mean can you make it pretty or design it anyway you want?
The answer to that is a resounding "Yes!"
So, what follows is a list of ApEx applications for you to evaluate on style and design (borrowed from the unofficial ApEx wiki housed at shellprompt):
In summary, you can do with ApEx what you can do with any other web tool/environment. Want AJAX? Done. CSS? Done.
You're only limited by your imagination.
(Polls are fun...I'll stop apologizing for it soon)
How can this be?
I'm hard-pressed to believe I am an early adopter. I like to think of myself as such, but perception and reality are two totally different things.
In a nutshell, ApEx (or Application Express, aka HTMLDB) is a rapid application tool that allows you to quickly build web based applications on top of an Oracle database. If I remember correctly, it was initially billed as the Microsoft Access killer.
It is also the front end for Oracle XE.
I've personally built 3 (professional, i.e. paid for) ApEx applications. One for my former day job with 350 pages (1.5 years to build and maintain), one for my fellow baseball alumni at the University of Florida, and one for my now defunct business, CABEZE.
I believe the question I get most often is: "Can you format it?"
Which I take to mean can you make it pretty or design it anyway you want?
The answer to that is a resounding "Yes!"
So, what follows is a list of ApEx applications for you to evaluate on style and design (borrowed from the unofficial ApEx wiki housed at shellprompt):
- Dance Tunes - arguably the best designed ApEx application out there.
- AskTom - We all know, or should know, this site. 40,000 page hits a day on a 4 CPU box.
- Metalink - In my opinion not the best implementation of ApEx, but it's a busy site.
- Shellprompt - host your applications here for a very reasonable price.
- View other examples here
In summary, you can do with ApEx what you can do with any other web tool/environment. Want AJAX? Done. CSS? Done.
You're only limited by your imagination.
(Polls are fun...I'll stop apologizing for it soon)
Labels: apex, development, oracle
¶ 5/12/2008 07:34:00 PM 12 Comments Links to this post
Thursday, May 8, 2008
SOUG: 2008 Technology Day
The Suncoast Oracle Users Group (SOUG) will be putting on their annual Technology Day on May 13th. If you are in Tampa or the surrounding area, you should go.
I went in 2006 (from Gainesville) and had a great time. Tom Kyte spoke that year. Surprisingly, my favorite speaker was this guy from Sun (I can't remember his name for the life of me). The main thrust of his presentation was the open sourcing of Solaris 10. He said something about DTrace (I think), way over my head. But his enthusiasm was contagious.
I've always wanted to learn a non-Windows OS, but I could never decide what flavor. Just too many choices.
Solaris 10 was free and it ran Oracle. Perfect!
I did recently create a Solaris 10 Virtual Machine, but I still haven't gotten around to attempting to install Oracle on it.
Anyway, it's a good time, especially for those of you starved for "nerd" companionship!
(Yes, another poll, these are pretty cool. Thanks to Mr. Eddie Awad for the polling link. I think he used it a couple of years ago and I've used it every since)
¶ 5/08/2008 08:37:00 PM 2 Comments
Links to this post
I went in 2006 (from Gainesville) and had a great time. Tom Kyte spoke that year. Surprisingly, my favorite speaker was this guy from Sun (I can't remember his name for the life of me). The main thrust of his presentation was the open sourcing of Solaris 10. He said something about DTrace (I think), way over my head. But his enthusiasm was contagious.
I've always wanted to learn a non-Windows OS, but I could never decide what flavor. Just too many choices.
Solaris 10 was free and it ran Oracle. Perfect!
I did recently create a Solaris 10 Virtual Machine, but I still haven't gotten around to attempting to install Oracle on it.
Anyway, it's a good time, especially for those of you starved for "nerd" companionship!
(Yes, another poll, these are pretty cool. Thanks to Mr. Eddie Awad for the polling link. I think he used it a couple of years ago and I've used it every since)
Tuesday, May 6, 2008
Oracle at Home: The Results
Almost 70% of you responded that you do not have Oracle installed at home. That was a bit of a shocker to me.
I figured that it would be the other way around; people who read blogs, Oracle blogs specifically, would be more likely to have Oracle installed at home.
So, why not?
I figured that it would be the other way around; people who read blogs, Oracle blogs specifically, would be more likely to have Oracle installed at home.
So, why not?
Labels: database, home, oracle, work
¶ 5/06/2008 10:39:00 PM 5 Comments Links to this post
Monday, May 5, 2008
Oracle at Home
I participated in an interview today. One of the questions I usually ask is "Do you have Oracle installed at home?"
It's more a question to gauge their nerdiness than anything, see if they're obsessed like me. Does it always mean that they aren't smart or capable? No, not really. That will come out through the other technical questions. For me at least, it does indicate a curiousity about how the software works.
So, do you have Oracle installed at home?
¶ 5/05/2008 11:53:00 PM 9 Comments
Links to this post
It's more a question to gauge their nerdiness than anything, see if they're obsessed like me. Does it always mean that they aren't smart or capable? No, not really. That will come out through the other technical questions. For me at least, it does indicate a curiousity about how the software works.
So, do you have Oracle installed at home?
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 )';
Thursday, April 24, 2008
ApEx Presenting = FUN!
Wow...that's all I can say.
At lunch today, I put together my power point presentation. I meant to do it sooner of course, but it's been another busy week. In fact, I hadn't done a thing to prepare other than walking through it in my head.
I did not want this to be a presentation so much as a demo. Six slides is all they got.
I suddenly got very nervous around 4 today. I just want to get over there and start. I ended up leaving work just before 5.
The meeting was at the PriceWaterhouseCoopers building in Tampa, just across the street from the Tampa Bay Buccaneers headquarters. Very nice building.
I was introduced by the SOUG president promptly at 6:30. Roughly 40 people showed up (filled the room).
I had an hour and a half to complete my presentation...ummm...WHAT? I've got like 10 minutes worth of material! How the hell am I going to manage this??
Anyway, I walked through some of the features of APEX: Load/Unload data, SQL Commands, that kind of stuff, just trying to get to the application builder.
A couple of nights ago I began walking through and building a basic little reporting application tailored to the DBAs. Reports on roles and privileges basically. I never even got to that.
I created a 2 line csv (yes, I should have done it before hand), uploaded it to demonstrate how easy it was and then off the creating a report on that table.
"Can you create a form to update that record?"
Sure, here's how you do it. One minute later the form was done and I had updated the record. True to form...it's just so damn easy.
I answered a few other questions and then I got stuck. A member of the audience started answering the questions that I couldn't. We (the member in the audience) had spoken before the meeting and I believe he's just as passionate about APEX as I am). It worked rather well, so well in fact, that we're going to try and work out a dual presentation at the upcoming Technology Day SOUG puts on.
Someone would ask a question, I would answer them by showing them how to do it. Have I mentioned how much I like APEX? Rocks.
Next thing I know, it's a little past 8 and I can see the President trying to end the meeting. No way dude, too many questions to answer!
I got out of there around 9:30. I answered questions to the best of my ability...if I didn't know, pointed them to resources that could.
Exhilarating!
Can't wait to do it again...but next time I'll practice!
update
BTW, great crowd tonight. I couldn't have asked for a better group. Thanks to everyone for their support, and thanks to Tom and LewisC for asking me to do it.
Did I mention how cool it was? ;)
At lunch today, I put together my power point presentation. I meant to do it sooner of course, but it's been another busy week. In fact, I hadn't done a thing to prepare other than walking through it in my head.
I did not want this to be a presentation so much as a demo. Six slides is all they got.
I suddenly got very nervous around 4 today. I just want to get over there and start. I ended up leaving work just before 5.
The meeting was at the PriceWaterhouseCoopers building in Tampa, just across the street from the Tampa Bay Buccaneers headquarters. Very nice building.
The Presentation
I was introduced by the SOUG president promptly at 6:30. Roughly 40 people showed up (filled the room).
I had an hour and a half to complete my presentation...ummm...WHAT? I've got like 10 minutes worth of material! How the hell am I going to manage this??
Anyway, I walked through some of the features of APEX: Load/Unload data, SQL Commands, that kind of stuff, just trying to get to the application builder.
A couple of nights ago I began walking through and building a basic little reporting application tailored to the DBAs. Reports on roles and privileges basically. I never even got to that.
I created a 2 line csv (yes, I should have done it before hand), uploaded it to demonstrate how easy it was and then off the creating a report on that table.
"Can you create a form to update that record?"
Sure, here's how you do it. One minute later the form was done and I had updated the record. True to form...it's just so damn easy.
I answered a few other questions and then I got stuck. A member of the audience started answering the questions that I couldn't. We (the member in the audience) had spoken before the meeting and I believe he's just as passionate about APEX as I am). It worked rather well, so well in fact, that we're going to try and work out a dual presentation at the upcoming Technology Day SOUG puts on.
Someone would ask a question, I would answer them by showing them how to do it. Have I mentioned how much I like APEX? Rocks.
Next thing I know, it's a little past 8 and I can see the President trying to end the meeting. No way dude, too many questions to answer!
I got out of there around 9:30. I answered questions to the best of my ability...if I didn't know, pointed them to resources that could.
Exhilarating!
Can't wait to do it again...but next time I'll practice!
update
BTW, great crowd tonight. I couldn't have asked for a better group. Thanks to everyone for their support, and thanks to Tom and LewisC for asking me to do it.
Did I mention how cool it was? ;)
Labels: apex, database, oracle, presentation
¶ 4/24/2008 10:49:00 PM 3 Comments Links to this post
Monday, April 21, 2008
ApEx Presentation
I'll be doing my first professional IT presentation this Thursday for the Suncoast Oracle Users Group.
I'm a bit nervous, but excited at the same time. My goal is to make it as interactive as possible and just let it take me where it takes me.
I will have a canned demo, but I would rather it be more fluid. Can't always plan for those things...I could be a total bust!
So, if you're in the neighborhood and you want to check it out (or just laugh and heckle me), come on by. Details can be found here.
I'm a bit nervous, but excited at the same time. My goal is to make it as interactive as possible and just let it take me where it takes me.
I will have a canned demo, but I would rather it be more fluid. Can't always plan for those things...I could be a total bust!
So, if you're in the neighborhood and you want to check it out (or just laugh and heckle me), come on by. Details can be found here.
Labels: apex, oracle, presentation
¶ 4/21/2008 08:54:00 PM 3 Comments Links to this post
Thursday, April 17, 2008
DBMS_CRYPTO: Example
In the comments from my previous post, Tyler and Tom both mentioned the fact that you should never store sensitive data as plain-text in the database. I used a bad example unfortunately, but Tom did mention the DBMS_CRYPTO package which I have used before.
Since I have used it and published a working example to the OTN forums, I figured I'd put it here as well.
The goal is the encrypt a SSN and store the encrypted string (RAW) in the database. In conjunction with VPD, you should be able to easily control who can see the plain-text version of the SSN. To do that you will need to create another function which decrypts the encrypted SSN.
This is a pretty basic example, but it should help to get you started. You can read more about the DBMS_CRYPTO package in the docs.
Since I have used it and published a working example to the OTN forums, I figured I'd put it here as well.
The goal is the encrypt a SSN and store the encrypted string (RAW) in the database. In conjunction with VPD, you should be able to easily control who can see the plain-text version of the SSN. To do that you will need to create another function which decrypts the encrypted SSN.
This is a pretty basic example, but it should help to get you started. You can read more about the DBMS_CRYPTO package in the docs.
I mention it in the comments of the code, but do not forget to wrap your PL/SQL before you load it, otherwise someone will be able to easily see your salt/key.
CREATE OR REPLACE
PACKAGE BODY p_encrypt
AS
--DO NOT FORGET TO WRAP THIS BEFORE LOADING INTO DATABASE
--IF IT IS NOT WRAPPED, THE KEY WILL BE EXPOSED
--THE WRAP UTILITY IS LOCATED IN THE\BIN DIRECTORY (WRAP.EXE)
G_KEY RAW(32) := UTL_I18N.STRING_TO_RAW( 'some_random_string_stuff_goes_here', 'AL32UTF8' );
FUNCTION encrypt_ssn( p_ssn IN VARCHAR2 ) RETURN RAW
IS
l_ssn RAW(32) := UTL_I18N.STRING_TO_RAW( p_ssn, 'AL32UTF8' );
l_encrypted RAW(32);
BEGIN
NULL;
l_encrypted := dbms_crypto.encrypt
( src => l_ssn,
typ => DBMS_CRYPTO.DES_CBC_PKCS5,
key => G_KEY );
RETURN l_encrypted;
END encrypt_ssn;
FUNCTION decrypt_ssn( p_ssn IN RAW ) RETURN VARCHAR2
IS
l_decrypted RAW(32);
BEGIN
l_decrypted := dbms_crypto.decrypt
( src => p_ssn,
typ => DBMS_CRYPTO.DES_CBC_PKCS5,
key => G_KEY );
RETURN UTL_I18N.RAW_TO_CHAR( l_decrypted, 'AL32UTF8' );
END decrypt_ssn;
END p_encrypt;
/
show errors
Labels: dbms_crypto, howto, oracle, plsql, sql
¶ 4/17/2008 12:24:00 AM 0 Comments Links to this post
Thursday, March 27, 2008
How Does Oracle Make Development Easier?
Continuing on the theme of late, what are the basic things that you can do to reduce the amount of code that needs to be written?
In the post linked above, I mentioned Constraints as probably the easiest way to reduce the amount of coding. For example:
FIRST_NAME - is not optional you hence the NOT NULL constraint.
MIDDLE_NAME - is optional (no constraint).
LAST_NAME - is not optional (NOT NULL).
GENDER - is not optional (NOT NULL). Also, you want to exclude everything but 'M' or 'F', thus the CHECK constraint.
SSN - is not optional (NOT NULL). The length of the value must be 9 characters (CHECK). The characters may only be numeric (CHECK). Unfortunately I don't yet know the REGEXP_INSTR function yet to truly demonstrate. Finally, the UNIQUE constraint on SSN since they shouldn't duplicate across people.
This is a simple demonstration of how you can potentially use constraints to reduce the amount of code necessary. Though I would probably check/validate these as well in code because the error that is generated will not be unique so it would difficult to tell. The point is, if you make a mistake in your validation code it will be easily caught by the constraints forcing you to fix it.
This will give you much more reliable data, which as we all know, is the most important thing.
I'd like to do more of the posts pointing out the easiest methods to reduce the amount of code you have to write by using Oracle.
What kind of solutions do you have or do you use?
In the post linked above, I mentioned Constraints as probably the easiest way to reduce the amount of coding. For example:
ID - is just a sequence generated key, no big deal there.
CREATE TABLE t
(
id NUMBER(10)
CONSTRAINT pk_id_t PRIMARY KEY,
first_name VARCHAR2(30)
CONSTRAINT nn_firstname_t NOT NULL,
middle_name VARCHAR2(30),
last_name VARCHAR2(40)
CONSTRAINT nn_lastname_t NOT NULL,
gender VARCHAR2(1)
CONSTRAINT nn_gender_t NOT NULL
CONSTRAINT ck_morf_gender_t CHECK ( gender IN ( 'M', 'F' ) ),
ssn VARCHAR2(9)
CONSTRAINT nn_ssn_t NOT NULL
CONSTRAINT ck_9_ssn_t CHECK ( LENGTH( ssn ) = 9 )
CONSTRAINT ck_numeric_ssn_t CHECK ( REGEXP_INSTR( ssn, ?, ?, ? ) )
CONSTRAINT uq_ssn_t UNIQUE
);
FIRST_NAME - is not optional you hence the NOT NULL constraint.
MIDDLE_NAME - is optional (no constraint).
LAST_NAME - is not optional (NOT NULL).
GENDER - is not optional (NOT NULL). Also, you want to exclude everything but 'M' or 'F', thus the CHECK constraint.
SSN - is not optional (NOT NULL). The length of the value must be 9 characters (CHECK). The characters may only be numeric (CHECK). Unfortunately I don't yet know the REGEXP_INSTR function yet to truly demonstrate. Finally, the UNIQUE constraint on SSN since they shouldn't duplicate across people.
This is a simple demonstration of how you can potentially use constraints to reduce the amount of code necessary. Though I would probably check/validate these as well in code because the error that is generated will not be unique so it would difficult to tell. The point is, if you make a mistake in your validation code it will be easily caught by the constraints forcing you to fix it.
This will give you much more reliable data, which as we all know, is the most important thing.
I'd like to do more of the posts pointing out the easiest methods to reduce the amount of code you have to write by using Oracle.
What kind of solutions do you have or do you use?
Labels: code, constraints, database, oracle, simplicity
¶ 3/27/2008 10:28:00 PM 0 Comments Links to this post
Tuesday, March 25, 2008
Use the [Oracle] Database dammit!
Dom Brooks recently posted an article about the Dea(r)th of the Oracle RDBMS. It seemed to struck a chord.
I've written about MySQL Friday or Application Developers vs. Database Developerswhich were similar in thought; the database is a bucket.
Ultimately, my take is that application developers don't know and don't want to learn how to use a database. PL/SQL specifically, is a platform in and of itself. You can do so much in the database now that you essentially need an application only for display, to determine the row color if you will.
The usual caveat follows:
If you are building applications that are supposed to be database independent, then the logic belongs in the application. The database is a bucket.
If you are building business applications specific to Oracle though, use the damn thing. Application/web developers are then forced to work on the design and user interface, not application/transaction logic.
Easy steps to actually utilize your database:
1. Use as many constraints as humanly possible - This will reduce the amount of code you have to write and you'll have the security of knowing the data will be what you constrain it to be.
2. DEFAULT columns in table definitions - create_date or load_date can be default to SYSDATE and thus left out of any application code. I've gone so far as to use SYS_CONTEXT( 'MY_CONTEXT', 'USERID' ) as the DEFAULT value for the create_user column. That along with a NOT NULL (or CHECK) constraint, makes life that much easier.
3. Did I mention constraints? Primary Key and Foreign Key constraints are very important to maintain data integrity (ensure you have the data you expect). Don't forget to index those foreign keys.
4. Security - VPD (Virtual Private Database) or Fine Grained Access Control. No longer do you need to maintain two separate schemas (or databases), just add a column and only allow those with the value set see that data. If you are using ApEx, this is incredibly easy to do.
5. Security (Roles and Privileges) - No more table based authorization, let the database do it through roles and privileges. GRANT EXECUTE ON my_package TO some_user
That's my short list for today. Like Dom, this makes me angry. If there were some rational logic behind it, great, convince me. I haven't seen it yet though.
I've written about MySQL Friday or Application Developers vs. Database Developerswhich were similar in thought; the database is a bucket.
Ultimately, my take is that application developers don't know and don't want to learn how to use a database. PL/SQL specifically, is a platform in and of itself. You can do so much in the database now that you essentially need an application only for display, to determine the row color if you will.
The usual caveat follows:
If you are building applications that are supposed to be database independent, then the logic belongs in the application. The database is a bucket.
If you are building business applications specific to Oracle though, use the damn thing. Application/web developers are then forced to work on the design and user interface, not application/transaction logic.
Easy steps to actually utilize your database:
1. Use as many constraints as humanly possible - This will reduce the amount of code you have to write and you'll have the security of knowing the data will be what you constrain it to be.
2. DEFAULT columns in table definitions - create_date or load_date can be default to SYSDATE and thus left out of any application code. I've gone so far as to use SYS_CONTEXT( 'MY_CONTEXT', 'USERID' ) as the DEFAULT value for the create_user column. That along with a NOT NULL (or CHECK) constraint, makes life that much easier.
3. Did I mention constraints? Primary Key and Foreign Key constraints are very important to maintain data integrity (ensure you have the data you expect). Don't forget to index those foreign keys.
4. Security - VPD (Virtual Private Database) or Fine Grained Access Control. No longer do you need to maintain two separate schemas (or databases), just add a column and only allow those with the value set see that data. If you are using ApEx, this is incredibly easy to do.
5. Security (Roles and Privileges) - No more table based authorization, let the database do it through roles and privileges. GRANT EXECUTE ON my_package TO some_user
That's my short list for today. Like Dom, this makes me angry. If there were some rational logic behind it, great, convince me. I haven't seen it yet though.
Labels: constraints, database, development, oracle, work
¶ 3/25/2008 03:16:00 PM 2 Comments Links to this post
Wednesday, March 19, 2008
The Return to ApEx
It's been almost a year, but I've finally gotten a chance to dive back into ApEx!
I've been working primarily on our financial reconciliation for our Medicaid business. That's now very stable as we have everything in our fancy new star schema.
One of the support type activities we've been doing for the past 6 months is maintaining their rate tables...manually. They send (and resend) a csv file and we then match and insert those new records into their rate tables. I don't get to recreate the entire thing unfortunately, it's horribly designed, but I do get to do something.
So instead of doing these manually I finally convinced my boss that this could be done relatively easy with ApEx. I've demo'd it for him in the past, so he's aware of it's capabilities (my evangelism of it doesn't hurt though). Of course our VP steps in and says we have to go through the technical review board. Fair enough, I'm all for standards.
Thankfully my manager convinced the architects that we don't have the Java or Ruby resources to do this, plus, it would take weeks!
So, here's to ApEx, and the further infiltration of it at WellCare! ¶ 3/19/2008 12:35:00 AM 2 Comments Links to this post
I've been working primarily on our financial reconciliation for our Medicaid business. That's now very stable as we have everything in our fancy new star schema.
One of the support type activities we've been doing for the past 6 months is maintaining their rate tables...manually. They send (and resend) a csv file and we then match and insert those new records into their rate tables. I don't get to recreate the entire thing unfortunately, it's horribly designed, but I do get to do something.
So instead of doing these manually I finally convinced my boss that this could be done relatively easy with ApEx. I've demo'd it for him in the past, so he's aware of it's capabilities (my evangelism of it doesn't hurt though). Of course our VP steps in and says we have to go through the technical review board. Fair enough, I'm all for standards.
Thankfully my manager convinced the architects that we don't have the Java or Ruby resources to do this, plus, it would take weeks!
So, here's to ApEx, and the further infiltration of it at WellCare! ¶ 3/19/2008 12:35:00 AM 2 Comments Links to this post
Wednesday, February 6, 2008
It's the Data, Stupid!
Search for the phrase on Google and you'll get plenty of results.
After reflecting for a few days on reaction to MySQL, I think I've realized what is at the heart of it all. Data.
Application developers are not stewards of data. They believe that to be the job of the DBA.
Someone recently asked one of our architects what features of MySQL convinced them to choose this as our new database engine. It's open source!
Of course, that makes perfect sense.
Can it connect to Oracle?
I don't know.
Our architects are made up primarily of former application developers, be it web or client server apps. Data was never that important...
They are currently driving our tool set to favor the application developers, which makes perfect sense to them. It's all about the interface.
But it's not. In the health-care industry, data is king. For any industry really.
I've been trying to convince everyone that this million dollar piece of software called Oracle is not just a bucket, it's feature rich. Streams, Queueing, all kinds of really cool tools. According to our DBAs, none of that stuff is used.
No wonder we're moving to MySQL.
So my quest is to convince the powers that be is to stop wasting money on our million dollar buckets and use them to their full capabilities.
If you have any information to help in this fight, links, slideshows, whatever, please send them on to me (myfirstname.mylastname@gmail.com), please!
Help me turn the tide back to Oracle, back to the data!
After reflecting for a few days on reaction to MySQL, I think I've realized what is at the heart of it all. Data.
Application developers are not stewards of data. They believe that to be the job of the DBA.
Someone recently asked one of our architects what features of MySQL convinced them to choose this as our new database engine. It's open source!
Of course, that makes perfect sense.
Can it connect to Oracle?
I don't know.
Our architects are made up primarily of former application developers, be it web or client server apps. Data was never that important...
They are currently driving our tool set to favor the application developers, which makes perfect sense to them. It's all about the interface.
But it's not. In the health-care industry, data is king. For any industry really.
I've been trying to convince everyone that this million dollar piece of software called Oracle is not just a bucket, it's feature rich. Streams, Queueing, all kinds of really cool tools. According to our DBAs, none of that stuff is used.
No wonder we're moving to MySQL.
So my quest is to convince the powers that be is to stop wasting money on our million dollar buckets and use them to their full capabilities.
If you have any information to help in this fight, links, slideshows, whatever, please send them on to me (myfirstname.mylastname@gmail.com), please!
Help me turn the tide back to Oracle, back to the data!
Labels: database, mysql, open source, oracle
¶ 2/06/2008 10:30:00 PM 1 Comments Links to this post
Monday, February 4, 2008
Is It Arrogance?
I wrote on Friday night about my experiences that day.
I am a very opinionated person. I believe, whole-heartedly, that the database is severely under-utilized, especially at my current employer.
I believe that one of the big draws of MySQL is that it's easy for web/application people to pick up. I also believe, in our situation, that's it's a way for application developers to skirt the whole "data" problem. They'll just pawn it off on the Production DBAs to keep the database running.
Amusingly, some of our application developers brought down one of our Oracle instances, more than once. Pretty tough thing to do I always thought.
I've read articles on bind variables since the beginning, but since it had been drilled into me, I found it quaint. Who would do that?
From a C# app someone passed in hundreds of thousands of un-bound INSERT statements. It flooded the shared pool (is that right?) and brought it to a screeching halt.
Anyway, back to the point.
I've been very vocal lately about MySQL. A few of my friends have begun to warn me that I may be crossing the line towards arrogance. That I will come off as someone resistant to change.
I don't see it. But sometimes we're the last to see our own reflection.
I don't believe that I am resistant to change. I like change. I just want it to be proven, that's all. I embraced ApEx because it made my life easier. That's all I want.
Does this make me arrogant?
I am a very opinionated person. I believe, whole-heartedly, that the database is severely under-utilized, especially at my current employer.
I believe that one of the big draws of MySQL is that it's easy for web/application people to pick up. I also believe, in our situation, that's it's a way for application developers to skirt the whole "data" problem. They'll just pawn it off on the Production DBAs to keep the database running.
Amusingly, some of our application developers brought down one of our Oracle instances, more than once. Pretty tough thing to do I always thought.
I've read articles on bind variables since the beginning, but since it had been drilled into me, I found it quaint. Who would do that?
From a C# app someone passed in hundreds of thousands of un-bound INSERT statements. It flooded the shared pool (is that right?) and brought it to a screeching halt.
Anyway, back to the point.
I've been very vocal lately about MySQL. A few of my friends have begun to warn me that I may be crossing the line towards arrogance. That I will come off as someone resistant to change.
I don't see it. But sometimes we're the last to see our own reflection.
I don't believe that I am resistant to change. I like change. I just want it to be proven, that's all. I embraced ApEx because it made my life easier. That's all I want.
Does this make me arrogant?
Labels: database, mysql, oracle, sql, work
¶ 2/04/2008 10:57:00 PM 3 Comments Links to this post
Friday, February 1, 2008
MySQL Friday
Each month we have an IT All-Hands meeting.
Last month I was promoted to Senior Vice President (SVP), because of my superior management techniques.
Today I was promoted to CEO! Unfortunately it only lasted for a few minutes. I happen to resemble our new CEO (and I'm always pining for a promotion) and they thought it would be funny (again) to bring me up.
I hugged the guy behind me, shook hands with people next to me and ran up to the front. I wanted to shriek, like the people do on The Price is Right, but I didn't have it in me. You gotta have fun at work right?
Well, after that it got serious. Our new Director (at WellCare, Directors are executives, one step up from managers and one below VPs) who heads our architecture team (and release management) got up to discuss where he would be taking us.
Slide one:
From 3 database engines to 1.
From 4 programming languages to 2.
From 3 OSs to 1.
Wanna guess what question I had?
"So, what database engine are we going to use?"
I knew the answer, but I take every single opportunity I get to make my point.
"MySQL."
Being on the datawarehouse team, I was confident that Oracle was not going away.
He went on to explain:
"Legacy applications would be maintained but everything going forward would be done in MySQL."
A flurry of questions came from the crowd so I was unable to followup immediately. I could feel the room come alive...it was weird (I think I'm still hopped up from the events that took place today).
Our CIO asked if there were any more questions or comments.
I spoke up.
I have two points.
1. If it's about cost, move all of the one-off applications into just a few Oracle instances. From what I can tell, we have somewhere in the neighborhood of 100. Let's say 5 databases, datawarehouse, our production OLTP and one for others. All you need to do is assign them different schemas, voila! Cost is much lower and there is a very big chance to reuse code.
2. Actually, I can't remember what my other point was. I think it had something to do with putting the logic in the database, that Java was the fad a few years ago, Ruby was the big thing now, what would it be in 5 years? Will we have to rewrite all of the logic then? (I guess I do sorta remember).
After that, someone asked about the two programming languages. Not a great answer from the crowd's reaction. Then someone asked about the OS.
The crowd was riotous (if that's a word). The CIO had to calm us all down.
I made a remark that he hadn't danced yet (one of our former hazing techniques for new employees) because I didn't want it to be completely personal, or just to ease something that I started.
After the meeting, I spoke with the Director. Oracle will be gone in 20 years because of the open source databases, it's being commoditized (not sure what that means). SOA is the wave of the future.
It was a polite conversation. I told him I look forward to learning from him but that I will probably never be sold on that idea. Fewer moving parts, simplicity, that's what I want.
I then spoke with the CIO, told him that once the decision was made, I would support it and keep my mouth shut (or find a new job).
I sent an email to the VP of the Director's group (after a couple of beers...idiot!) explaining my rationale.
One of the biggest reasons we chose to come to Tampa, to WellCare specifically, was because it was so young and immature. I would have the opportunity, if I could prove myself, to shape the future of IT here.
It's nice to have a voice.
Anyway, it's Friday, I'm prepped to spend all weekend at work to get this project delivered that was due in November. Have a good weekend!
Last month I was promoted to Senior Vice President (SVP), because of my superior management techniques.
Today I was promoted to CEO! Unfortunately it only lasted for a few minutes. I happen to resemble our new CEO (and I'm always pining for a promotion) and they thought it would be funny (again) to bring me up.
I hugged the guy behind me, shook hands with people next to me and ran up to the front. I wanted to shriek, like the people do on The Price is Right, but I didn't have it in me. You gotta have fun at work right?
Well, after that it got serious. Our new Director (at WellCare, Directors are executives, one step up from managers and one below VPs) who heads our architecture team (and release management) got up to discuss where he would be taking us.
Slide one:
From 3 database engines to 1.
From 4 programming languages to 2.
From 3 OSs to 1.
Wanna guess what question I had?
"So, what database engine are we going to use?"
I knew the answer, but I take every single opportunity I get to make my point.
"MySQL."
Being on the datawarehouse team, I was confident that Oracle was not going away.
He went on to explain:
"Legacy applications would be maintained but everything going forward would be done in MySQL."
A flurry of questions came from the crowd so I was unable to followup immediately. I could feel the room come alive...it was weird (I think I'm still hopped up from the events that took place today).
Our CIO asked if there were any more questions or comments.
I spoke up.
I have two points.
1. If it's about cost, move all of the one-off applications into just a few Oracle instances. From what I can tell, we have somewhere in the neighborhood of 100. Let's say 5 databases, datawarehouse, our production OLTP and one for others. All you need to do is assign them different schemas, voila! Cost is much lower and there is a very big chance to reuse code.
2. Actually, I can't remember what my other point was. I think it had something to do with putting the logic in the database, that Java was the fad a few years ago, Ruby was the big thing now, what would it be in 5 years? Will we have to rewrite all of the logic then? (I guess I do sorta remember).
After that, someone asked about the two programming languages. Not a great answer from the crowd's reaction. Then someone asked about the OS.
The crowd was riotous (if that's a word). The CIO had to calm us all down.
I made a remark that he hadn't danced yet (one of our former hazing techniques for new employees) because I didn't want it to be completely personal, or just to ease something that I started.
After the meeting, I spoke with the Director. Oracle will be gone in 20 years because of the open source databases, it's being commoditized (not sure what that means). SOA is the wave of the future.
It was a polite conversation. I told him I look forward to learning from him but that I will probably never be sold on that idea. Fewer moving parts, simplicity, that's what I want.
I then spoke with the CIO, told him that once the decision was made, I would support it and keep my mouth shut (or find a new job).
I sent an email to the VP of the Director's group (after a couple of beers...idiot!) explaining my rationale.
One of the biggest reasons we chose to come to Tampa, to WellCare specifically, was because it was so young and immature. I would have the opportunity, if I could prove myself, to shape the future of IT here.
It's nice to have a voice.
Anyway, it's Friday, I'm prepped to spend all weekend at work to get this project delivered that was due in November. Have a good weekend!
Labels: cio, database, funny, mysql, oracle, sql, work
¶ 2/01/2008 10:52:00 PM 2 Comments Links to this post
Sunday, January 27, 2008
Open Source Obsession?
Since our new CIO came on board last January, there seems to a big movement towards Open Source tools.
While I have nothing against them, I've used various tools in the past; I just don't see what our obsession is with them.
Let's start with Ruby on Rails. An open source framework built on top of Ruby. It's used for web development. It's supposed to be a much more user intuitive language which makes it so easy. Fair enough. I'm always for something that will make my job easier. Our corporate website and provider portals were previously created on the dot net framework.
It was decided last year to replace our entire web infrastructure with Ruby on Rails. I'm still trying to figure out why. It's not that I am a pro-Microsoft guy, but the site worked. There were complaints about it missing this or that functionality, but that's fairly easy to remedy. As far as I know, dot net can support AJAX functionality (which I believe was at the heart of everything).
The demos of the new site were very cool (apparently we paid someone a lot of money to design the site...a LOT of money). It looked all web 2.0ey, big buttons, small text (which is a suprise given we are an HMO managing Medicare). It looked a lot like the 37signals applications in fact. I guess I've read Tom Kyte for far too long and his rant about putting business logic in the database because the front end will always change. Seems true. Java or dot net were all the rage a few years back, now it's Ruby, in a couple of years it will be something else (ApEx!) at which point we will have to rewrite the whole thing.
Next up, MySQL. It's a given that this database has come a long way. Version 5 even has stored procedures. It's a great free database that supports many websites out there. Free is good. Well, mostly free anyway, we do pay support costs right now. Almost every new project is built on top of MySQL. Why? I'm not sure other than free.
I asked the question to the CIO in one of our All-Hands meetings and his response was cost.
Fair enough, Oracle is expensive. The way we use Oracle is even more so. Each project seems to get it's own database. Why they don't get a schema on an existing database I don't quite understand. I've been told that it is a logistical nightmare to pull down a database that affects so many different applications. Each group wants their own version, etc., etc.
I can understand the Data Warehouse having their own database and perhaps our main appplication having it's own database; but every single application? Why not a one off database that houses all the smaller applications in different schemas? That's what Oracle was built for? Plus you can reuse code, reduce the number of instances (thus reducing the cost)...I just don't understand.
I believe my main complaint is that they are still just treating the database (whether Oracle or MySQL) like a bucket. Web people should not be writing SQL; Ruby people should not be writing SQL; just like I shouldn't be writing Ruby code. I don't know it.
I would be willing to bet that I could re-create many of our smaller applications in a much shorter period of time in Oracle and ApEx given the same requirements.
So I rant on. If I truly thought that this was an effort to make IT cheaper and more sustainable, I would be on board. I just don't see that that is the case...
While I have nothing against them, I've used various tools in the past; I just don't see what our obsession is with them.
Let's start with Ruby on Rails. An open source framework built on top of Ruby. It's used for web development. It's supposed to be a much more user intuitive language which makes it so easy. Fair enough. I'm always for something that will make my job easier. Our corporate website and provider portals were previously created on the dot net framework.
It was decided last year to replace our entire web infrastructure with Ruby on Rails. I'm still trying to figure out why. It's not that I am a pro-Microsoft guy, but the site worked. There were complaints about it missing this or that functionality, but that's fairly easy to remedy. As far as I know, dot net can support AJAX functionality (which I believe was at the heart of everything).
The demos of the new site were very cool (apparently we paid someone a lot of money to design the site...a LOT of money). It looked all web 2.0ey, big buttons, small text (which is a suprise given we are an HMO managing Medicare). It looked a lot like the 37signals applications in fact. I guess I've read Tom Kyte for far too long and his rant about putting business logic in the database because the front end will always change. Seems true. Java or dot net were all the rage a few years back, now it's Ruby, in a couple of years it will be something else (ApEx!) at which point we will have to rewrite the whole thing.
Next up, MySQL. It's a given that this database has come a long way. Version 5 even has stored procedures. It's a great free database that supports many websites out there. Free is good. Well, mostly free anyway, we do pay support costs right now. Almost every new project is built on top of MySQL. Why? I'm not sure other than free.
I asked the question to the CIO in one of our All-Hands meetings and his response was cost.
Fair enough, Oracle is expensive. The way we use Oracle is even more so. Each project seems to get it's own database. Why they don't get a schema on an existing database I don't quite understand. I've been told that it is a logistical nightmare to pull down a database that affects so many different applications. Each group wants their own version, etc., etc.
I can understand the Data Warehouse having their own database and perhaps our main appplication having it's own database; but every single application? Why not a one off database that houses all the smaller applications in different schemas? That's what Oracle was built for? Plus you can reuse code, reduce the number of instances (thus reducing the cost)...I just don't understand.
I believe my main complaint is that they are still just treating the database (whether Oracle or MySQL) like a bucket. Web people should not be writing SQL; Ruby people should not be writing SQL; just like I shouldn't be writing Ruby code. I don't know it.
I would be willing to bet that I could re-create many of our smaller applications in a much shorter period of time in Oracle and ApEx given the same requirements.
So I rant on. If I truly thought that this was an effort to make IT cheaper and more sustainable, I would be on board. I just don't see that that is the case...
Labels: database, mysql, open source, oracle, work
¶ 1/27/2008 09:53:00 PM 6 Comments Links to this post
Tuesday, January 15, 2008
Asynchronous Distributed HotLog - CDC Part IV
Part I, II and III.
For the time being I am throwing in the towel. I managed to patch my 10.2.0.1 version up to 10.2.0.3 thinking that might help, but it did not.
I'm still leaning to something on the 9.2.0.6 side, the only reason being is that in the trace file generated by the 10g instance, I see a "Alter9iSource" line in there...but there is nothing in the alert log on the 9i instance.
So I'm packing it up for a little while, hopefully the time away will do me some good and help me regain focus.
I did find a great example of the manual setup by Lewis Cunningham; that may be the route in the future, just a bit more work to maintain.
For the time being I am throwing in the towel. I managed to patch my 10.2.0.1 version up to 10.2.0.3 thinking that might help, but it did not.
I'm still leaning to something on the 9.2.0.6 side, the only reason being is that in the trace file generated by the 10g instance, I see a "Alter9iSource" line in there...but there is nothing in the alert log on the 9i instance.
So I'm packing it up for a little while, hopefully the time away will do me some good and help me regain focus.
I did find a great example of the manual setup by Lewis Cunningham; that may be the route in the future, just a bit more work to maintain.
Labels: cdc, database, datawarehouse, dbms_cdc_publish, oracle
¶ 1/15/2008 12:20:00 AM 0 Comments Links to this post
Thursday, January 3, 2008
Asynchronous Distributed HotLog - CDC Part II
In my first installment, I talked about my task to configure CDC.
I've set up both the source and target databases as detailed in the documents.
I create a simple table:
From the target database I issue the following:
and shortly thereafter receive the following error:
So I set up tracing and I find the following in the trace file:
The way I read that is "###" is being passed to dbms_output.get_lines where it should be expecting a number.
Again, the source database is 9.2.0.7 and the target is 10.2.0.1. I uninstalled the JVM because I thought that the wrong version of java was used to load it (not that I can find any reference to it being used). Changed my path, set up an ORACLE_HOME environment variable (yes, windows XP).
I'm hoping I just mangled the configuration of my local database. Tomorrow one of the DBAs is going to walk me through this in either our DEV or TEST environments (as it should be).
Fun... ¶ 1/03/2008 12:39:00 AM 0 Comments Links to this post
I've set up both the source and target databases as detailed in the documents.
I create a simple table:
CREATE TABLE t ( x NUMBER, y DATE );
From the target database I issue the following:
BEGIN
dbms_cdc_publish.create_hotlog_change_source
( change_source_name => 'T_CHANGE',
description => 'Test',
source_database => 'CDC_APP' );
END;
/
and shortly thereafter receive the following error:
BEGIN
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "SYS.DBMS_CDC_IPUBLISH", line 133
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 226
ORA-06512: at line 2
Elapsed: 00:00:57.23
So I set up tracing and I find the following in the trace file:
PARSING IN CURSOR #13 len=52 dep=0 uid=12 oct=47 lid=12 tim=342736069442 hv=1029988163 ad='3414b300'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #13:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=342736069435
BINDS #13:
kkscoacd
Bind#0
oacdty=123 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000000 frm=00 csi=00 siz=4000 off=0
kxsbbbfp=08460bd8 bln=4000 avl=00 flg=15
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=0844cf58 bln=22 avl=22 flg=05
value=###
An invalid number has been seen.Memory contents are :
The way I read that is "###" is being passed to dbms_output.get_lines where it should be expecting a number.
Again, the source database is 9.2.0.7 and the target is 10.2.0.1. I uninstalled the JVM because I thought that the wrong version of java was used to load it (not that I can find any reference to it being used). Changed my path, set up an ORACLE_HOME environment variable (yes, windows XP).
I'm hoping I just mangled the configuration of my local database. Tomorrow one of the DBAs is going to walk me through this in either our DEV or TEST environments (as it should be).
Fun... ¶ 1/03/2008 12:39:00 AM 0 Comments Links to this post
Thursday, December 27, 2007
Asynchronous Distributed HotLog - Change Data Capture
I am currently researching the feasibility of getting Asynchronous Distributed HotLog CDC working for a Proof of Concept in our organization. The source database is a 9.2.0.7 on Sun Solaris and our target database is a 10gR2 on the same OS.
I have been trying, since I arrived, to get them to use more features provided by Oracle. From what I have found so far, all of our Oracle databases are severely under utilized.
I will post my findings here along with a full How To when I have completed it and hopefully the results of whether it was implemented or not.
I did want to post some of the sources I have used so far though:
Oracle Documentation - This is where I started. Most of the other links reference the docs.
Mark Rittman - 10g to 10g. Great example but from what I can tell so far, it's almost identical to the documentation.
Oracle How To - I just found this one and am very interested in reading through it.
If you have any examples, feel free to post the link.
I have been trying, since I arrived, to get them to use more features provided by Oracle. From what I have found so far, all of our Oracle databases are severely under utilized.
I will post my findings here along with a full How To when I have completed it and hopefully the results of whether it was implemented or not.
I did want to post some of the sources I have used so far though:
Oracle Documentation - This is where I started. Most of the other links reference the docs.
Mark Rittman - 10g to 10g. Great example but from what I can tell so far, it's almost identical to the documentation.
Oracle How To - I just found this one and am very interested in reading through it.
If you have any examples, feel free to post the link.
Labels: cdc, database, development, oracle
¶ 12/27/2007 01:01:00 AM 0 Comments Links to this post
Monday, November 5, 2007
I Want to Be Better Than Tom Kyte
OK, that got your attention. Somehow I knew it would.
I believe Mr. Kyte to be one of the foremost experts at Oracle development. His solutions are usually simple and concise. His philosophy is simple and concise; logic belongs with the data (in the database), don't reinvent the wheel if we've already created it (using supplied packages) and keep it simple. Of course Mr. Kyte may have objections to some of that, but that's the general idea I have gleaned over that past 5 years.
2. I grew up playing baseball, I liked being better than most of the other kids. I still believe that if I hadn't drank away my opportunity in college, I'd still be playing.
3. I'm an only child, I'm used to the attention and crave it. How do I get it now? By being better than everyone else.
Perhaps I should start small...be the best Oracle developer at WellCare, then Tampa, Florida, the U.S, North America, Northern Hemisphere and finally the World!
I am probably not the best Oracle developer at WellCare, so I have a ways to go. That's what drives me though. Trying to be the best. I'm surrounded by a lot of smart people which is a good thing. No...a great thing. I've been the lone wolf developer for too long. Now I have the opportunity to learn directly (as opposed to just reading) from others. There is give and take. Sometimes my solution is the best and sometimes it is not.
I don't believe my competitive nature interferes with my interpersonal relationships (I hope not anyway). It is more of an internal thing to me. Once upon a time I was skinny and in shape and I did triathlons. I wanted to be a pro (laughable). Each time though I tried to outdo my previous performance. Did I want to win? Sure I did, but it was more important for me to improve.
I believe that I am strong enough to take criticism from others. I can admit when I'm wrong (see countdown timer above).
I do want to be the best. I'll probably never have the opportunity (nor the time) to do what Mr. Kyte has done. I'm not going to strive to be mediocre though. Whether I realize that goal or not is mainly irrelevant, but that is my goal...to be better than Mr. Kyte.
I believe Mr. Kyte to be one of the foremost experts at Oracle development. His solutions are usually simple and concise. His philosophy is simple and concise; logic belongs with the data (in the database), don't reinvent the wheel if we've already created it (using supplied packages) and keep it simple. Of course Mr. Kyte may have objections to some of that, but that's the general idea I have gleaned over that past 5 years.
I am very competitive
1. One of the reasons I got into IT in the first place was that I didn't like this whole group of people knowing more than I did.2. I grew up playing baseball, I liked being better than most of the other kids. I still believe that if I hadn't drank away my opportunity in college, I'd still be playing.
3. I'm an only child, I'm used to the attention and crave it. How do I get it now? By being better than everyone else.
My goal is to be the best developer in the world
Will I ever achieve that? Could it even be measured? Is there some sort of Oracle developer competition out there?Perhaps I should start small...be the best Oracle developer at WellCare, then Tampa, Florida, the U.S, North America, Northern Hemisphere and finally the World!
I am probably not the best Oracle developer at WellCare, so I have a ways to go. That's what drives me though. Trying to be the best. I'm surrounded by a lot of smart people which is a good thing. No...a great thing. I've been the lone wolf developer for too long. Now I have the opportunity to learn directly (as opposed to just reading) from others. There is give and take. Sometimes my solution is the best and sometimes it is not.
I don't believe my competitive nature interferes with my interpersonal relationships (I hope not anyway). It is more of an internal thing to me. Once upon a time I was skinny and in shape and I did triathlons. I wanted to be a pro (laughable). Each time though I tried to outdo my previous performance. Did I want to win? Sure I did, but it was more important for me to improve.
I believe that I am strong enough to take criticism from others. I can admit when I'm wrong (see countdown timer above).
I do want to be the best. I'll probably never have the opportunity (nor the time) to do what Mr. Kyte has done. I'm not going to strive to be mediocre though. Whether I realize that goal or not is mainly irrelevant, but that is my goal...to be better than Mr. Kyte.
Labels: database, development, nerd, oracle, work
¶ 11/05/2007 08:48:00 PM 4 Comments Links to this post
Thursday, October 25, 2007
Looking for an Oracle Developer?
So I am on vacation (well, I wouldn't necessarily call being a pack mule vacation) at Disney World this week.
Yesterday I received a text message from one of my friends, our company was being overtaken by armed FBI agents, lots of 'em.
FBI raid shutters Medicare insurer
FBI Raids Tampa WellCare
I've never seen any of these types of activities, though the scope of duties has been fairly limited (I've been there less than a year). I find it hard to believe as everyone I have worked with on the IT side of things I KNOW is on the up and up. I've never been asked to do anything nefarious...My hope, that if true, it is only a select few individuals.
I thoroughly enjoy working there. I am surrounding by some great people, both personally and professionally, but I do have a family to feed so...
So if you are in the Tampa area and are looking for a hard-working Oracle developer (APEX, PL/SQL), either email me or post a comment (I won't post the comment, but I will receive it via email and I can contact you from there). I'll also work on getting my resume updated and online.
Yesterday I received a text message from one of my friends, our company was being overtaken by armed FBI agents, lots of 'em.
FBI raid shutters Medicare insurer
FBI Raids Tampa WellCare
I've never seen any of these types of activities, though the scope of duties has been fairly limited (I've been there less than a year). I find it hard to believe as everyone I have worked with on the IT side of things I KNOW is on the up and up. I've never been asked to do anything nefarious...My hope, that if true, it is only a select few individuals.
I thoroughly enjoy working there. I am surrounding by some great people, both personally and professionally, but I do have a family to feed so...
So if you are in the Tampa area and are looking for a hard-working Oracle developer (APEX, PL/SQL), either email me or post a comment (I won't post the comment, but I will receive it via email and I can contact you from there). I'll also work on getting my resume updated and online.
Labels: apex, development, oracle, work
¶ 10/25/2007 11:41:00 AM 0 Comments Links to this post
Wednesday, September 26, 2007
To LOOP or Not To LOOP - Revisited
In a previous post, I pondered the decision of LOOPing (i.e. PL/SQL calls to a lookup). Given 8 days to code and a 90 hour week last week I think I came up with a decent solution.
Initially I went the easy route, writing PL/SQL functions that would populate collections and then do the matching. I tried to tune the functions so that the collection would be populated only once for each line of business and then ordering the result set that would be passed through. In my mind, this would reduce the number of cursors opened. I wasn't if that would make a difference or not, but I was going to give it a try.
Well, it was dog slow. One of the lookups calls was estimated to take days to complete (I only know this because I have gotten into the habit of using dbms_application_info.set_session_longops).
So I rethought it.
I only needed to get the keys right?
I first created a primary key on the staging table (same key that would be used in the dimension). I then created a "work" table that would reference that key and then store the other key that I needed to lookup. I also made the foreign key a unique key (one to one relationship) so that I would know immediately if I did something wrong. I then used straight SQL to populate that table. One pass for all the values that matched and then another pass with those that didn't have a matching record (padded rows).
From days to minutes...2 minutes in fact. So I applied that technique to two other lookup situations.
The code is still pretty straight-forward and adding another line of business is relatively easy (I checked this today and it took all of 10 minutes). All the fancy PL/SQL and collection stuff I had created was thrown out the window.
So I did manage to use straight SQL, the performance is phenomenal and maintenance will be easy.
It even passed muster (albeit grudgingly) with our more seasoned datawarehouse folks. Ultimately, they couldn't complain when it ran in under two hours...
Initially I went the easy route, writing PL/SQL functions that would populate collections and then do the matching. I tried to tune the functions so that the collection would be populated only once for each line of business and then ordering the result set that would be passed through. In my mind, this would reduce the number of cursors opened. I wasn't if that would make a difference or not, but I was going to give it a try.
Well, it was dog slow. One of the lookups calls was estimated to take days to complete (I only know this because I have gotten into the habit of using dbms_application_info.set_session_longops).
So I rethought it.
I only needed to get the keys right?
I first created a primary key on the staging table (same key that would be used in the dimension). I then created a "work" table that would reference that key and then store the other key that I needed to lookup. I also made the foreign key a unique key (one to one relationship) so that I would know immediately if I did something wrong. I then used straight SQL to populate that table. One pass for all the values that matched and then another pass with those that didn't have a matching record (padded rows).
From days to minutes...2 minutes in fact. So I applied that technique to two other lookup situations.
The code is still pretty straight-forward and adding another line of business is relatively easy (I checked this today and it took all of 10 minutes). All the fancy PL/SQL and collection stuff I had created was thrown out the window.
So I did manage to use straight SQL, the performance is phenomenal and maintenance will be easy.
It even passed muster (albeit grudgingly) with our more seasoned datawarehouse folks. Ultimately, they couldn't complain when it ran in under two hours...
Labels: datawarehouse, etl, oracle, work
¶ 9/26/2007 11:29:00 PM 0 Comments Links to this post
Wednesday, September 19, 2007
To CONSTRAINT or Not to CONSTRAINT
I've been having these long (good) drawn out conversations with a colleague of mine recently about constraints in a datawarehouse environment. Since I come from an OLTP environment, I want to put a constraint on everything. He on the other hand wants any constraints enforced through code.
I can understand that constraints my slow down table loads, but with the volume of data we are currently using (100 million row tables), I just don't see that as a huge impact on performance. For me, writing less code is better than any minimal performance gains we might achieve.
For instance, today he told me he wanted to add a new column. The possible values would either be M or G. I asked (I'm lead on the project) him to throw a CHECK constraint on the table limiting the values that can go in that field.
For me, I guess it's a support issue and part database purist issue. Use it for more than a bucket, Oracle's expensive. It forces developers to deal with that and will immediately tell them if they have done something wrong. It also makes support a tad easier as they won't have to wonder what actually goes in the field.
We talked at length about it but never really came to a decision (i.e. I couldn't convince him).
I know that a benchmark test would be the best way to prove either way, but I don't have the time right now to do it. Perhaps when things slow down again I will.
Any suggestions out there?
I can understand that constraints my slow down table loads, but with the volume of data we are currently using (100 million row tables), I just don't see that as a huge impact on performance. For me, writing less code is better than any minimal performance gains we might achieve.
For instance, today he told me he wanted to add a new column. The possible values would either be M or G. I asked (I'm lead on the project) him to throw a CHECK constraint on the table limiting the values that can go in that field.
For me, I guess it's a support issue and part database purist issue. Use it for more than a bucket, Oracle's expensive. It forces developers to deal with that and will immediately tell them if they have done something wrong. It also makes support a tad easier as they won't have to wonder what actually goes in the field.
We talked at length about it but never really came to a decision (i.e. I couldn't convince him).
I know that a benchmark test would be the best way to prove either way, but I don't have the time right now to do it. Perhaps when things slow down again I will.
Any suggestions out there?
Labels: database, datawarehouse, oracle
¶ 9/19/2007 09:52:00 PM 0 Comments Links to this post
Monday, September 10, 2007
Oracle Tools I've Used
Tools:
SQL*Plus
APEX - Application Express (formerly HTMLDB)
JDeveloper
SQL Developer
Reports Builder
OC4J
Discoverer
Oracle Server 8i, 9i, 10g (Windows)
Oracle Application Server 9i, 10g (Windows)
rman
oradim
lsnrctl
tkprof
Coming Soon:
Oracle Warehouse Builder
Database Features:
Java
Object Types
Workflow
Advanced Queueing (in conjunction with Workflow)
Heterogenous Services
Features I'd like to use:
Change Data Capture
XML
Spatial
interMedia
Regular Expressions
Database Supplied Packages:
DBMS_OUTPUT
DBMS_LOB
UTL_FILE
DBMS_APPLICATION_INFO
DBMS_CRYPTO
DBMS_UTILITY
DBMS_METADATA
DBMS_EPG
DBMS_HS_PASSTHROUGH
DBMS_JAVA
DBMS_JOB
DBMS_LOCK
DBMS_MVIEW
DBMS_OBFUSCATION_TOOLKIT
DBMS_RANDOM
DBMS_SESSION
DBMS_SQL
DBMS_STATS
DBMS_XDB
DBMS_XPLAN
HTMLDB_APPLICATION
HTMLDB_ITEM
HTMLDB_UTIL
HTP
OWA_COOKIE
UTL_MAIL
UTL_RAW
UTL_SMTP
WPG_DOCLOAD
Packages I'd like learn to use (10g):
UTL_DBWS
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_DATA_MINING
DBMS_DATAPUMP
DBMS_SCHEDULER
DBMS_OLAP
DBMS_PIPE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
DBMS_STREAMS_MESSAGING
SQL*Plus
APEX - Application Express (formerly HTMLDB)
JDeveloper
SQL Developer
Reports Builder
OC4J
Discoverer
Oracle Server 8i, 9i, 10g (Windows)
Oracle Application Server 9i, 10g (Windows)
rman
oradim
lsnrctl
tkprof
Coming Soon:
Oracle Warehouse Builder
Database Features:
Java
Object Types
Workflow
Advanced Queueing (in conjunction with Workflow)
Heterogenous Services
Features I'd like to use:
Change Data Capture
XML
Spatial
interMedia
Regular Expressions
Database Supplied Packages:
DBMS_OUTPUT
DBMS_LOB
UTL_FILE
DBMS_APPLICATION_INFO
DBMS_CRYPTO
DBMS_UTILITY
DBMS_METADATA
DBMS_EPG
DBMS_HS_PASSTHROUGH
DBMS_JAVA
DBMS_JOB
DBMS_LOCK
DBMS_MVIEW
DBMS_OBFUSCATION_TOOLKIT
DBMS_RANDOM
DBMS_SESSION
DBMS_SQL
DBMS_STATS
DBMS_XDB
DBMS_XPLAN
HTMLDB_APPLICATION
HTMLDB_ITEM
HTMLDB_UTIL
HTP
OWA_COOKIE
UTL_MAIL
UTL_RAW
UTL_SMTP
WPG_DOCLOAD
Packages I'd like learn to use (10g):
UTL_DBWS
DBMS_CDC_PUBLISH
DBMS_CDC_SUBSCRIBE
DBMS_DATA_MINING
DBMS_DATAPUMP
DBMS_SCHEDULER
DBMS_OLAP
DBMS_PIPE
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
DBMS_STREAMS_MESSAGING
Labels: database, oracle, tools, utilities
¶ 9/10/2007 12:12:00 AM 0 Comments Links to this post
Wednesday, September 5, 2007
Business Logic: In the Database or in the Application
When I started in the Data Warehouse, I began subscribing to as many BI/Data Warehousing blogs that I could find (there don't seem to be lot of them). Intelligent Enterprise (Roger Kimball) and Bill Inmon seem to be the most popular or well known. I also read Mark Rittman (and Mr. Mead), David Aldridge, Lewis Cunningham and Dratz who all seem to have more of an Oracle lean.
Today I read a post from Dan Linstedt titled "How Data Models can Impact Business." I followed the rather lengthy, but descriptive post, until I got to this part:
Today I read a post from Dan Linstedt titled "How Data Models can Impact Business." I followed the rather lengthy, but descriptive post, until I got to this part:
"Ok, I kind-of buy it, but what about Referential Integrity, when should that be enforced?
In two places:
1) When the data is captured within the application capturing it - it would clean up a LOT of these source systems, and put much more rigorous business logic (and cleaner data) into the source systems to begin with.
2) When the data is "released" for users, to reports, to screens, to output. This is when reusable common services / routines for getting data out are helpful. They implement the referential integrity in the application layer."
Now, I haven't been in IT all that long (5+ years now), but I put as much business logic into the database as I possibly can. I use the front end (APEX, woohoo!), to handle row color or something along those lines.
If you start with a good, flexible model, have a strong database team (DBAs, Developers) and you build a good API to your physical model (no INSERT, UPDATE or DELETE to any users, the only entry point to your tables being your API), flexibility and maintainability should not be a problem. Making changes should not entail a monumental effort. I suppose if the application in Mr. Linstedt's article is the only point of entry into the tables, I probably wouldn't really disagree (he's just moved it from my database API to his application), but then you have a giant bucket. Why not just use what you have paid for and build it in the database? That way, more than one application can use the same API over and over.
Labels: database, development, oracle, work
¶ 9/05/2007 09:43:00 PM 3 Comments Links to this post
Monday, August 27, 2007
APEX Evangelism
I started using APEX, Application Express, formerly HTMLDB, more than two years ago.
Prior to APEX, anything web related I used Java (J2EE).
When I started at my previous job, they had virtually no internal systems. I was hired as a Reports Analyst/Data Analyst because they had decided their 3rd party vendor wasn't working out.
I somehow managed to talk them into purchasing an Oracle database (SE); since they wanted it quick, and that's what I knew, it was a no-brainer for my boss.
Relatively quickly I managed to nail down my first report after downloading all the data into our shiny new Oracle database. I wanted to impress so I began the tedious task of webifying it, and boy were they impressed.
Up to that point, I had been using a modified version of the Struts framework. It was definitely overkill. I created a bean (class) for the report data and a page (jsp) to display that data. It was a time consuming process at best.
I had read about HTMLDB through AskTom at the job before that one and it was intriguing. So I downloaded it at the new job and took it for a spin. Within a month I had a couple of pages up and running (complete with user login and our cool new graphs). It was difficult initially to wrap my head around the terminology, but once I did it was full speed ahead.
I found the forums on OTN for APEX and began researching problems and asking questions. Soon after that I was contributing. I was hooked.
I was now an APEX evangelist. I would extoll the virtues of APEX to anyone that would listen (mostly my wife, but she was just humoring me). By the time I left that job, 18 months later, our internal web site consisted of almost 350 pages.
At one point I read an job description something along the lines of "Developer wanted to help maintain 100 page web site with a team of 6." Six people? What? Why does it take so many people? What the hell are they using, Java?
I still consider myself an APEX evangelist, but I no longer get to work with it on a daily business. I did manage to convince my superiors though that it would be a good basic reporting tool for our data warehouse environment. We've built 2 applications so far that utilize APEX and hopefully there's more to come.
Prior to APEX, anything web related I used Java (J2EE).
When I started at my previous job, they had virtually no internal systems. I was hired as a Reports Analyst/Data Analyst because they had decided their 3rd party vendor wasn't working out.
I somehow managed to talk them into purchasing an Oracle database (SE); since they wanted it quick, and that's what I knew, it was a no-brainer for my boss.
Relatively quickly I managed to nail down my first report after downloading all the data into our shiny new Oracle database. I wanted to impress so I began the tedious task of webifying it, and boy were they impressed.
Up to that point, I had been using a modified version of the Struts framework. It was definitely overkill. I created a bean (class) for the report data and a page (jsp) to display that data. It was a time consuming process at best.
I had read about HTMLDB through AskTom at the job before that one and it was intriguing. So I downloaded it at the new job and took it for a spin. Within a month I had a couple of pages up and running (complete with user login and our cool new graphs). It was difficult initially to wrap my head around the terminology, but once I did it was full speed ahead.
I found the forums on OTN for APEX and began researching problems and asking questions. Soon after that I was contributing. I was hooked.
I was now an APEX evangelist. I would extoll the virtues of APEX to anyone that would listen (mostly my wife, but she was just humoring me). By the time I left that job, 18 months later, our internal web site consisted of almost 350 pages.
At one point I read an job description something along the lines of "Developer wanted to help maintain 100 page web site with a team of 6." Six people? What? Why does it take so many people? What the hell are they using, Java?
I still consider myself an APEX evangelist, but I no longer get to work with it on a daily business. I did manage to convince my superiors though that it would be a good basic reporting tool for our data warehouse environment. We've built 2 applications so far that utilize APEX and hopefully there's more to come.
Labels: apex, database, oracle
¶ 8/27/2007 11:40:00 PM 0 Comments Links to this post
Saturday, August 18, 2007
How I Got Started...
It all started 5 1/2 years ago, my first IT job as a reports developer. I was given a tnsnames file and SQL*Plus. What? What the hell am I supposed to do with this?
I learned about databases through Microsoft's Access. While I would never want to go back to it, I am thankful it was there or I probably would never have learned. I was basically a secretary (administrative assistant for the PC). I got tired of entering in the same thing every month and knew there had to be a better way. So I put it in Access to speed things up a bit. I began to annoy the crap out of the IT department. I now wanted raw feeds of the data so that I wouldn't have to type in anything...
Finally a friend came along and offered me a job.
That's when I met Oracle.
So there I sat on my first day wondering where the tables where. I couldn't "see" them. I needed to "see" them dammit. Within a week I had set up an Access database to be my front end to Oracle; I couldn't stand not "seeing" the tables.
That got old real quick though. The sheer volume of data caused that. Within weeks, I was using SQL*Plus and beggining to learn the data dictionary.
I'd ask my cube mate, "How do I see the source of the view?"
SELECT text FROM user_views WHERE view_name = 'MY_VIEW';
Oh yeah; SET LONG 100000
I was fortunate enough to have a helpful cube mate.
And so began my obsession...
I would say it took a good year before I really started to feel comfortable with SQL*Plus and Oracle, and 5 years later, I'm still using it.
I'm glad I was forced to learn Oracle that way. I believe that I have a far better understanding of how Oracle works because of my experience.
¶ 8/18/2007 11:48:00 PM 2 Comments Links to this post
I learned about databases through Microsoft's Access. While I would never want to go back to it, I am thankful it was there or I probably would never have learned. I was basically a secretary (administrative assistant for the PC). I got tired of entering in the same thing every month and knew there had to be a better way. So I put it in Access to speed things up a bit. I began to annoy the crap out of the IT department. I now wanted raw feeds of the data so that I wouldn't have to type in anything...
Finally a friend came along and offered me a job.
That's when I met Oracle.
So there I sat on my first day wondering where the tables where. I couldn't "see" them. I needed to "see" them dammit. Within a week I had set up an Access database to be my front end to Oracle; I couldn't stand not "seeing" the tables.
That got old real quick though. The sheer volume of data caused that. Within weeks, I was using SQL*Plus and beggining to learn the data dictionary.
I'd ask my cube mate, "How do I see the source of the view?"
SELECT text FROM user_views WHERE view_name = 'MY_VIEW';
Oh yeah; SET LONG 100000
I was fortunate enough to have a helpful cube mate.
And so began my obsession...
I would say it took a good year before I really started to feel comfortable with SQL*Plus and Oracle, and 5 years later, I'm still using it.
I'm glad I was forced to learn Oracle that way. I believe that I have a far better understanding of how Oracle works because of my experience.
¶ 8/18/2007 11:48:00 PM 2 Comments Links to this post
Thursday, August 16, 2007
What I Read...
Database
Oracle Documentation.
For over 5 years now I have been reading Tom Kyte's AskTom site hosted on Oracle's site. This is the first place I go when I have an Oracle related problem.
For the past 2 years or so, I have also been reading his blog.
The second source of information concerning Oracle is Metalink. I mostly find all my answers on AskTom, but for the more obscure problems, this is the best place to go.
Third would be the OTN forums, mainly the SQL and PL/SQL forum.
The C.D.O.S. group is next followed b
Database
Oracle Documentation.
For over 5 years now I have been reading Tom Kyte's AskTom site hosted on Oracle's site. This is the first place I go when I have an Oracle related problem.
For the past 2 years or so, I have also been reading his blog.
The second source of information concerning Oracle is Metalink. I mostly find all my answers on AskTom, but for the more obscure problems, this is the best place to go.
Third would be the OTN forums, mainly the SQL and PL/SQL forum.
The C.D.O.S. group is next followed b