Wednesday, August 20, 2008
Calculate Total Possible Score for Wii Bowling, Power Throws
Needless to say I have to much time on my hands. I've become obsessed with two Wii games, Bowling and Tanks. I play over and over. I had a perfect game in bowling the other day which wasn't quite as exciting as I thought it would be.
I've also been playing the "Training" game of Power Throws (bowling). Your given 10 shots with another row added each frame. You start out with 10, then 15, then 21, etc. If you get all the pins, you get a bonue (total pins * 2). So far, my highest score has been 651.
I've never bothered to figure out what the total possible score is though.
SQL to the rescue.
Using analytics and the CONNECT BY LEVEL option (10g and higher), it's fairly easy:
So how come no one wants to hire a guy that can do this kind of fun stuff with SQL? ;) ¶ 8/20/2008 10:04:00 PM 3 Comments Links to this post
I've also been playing the "Training" game of Power Throws (bowling). Your given 10 shots with another row added each frame. You start out with 10, then 15, then 21, etc. If you get all the pins, you get a bonue (total pins * 2). So far, my highest score has been 651.
I've never bothered to figure out what the total possible score is though.
SQL to the rescue.
Using analytics and the CONNECT BY LEVEL option (10g and higher), it's fairly easy:
I had to set the filter on the outer query because you start with 4 rows (10 pins). And the results:
SELECT
pin_row,
pins,
running_total,
rt_with_bonus
FROM
(
SELECT
rownum pin_row,
rownum pins,
SUM( rownum ) OVER ( ORDER BY rownum ) running_total,
( SUM( rownum ) OVER ( ORDER BY rownum ) * 2 ) rt_with_bonus
FROM dual
CONNECT BY LEVEL <= 13
)
WHERE pins >= 4
Voila! 890 is the best possible score if I strike every frame. I did manage to pass my high score by 9 pins tonight as well. It's a great day!
CJUSTICE@XE>BREAK ON REPORT
CJUSTICE@XE>COMPUTE SUM OF RUNNING_TOTAL ON REPOR
CJUSTICE@XE>COMPUTE SUM OF RT_WITH_BONUS ON REPOR
CJUSTICE@XE>/
PIN_ROW PINS RUNNING_TOTAL RT_WITH_BONUS
---------- ---------- ------------- -------------
4 4 10 20
5 5 15 30
6 6 21 42
7 7 28 56
8 8 36 72
9 9 45 90
10 10 55 110
11 11 66 132
12 12 78 156
13 13 91 182
------------- -------------
sum 445 890
So how come no one wants to hire a guy that can do this kind of fun stuff with SQL? ;) ¶ 8/20/2008 10:04:00 PM 3 Comments Links to this post
Thursday, July 31, 2008
Oracle Mix: Two Ideas
I've added two ideas at Oracle Mix. The first one is UTL_FILE has the ability to read the contents of a directory and the second one is Add a pseudo-column that stores the date the record was INSERTed or UPDATEd.
I mentioned the second one before. The first one I've seen on the iloveplsqland.net site. So go and vote (or don't, but leave your comments).
I mentioned the second one before. The first one I've seen on the iloveplsqland.net site. So go and vote (or don't, but leave your comments).
Labels: development, ideas, oracle, oracle mix, sql
¶ 7/31/2008 11:15:00 PM 0 Comments Links to this post
11g New Feature: PIVOT
I do have an interview tomorrow. Woohoo!
So my go at the PIVOT operator, "new" in 11g.
pivot_clause
Datawarehouse Guide
SQL Reference Examples
Arup Nanda's Example
My example.
Let's create some data first:
Basically, PIVOT allows you to pivot rows into columns. We often do this for reports we generate. Here's the old way:
Here's the new cool way:
So my go at the PIVOT operator, "new" in 11g.
pivot_clause
Datawarehouse Guide
SQL Reference Examples
Arup Nanda's Example
My example.
Let's create some data first:
So what's the big deal with PIVOT? I'm not sure yet other than it's something new and new is cool.
CREATE TABLE transaction_types
(
transactiontypecode VARCHAR2(10)
CONSTRAINT pk_transactiontypecode PRIMARY KEY
);
INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'DEBIT' );
INSERT INTO transaction_types( transactiontypecode )
VALUES ( 'CREDIT' );
CREATE TABLE transactions
(
id NUMBER(10)
CONSTRAINT pk_id PRIMARY KEY,
transactiontypecode
CONSTRAINT fk_ttcode_transactions REFERENCES transaction_types( transactiontypecode )
CONSTRAINT nn_ttcode_transactions NOT NULL,
amount NUMBER(16,2)
CONSTRAINT nn_amount_trans NOT NULL
CONSTRAINT ck_amount_trans CHECK ( amount >= 0 ),
date_created DATE DEFAULT SYSDATE
CONSTRAINT nn_datecreated_trans NOT NULL
);
INSERT INTO transactions
( id,
transactiontypecode,
amount )
VALUES
( 1,
'DEBIT',
44.44 );
INSERT INTO transactions
( id,
transactiontypecode,
amount )
VALUES
( 2,
'DEBIT',
20.34 );
INSERT INTO transactions
( id,
transactiontypecode,
amount )
VALUES
( 3,
'CREDIT',
5.60 );
INSERT INTO transactions
( id,
transactiontypecode,
amount )
VALUES
( 4,
'DEBIT',
67 );
INSERT INTO transactions
( id,
transactiontypecode,
amount )
VALUES
( 5,
'DEBIT',
234.55 );
INSERT INTO transactions
( id,
transactiontypecode,
amount )
VALUES
( 6,
'CREDIT',
76.55 );
INSERT INTO transactions
( id,
transactiontypecode,
amount )
VALUES
( 7,
'DEBIT',
3.45 );
Basically, PIVOT allows you to pivot rows into columns. We often do this for reports we generate. Here's the old way:
Really, not that bad. But if I want to do COUNT and AVG, I have to create more CASE statements like the ones above. My query will go from 14 lines to 30 in a hurry.
SELECT
TRUNC( date_created ) date_created,
SUM( CASE
WHEN transactiontypecode = 'DEBIT' THEN
amount
END ) debit_amount,
SUM( CASE
WHEN transactiontypecode = 'CREDIT' THEN
amount
END ) credit_amount
FROM transactions
GROUP BY
TRUNC( date_created )
ORDER BY date_created;
DATE_CREA DEBIT_AMOUNT CREDIT_AMOUNT
--------- ------------ -------------
31-JUL-08 369.78 82.15
Here's the new cool way:
It happens that is 14 rows as well. Now I'll add AVG and COUNT:
SELECT *
FROM
(
SELECT
transactiontypecode tt,
TRUNC( date_created ) date_created,
amount
FROM transactions
)
PIVOT
(
SUM( amount ) total_amount
FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);
DATE_CREA DEBIT_TOTAL_AMOUNT CREDIT_TOTAL_AMOUNT
--------- ------------------ -------------------
31-JUL-08 369.78 82.15
SELECT *Nice! Sixteen lines of SQL...not bad at all. That should make code a bit more readable (it'll fit on a single page)...I like it!
FROM
(
SELECT
transactiontypecode tt,
TRUNC( date_created ) date_created,
amount
FROM transactions
)
PIVOT
(
SUM( amount ) total_amount,
COUNT( amount ) total_count,
AVG( amount ) avg_amount
FOR tt IN ( 'DEBIT' AS "DEBIT", 'CREDIT' AS "CREDIT" )
);
DATE_CREA D_AMT D_CNT D_AVG C_AMT C_CNT C_AVG
--------- ---------- ---------- ---------- ---------- ---------- ----------
31-JUL-08 369.78 5 73.956 82.15 2 41.075
Labels: 11g, development, sql
¶ 7/31/2008 05:15:00 PM 0 Comments Links to this post
Monday, July 14, 2008
Commas: Before or After the Line?
I'm in a new group again which means I have to learn (and accept) other people's style. No one at WellCare put commas before the line (thankfully), but I've found a few here.
I've finally come to accept that this is just style and doesn't really matter, as long as the code does what it's intended to do.
Yes, it's silly, but we all have our little quirks right? ¶ 7/14/2008 10:36:00 PM 8 Comments Links to this post
I'm in a new group again which means I have to learn (and accept) other people's style. No one at WellCare put commas before the line (thankfully), but I've found a few here.
I've finally come to accept that this is just style and doesn't really matter, as long as the code does what it's intended to do.
Yes, it's silly, but we all have our little quirks right? ¶ 7/14/2008 10:36:00 PM 8 Comments Links to this post
Tuesday, July 8, 2008
Count The K's
I have this really annoying co-worker who happens to be the DBA. Everytime he walks by my desk he pounces on my keyboard. I've learned to Windows Key + L to lock the computer when I hear him approaching, but occasionally I forget.
As I'm standing near my unlocked computer he starts typing furiously in my sql*plus session (shouldn't he know better?). I should know better...
Walking away he asks, "How many K's are in there?"
I ignored him, but then wondered myself...what's the best solution to this problem?
So, here it goes:
Then I started thinkinhg...can I do this in pure SQL? Of course!
As I'm standing near my unlocked computer he starts typing furiously in my sql*plus session (shouldn't he know better?). I should know better...
Walking away he asks, "How many K's are in there?"
I ignored him, but then wondered myself...what's the best solution to this problem?
So, here it goes:
DECLAREEasy enough, 45.
l_count_k NUMBER := 0;
l_string VARCHAR2(300);
l_string_length INTEGER;
BEGIN
l_string := 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
TKELTKER';
l_string_length := LENGTH( l_string );
FOR i IN 1..l_string_length LOOP
IF SUBSTR( l_string, i, 1 ) IN ( 'K', 'k' ) THEN
l_count_k := l_count_k + 1;
END IF;
END LOOP;
dbms_output.put_line( 'Kk Count: ' || l_count_k );
END;
/
Then I started thinkinhg...can I do this in pure SQL? Of course!
SELECTI'm sure many of you can do better than that. So let's see 'em. Prodlife, this isn't a complicators test either. ;) ¶ 7/08/2008 09:00:00 PM 9 Comments Links to this post
SUM( CASE
WHEN SUBSTR( UPPER( mystring ), rownum, 1 ) = 'K' THEN
1
END ) k
FROM
dual,
(
SELECT 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
TKELTKER' mystring
FROM dual
) c
CONNECT BY LEVEL <= LENGTH( mystring )
COUNT_OF_K
----------
45
1 row selected.
Wednesday, June 11, 2008
SQL for Buying a New Car
Two weeks ago I purchased a new car. The main reason behind that was that I drove a 1996 Nissan P.O.S. (not point of sale either). I now had a 30+ mile commute each way and I (well, my wife and mother) needed a reliable car.
A big factor was gas mileage.
Two brands stood out, Honda and Toyota. Honda has the Civic Hybrid and Toyota has the Prius (even though I don't care much for the design). The Corolla and Civic were the top two cars.
So after visiting those two dealers to look at cars, I stopped by the Mazda dealer to see if they had anything with MPG greater than 30. I drove the Mazda3 and it was nice (everything would be nice compared to my POS). Salesman offered to run the numbers and I said OK.
The point of this rambling, is that I want to know what it will cost based on mileage. Of course I didn't have my laptop, so I couldn't run the numbers (and I hadn't previously). I did it in Excel, but wanted to try it out in SQL.
Here's the result:
Three or four years ago I would have created a table and a function to do this. Now I can do it "virtually."
I love SQL...
*Key:
car_1 = miles for car 1
car_2 = miles for car 2
car_1_gpy = gallons per year car 1
car_2_gpy = gallons per year car 2
car_1_cpy = cost per year car 1 (gas)
car_2_cpy = cost per year car 2 (gas)
car_1_cpm = cost per mile car 1
car_2_cpm = cost per mile car 2 ¶ 6/11/2008 07:58:00 PM 3 Comments Links to this post
A big factor was gas mileage.
Two brands stood out, Honda and Toyota. Honda has the Civic Hybrid and Toyota has the Prius (even though I don't care much for the design). The Corolla and Civic were the top two cars.
So after visiting those two dealers to look at cars, I stopped by the Mazda dealer to see if they had anything with MPG greater than 30. I drove the Mazda3 and it was nice (everything would be nice compared to my POS). Salesman offered to run the numbers and I said OK.
The point of this rambling, is that I want to know what it will cost based on mileage. Of course I didn't have my laptop, so I couldn't run the numbers (and I hadn't previously). I did it in Excel, but wanted to try it out in SQL.
Here's the result:
Obviously this isn't terribly difficult. You can do lots with the results as well.
COLUMN ppg FORMAT $999.00
COLUMN car_1_gpy FORMAT 99,999
COLUMN car_2_gpy FORMAT 99,999
COLUMN car_1_cpy FORMAT $999,999.00
COLUMN car_2_cpy FORMAT $999,999.00
COLUMN car_1_cpm FORMAT 999.00
COLUMN car_2_cpm FORMAT 999.00
SELECT
car_1,
car_2,
ppg,
mpy,
ROUND( ( mpy / car_1 ) ) car_1_gpy,
ROUND( ( mpy / car_2 ) ) car_2_gpy,
ROUND( ( ( mpy / car_1 ) * ppg ), 2 ) car_1_cpy,
ROUND( ( ( mpy / car_2 ) * ppg ), 2 ) car_2_cpy,
ROUND( ( ( mpy / car_1 ) * ppg ) / mpy, 2 ) car_1_cpm,
ROUND( ( ( mpy / car_2 ) * ppg ) / mpy, 2 ) car_2_cpm
FROM
(
SELECT
rownum car_1,
LAG( rownum, 5 ) OVER
( PARTITION BY NULL
ORDER BY rownum ) car_2
FROM dual
CONNECT BY LEVEL <= 50
) car_miles,
(
SELECT
3.99 ppg,
15000 mpy
FROM dual
) constants
/
CAR_1 CAR_2 CAR_1_GPY CAR_2_GPY CAR_1_CPY CAR_2_CPY CAR_1_CPM CAR_2_CPM
---------- ---------- --------- --------- ------------ ------------ --------- ---------
1 15,000 $59,850.00 3.99
2 7,500 $29,925.00 2.00
3 5,000 $19,950.00 1.33
4 3,750 $14,962.50 1.00
5 3,000 $11,970.00 .80
6 1 2,500 15,000 $9,975.00 $59,850.00 .67 3.99
7 2 2,143 7,500 $8,550.00 $29,925.00 .57 2.00
8 3 1,875 5,000 $7,481.25 $19,950.00 .50 1.33
9 4 1,667 3,750 $6,650.00 $14,962.50 .44 1.00
10 5 1,500 3,000 $5,985.00 $11,970.00 .40 .80
11 6 1,364 2,500 $5,440.91 $9,975.00 .36 .67
12 7 1,250 2,143 $4,987.50 $8,550.00 .33 .57
13 8 1,154 1,875 $4,603.85 $7,481.25 .31 .50
14 9 1,071 1,667 $4,275.00 $6,650.00 .28 .44
15 10 1,000 1,500 $3,990.00 $5,985.00 .27 .40
16 11 938 1,364 $3,740.63 $5,440.91 .25 .36
17 12 882 1,250 $3,520.59 $4,987.50 .23 .33
18 13 833 1,154 $3,325.00 $4,603.85 .22 .31
19 14 789 1,071 $3,150.00 $4,275.00 .21 .28
20 15 750 1,000 $2,992.50 $3,990.00 .20 .27
21 16 714 938 $2,850.00 $3,740.63 .19 .25
22 17 682 882 $2,720.45 $3,520.59 .18 .23
23 18 652 833 $2,602.17 $3,325.00 .17 .22
24 19 625 789 $2,493.75 $3,150.00 .17 .21
25 20 600 750 $2,394.00 $2,992.50 .16 .20
26 21 577 714 $2,301.92 $2,850.00 .15 .19
27 22 556 682 $2,216.67 $2,720.45 .15 .18
28 23 536 652 $2,137.50 $2,602.17 .14 .17
29 24 517 625 $2,063.79 $2,493.75 .14 .17
30 25 500 600 $1,995.00 $2,394.00 .13 .16
31 26 484 577 $1,930.65 $2,301.92 .13 .15
32 27 469 556 $1,870.31 $2,216.67 .12 .15
33 28 455 536 $1,813.64 $2,137.50 .12 .14
34 29 441 517 $1,760.29 $2,063.79 .12 .14
35 30 429 500 $1,710.00 $1,995.00 .11 .13
36 31 417 484 $1,662.50 $1,930.65 .11 .13
37 32 405 469 $1,617.57 $1,870.31 .11 .12
38 33 395 455 $1,575.00 $1,813.64 .11 .12
39 34 385 441 $1,534.62 $1,760.29 .10 .12
40 35 375 429 $1,496.25 $1,710.00 .10 .11
41 36 366 417 $1,459.76 $1,662.50 .10 .11
42 37 357 405 $1,425.00 $1,617.57 .09 .11
43 38 349 395 $1,391.86 $1,575.00 .09 .11
44 39 341 385 $1,360.23 $1,534.62 .09 .10
45 40 333 375 $1,330.00 $1,496.25 .09 .10
46 41 326 366 $1,301.09 $1,459.76 .09 .10
47 42 319 357 $1,273.40 $1,425.00 .08 .09
48 43 313 349 $1,246.88 $1,391.86 .08 .09
49 44 306 341 $1,221.43 $1,360.23 .08 .09
50 45 300 333 $1,197.00 $1,330.00 .08 .09
Three or four years ago I would have created a table and a function to do this. Now I can do it "virtually."
I love SQL...
*Key:
car_1 = miles for car 1
car_2 = miles for car 2
car_1_gpy = gallons per year car 1
car_2_gpy = gallons per year car 2
car_1_cpy = cost per year car 1 (gas)
car_2_cpy = cost per year car 2 (gas)
car_1_cpm = cost per mile car 1
car_2_cpm = cost per mile car 2 ¶ 6/11/2008 07:58:00 PM 3 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
Wednesday, May 21, 2008
Multi Table INSERTs
I learned something new today! Multi Table INSERTs.
I was trying to capture the new records into a staging table using RETURNING BULK COLLECT INTO, but that doesn't work (yet).
I remembered reading about Multi Table INSERTs, but never had the need for using them, so never bothered to learn it.
Awesome!
I was trying to capture the new records into a staging table using RETURNING BULK COLLECT INTO, but that doesn't work (yet).
I remembered reading about Multi Table INSERTs, but never had the need for using them, so never bothered to learn it.
CREATE TABLE t
(
x NUMBER PRIMARY KEY,
y NUMBER,
z NUMBER
);
CREATE TABLE s
(
x NUMBER PRIMARY KEY
);
INSERT ALL
INTO t ( x, y, z )
VALUES ( myrownum, ran1, ran2 )
INTO s ( x )
VALUES ( myrownum )
SELECT
rownum myrownum,
dbms_random.value ran1,
dbms_random.value ran2
FROM dual
CONNECT BY LEVEL < 101;
CJUSTICE@ORA10GR2>SELECT COUNT(*) FROM t;
COUNT(*)
----------
100
1 row selected.
Elapsed: 00:00:00.01
CJUSTICE@ORA10GR2>SELECT COUNT(*) FROM s;
COUNT(*)
----------
100
1 row selected.
Elapsed: 00:00:00.02
Awesome!
Labels: database, datawarehouse, sql
¶ 5/21/2008 05:17:00 PM 1 Comments Links to this post
Thursday, May 1, 2008
Validating a Process Part II
Continued from my previous post.
While discussing external tables with my feisty colleague some time back, I explained that I liked using them but I couldn't figure out how to change the file name to match that of what was defined in the table definition.
Colleage to the rescue:
As I mentioned before, I had 4 files types I had to read: 820 and 835, both of the x12 format and two custom file layouts (flat files essentially). Since no one in the group knows Java yet, I wanted to keep the Java portion of the application as small as possible. So with the two custom files, I decided to use external tables. I could then put into practice the above ALTER TABLE statement.
As I looped through the list of files to be processed, I would issue an EXECUTE IMMEDIATE so that I could then SELECT from the table in the next step. It worked like a charm.
As I was doing some testing, I would issue the ROLLBACK statement to clear the tables for the next run. When I verified, there was still data there. WTF?
Oh wait, there's an EXECUTE IMMEDIATE...which runs DDL...which COMMITs...barnacles!
So I couldn't use that new thing I learned, oh well. Fortunately UTL_FILE does have the ability to rename files so I picked a name like 'external_table_file_name.txt' and rename the incoming file to that, then SELECT. Works like a charm.
While discussing external tables with my feisty colleague some time back, I explained that I liked using them but I couldn't figure out how to change the file name to match that of what was defined in the table definition.
Colleage to the rescue:
Cool!
ALTER TABLE table_name LOCATION ( 'new_file_name.csv' );
As I mentioned before, I had 4 files types I had to read: 820 and 835, both of the x12 format and two custom file layouts (flat files essentially). Since no one in the group knows Java yet, I wanted to keep the Java portion of the application as small as possible. So with the two custom files, I decided to use external tables. I could then put into practice the above ALTER TABLE statement.
As I looped through the list of files to be processed, I would issue an EXECUTE IMMEDIATE so that I could then SELECT from the table in the next step. It worked like a charm.
As I was doing some testing, I would issue the ROLLBACK statement to clear the tables for the next run. When I verified, there was still data there. WTF?
Oh wait, there's an EXECUTE IMMEDIATE...which runs DDL...which COMMITs...barnacles!
So I couldn't use that new thing I learned, oh well. Fortunately UTL_FILE does have the ability to rename files so I picked a name like 'external_table_file_name.txt' and rename the incoming file to that, then SELECT. Works like a charm.
Labels: ddl, java, sql, utl_file
¶ 5/01/2008 11:40:00 PM 5 Comments Links to this post
Monday, April 21, 2008
It's a Matter of Time
I've been thinking a lot lately about my recent failed deployments.
How did I get so sloppy?
I'm not one to make excuses, but I would say there are some mitigating circumstances at least. Time being one of them.
So I got out my trusty calculator (SQL*Plus), and ran the numbers.
From August 26, 2007 through March 30, 2008, I've worked 1802 hours. Of that, 118 were PTO or holiday, which brings the total down to 1784. For perspective, a work year of 8 hours per day comes out to 2080 hours a year.
Cool! Only 86% of my hours in a little over 6 months!
Obviously, this is not a good thing.
Further breaking the numbers down:
DB = Days Between
BD = Business Days
HPD = Hours Per Day
HPDW = Hours Per Day/Week
HPWD = Hours Per Work Day
HPWW = Hours Per Work Week
The scary part is that I am not very diligent about entering my time. It's probably short anywhere between 5 and 10%.
I'm not the only one working these kinds of hours either. I know for a fact there are others.
Do you think this plays a role in my failed deployments?
How did I get so sloppy?
I'm not one to make excuses, but I would say there are some mitigating circumstances at least. Time being one of them.
So I got out my trusty calculator (SQL*Plus), and ran the numbers.
From August 26, 2007 through March 30, 2008, I've worked 1802 hours. Of that, 118 were PTO or holiday, which brings the total down to 1784. For perspective, a work year of 8 hours per day comes out to 2080 hours a year.
VAR HOURS NUMBER;
EXEC :hours := 1784
1 SELECT
2 ROUND( ( :HOURS / 2080 ) * 100, 1 ) per_of_tot_year_hours
3* FROM DUAL
ETL_WRK@ORA10GR2>/
PER_OF_TOT_YEAR_HOURS
---------------------
85.8
Cool! Only 86% of my hours in a little over 6 months!
Obviously, this is not a good thing.
Further breaking the numbers down:
VAR C VARCHAR2(10);
EXEC :C := '26-AUG-07';
SELECT
start_day,
end_day,
days_between db,
SUM( business_days ) bd,
ROUND( ( :hours / days_between ), 1 ) hpd,
ROUND( ( :hours / ( days_between / 7 ) ), 1 ) hpdw,
ROUND( ( :hours / SUM( business_days ) ), 1 ) hpwd,
ROUND( ( :hours / SUM( business_days / 5 ) ), 1 ) hpww
FROM
(
SELECT
start_day,
end_day,
TRUNC( end_day - start_day ) days_between,
start_day + rownum dayof,
( CASE
WHEN TO_CHAR( start_day + rownum, 'D' ) IN ( 2, 3, 4, 5, 6 ) THEN
1
END ) business_days
FROM
dual a,
(
SELECT
TO_DATE( :c, 'DD-MON-YY' ) start_day,
TO_DATE( '30-MAR-08', 'DD-MON-YY' ) end_day
FROM dual
) b
CONNECT BY LEVEL <= TRUNC( end_day - start_day )
)
GROUP BY
start_day,
end_day,
days_between
/
START_DAY END_DAY DB BD HPD HPDW HPWD HPWW
---------- ---------- ------ ------ ------ ------ ------ ------
08/26/2007 03/30/2008 217 155 8.2 57.5 11.5 57.5
DB = Days Between
BD = Business Days
HPD = Hours Per Day
HPDW = Hours Per Day/Week
HPWD = Hours Per Work Day
HPWW = Hours Per Work Week
The scary part is that I am not very diligent about entering my time. It's probably short anywhere between 5 and 10%.
I'm not the only one working these kinds of hours either. I know for a fact there are others.
Do you think this plays a role in my failed deployments?
Labels: discipline, sql, work
¶ 4/21/2008 08:11:00 PM 4 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 1 Comments Links to this post
Sunday, March 30, 2008
Failed Deployment...
236 Days
20 Hours
48 Minutes
30 Seconds
I hate making mistakes but I've made another one. My streak ends almost 237 days from my previous one.
Something so silly too.
In our source system, data was been double loaded somehow. So we decided on a surgical delete. A total of 7 DELETE statements needed to be run; 4 on the source system and 3 on the target system.
The source system went off without a hitch. I babysat the re-load of the source tables and was ready to have our load jobs run in our target system.
What's this? It ran in half the time?! How's that possible?
I pulled up our logs to find that zero rows were loaded into one of our tables. There should have been 45 Million plus.
I started to run down the possible causes:
1. Did the job we have in the scheduler that TRUNCATEs our persistent staging tables run? Nope.
2. Did I fail to instruct the DBAs correctly in the critical CR? Nope. Instructions look good.
3. Next to the logs, it ran fine on Saturday morning but not Sunday morning. What happened yesterday?
4. Ah yes, my CR. Open up the script...nothing out of the ordinary...and then I saw it.
On our target system, we use work tables to pre-generate a keys. It makes things a heck of a lot faster and removes the need for PL/SQL lookups in SQL (no, we don't have incremental builds yet).
So the work table needs to be DELETEd from first based on the keys from the first:
OK, no funny business there.
Then I DELETE from the main table:
As I look at it I wonder WTH I was thinking using an EXISTS clause on the main table. That's the source.
But do you see what I missed?
See it yet?
OK, I left out the "AND my_key = a.my_key" from the inner query. Obviously a stupid approach, but it would have worked. The best way to do it is to just get rid of the EXISTS clause:
Live and learn, live and learn...
20 Hours
48 Minutes
30 Seconds
I hate making mistakes but I've made another one. My streak ends almost 237 days from my previous one.
Something so silly too.
In our source system, data was been double loaded somehow. So we decided on a surgical delete. A total of 7 DELETE statements needed to be run; 4 on the source system and 3 on the target system.
The source system went off without a hitch. I babysat the re-load of the source tables and was ready to have our load jobs run in our target system.
What's this? It ran in half the time?! How's that possible?
I pulled up our logs to find that zero rows were loaded into one of our tables. There should have been 45 Million plus.
I started to run down the possible causes:
1. Did the job we have in the scheduler that TRUNCATEs our persistent staging tables run? Nope.
2. Did I fail to instruct the DBAs correctly in the critical CR? Nope. Instructions look good.
3. Next to the logs, it ran fine on Saturday morning but not Sunday morning. What happened yesterday?
4. Ah yes, my CR. Open up the script...nothing out of the ordinary...and then I saw it.
On our target system, we use work tables to pre-generate a keys. It makes things a heck of a lot faster and removes the need for PL/SQL lookups in SQL (no, we don't have incremental builds yet).
So the work table needs to be DELETEd from first based on the keys from the first:
DELETE FROM some_key_table a
WHERE EXISTS ( SELECT NULL
FROM the_main_table
WHERE business IN ( 'TTT', 'TTR' )
AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' )
AND my_key = a.my_key );
OK, no funny business there.
Then I DELETE from the main table:
DELETE FROM the_main_table a
WHERE EXISTS ( SELECT NULL
FROM the_main_table
WHERE business IN ( 'TTT', 'TTR' )
AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' ) );
As I look at it I wonder WTH I was thinking using an EXISTS clause on the main table. That's the source.
But do you see what I missed?
See it yet?
OK, I left out the "AND my_key = a.my_key" from the inner query. Obviously a stupid approach, but it would have worked. The best way to do it is to just get rid of the EXISTS clause:
DELETE FROM the_main_table a
WHERE business IN ( 'TTT', 'TTR' )
AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' ) );
Live and learn, live and learn...
Labels: funny, humility, sql, work
¶ 3/30/2008 10:21:00 PM 0 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
Tuesday, December 11, 2007
SAS: Create SAS Dataset from Oracle
SAS is very prevalent in our environment. When testing, I need to create some datasets for the business users so they they can do their UAT (yes, I shouldn't be involved at this point...but I am).
Here's the syntax to do that (I use SAS EG):
You can also use the following syntax:
If you want to use Oracle SQL (SAS guys and gals call it passthrough SQL), here's the syntax for that:
Here's the syntax to do that (I use SAS EG):
LIBNAME dmart oracle path=<oracle_sid> schema=<schema_name> pw=<password> user=<db_username>;
CREATE TABLE sasave.mytable
AS
SELECT * FROM dmart.mytable;
QUIT;
You can also use the following syntax:
LIBNAME dmart oracle path=<oracle_sid> schema=<schema_name> pw=<password> user=<db_username>;
DATA sasave.mytable_filtered;
SET dmart.mytable
( WHERE = id IN ( 5, 6, 7, 8 ) );
RUN;
If you want to use Oracle SQL (SAS guys and gals call it passthrough SQL), here's the syntax for that:
¶ 12/11/2007 02:14:00 PM 0 Comments Links to this post
PROC SQL NOPRINT;
CONNECT TO oracle( username=<username> pw=<password> path="<oracle_sid>");
CREATE TABLE sasave.dual_test
AS
SELECT *
FROM connection to oracle
( SELECT * FROM DUAL );
disconnect from oracle;
QUIT;
Sunday, December 2, 2007
Instrumentation: DBMS_APPLICATION_INFO
Instrumentation has something that I have come to rely on fairly heavily. I believe I first read about it on asktom, but the one that really spurred me on was this post on instrumentation on his personal blog.
Initially, I couldn't really wrap my head around instrumentation. I don't know why it was so difficult; I had a similar problem with sessions when I first started my career. I look back now and it just seems so obvious.
Now that I am doing datawarehouse work, nothing is fast. Fast to me is now one hour to load 30 or 40 million records. No more split second queries for me.
We currently use no tools. It's straight PL/SQL. Instrumentation of the code is ideal. Actually, it's more instrumentation to aid monitoring. The tool most easily used is provided by Oracle in the DBMS_APPLICATION_INFO package.
There are three subprograms that I use most, SET_MODULE, SET_ACTION and most importantly SET_SESSION_LONGOPS. I hadn't started using it until this year, I mainly stuck to the first two. SET_SESSION_LONGOPS is now part of my procedure/function template I've created in JDeveloper.
What it allows you to do is set a row in the v$session_longops view (I know it's not actually putting the row in the view...it's the underlying table, but I digress). You can then monitor how your job is doing.
Here's an example:
g_index and g_slno are global variables in the package. l_table is a PL/SQL TABLE OF VARCHAR2.
Now you can monitor the progress of your job in v$session_longops!
Here's the query I use:
Now you too can sit for hours and watch your job move incrementally forward!
But seriously, it does help tremendously to know where a job is at. You can further use the SET_MODULE and SET_ACTION calls to see a specific point in the processing (inside a loop).
Here's the code in context:
Initially, I couldn't really wrap my head around instrumentation. I don't know why it was so difficult; I had a similar problem with sessions when I first started my career. I look back now and it just seems so obvious.
Now that I am doing datawarehouse work, nothing is fast. Fast to me is now one hour to load 30 or 40 million records. No more split second queries for me.
We currently use no tools. It's straight PL/SQL. Instrumentation of the code is ideal. Actually, it's more instrumentation to aid monitoring. The tool most easily used is provided by Oracle in the DBMS_APPLICATION_INFO package.
There are three subprograms that I use most, SET_MODULE, SET_ACTION and most importantly SET_SESSION_LONGOPS. I hadn't started using it until this year, I mainly stuck to the first two. SET_SESSION_LONGOPS is now part of my procedure/function template I've created in JDeveloper.
What it allows you to do is set a row in the v$session_longops view (I know it's not actually putting the row in the view...it's the underlying table, but I digress). You can then monitor how your job is doing.
Here's an example:
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBER DATA',
sofar => 0,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBER DATA' );
g_index and g_slno are global variables in the package. l_table is a PL/SQL TABLE OF VARCHAR2.
Now you can monitor the progress of your job in v$session_longops!
Here's the query I use:
SELECT
username,
sid,
serial#,
TO_CHAR( start_time, 'MM/DD/YYYY HH24:MI:SS' ) start_ti,
time_remaining rem,
elapsed_seconds ela,
ROUND( ( sofar / REPLACE( totalwork, 0, 1 ) ) * 100, 2 ) per,
sofar,
totalwork work,
message,
target_desc
FROM v$session_longops
WHERE start_time >= SYSDATE - 1
ORDER BY start_time DESC
Now you too can sit for hours and watch your job move incrementally forward!
But seriously, it does help tremendously to know where a job is at. You can further use the SET_MODULE and SET_ACTION calls to see a specific point in the processing (inside a loop).
Here's the code in context:
PROCEDURE get_member_data
IS
l_exists INTEGER;
TYPE table_of_lobs IS TABLE OF VARCHAR2(3);
l_table TABLE_OF_LOBS := TABLE_OF_LOBS( 'COM', 'ORG' );
l_count INTEGER := 0;
BEGIN
--check to see if there is enrollment data, if not, move on
SELECT COUNT(*)
INTO l_exists
FROM members
WHERE rownum < 2;
IF l_exists = 1 THEN--data exists, truncate and reload
g_index := dbms_application_info.set_session_longops_nohint;
EXECUTE IMMEDIATE 'TRUNCATE TABLE member_stg';
g_audit_key := p_audit.begin_load
( p_targettable => 'MEMBER_STG',
p_loadsource => 'MEMBER_SOURCE',
p_loadstatus => 'PRE',
p_loadprogram => 'GET_MEMBER_DATA',
p_commenttext => 'INSERT' );
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBERS',
sofar => 0,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBERS' );
FOR i IN 1..l_table.COUNT LOOP
l_count := l_count + 1;
INSERT INTO member_stg
SELECT *
FROM members;
g_total_rows_affected := g_total_rows_affected + sql%rowcount;
COMMIT;
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBERS',
sofar => l_count,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBERS' );
END LOOP;
p_audit.end_load
( p_auditkey => g_audit_key,
p_loadstatus => 'SUC',
p_rowsuccess => g_total_rows_affected );
gather_table_stats
( p_tablename => 'MEMBER_STG',
p_schemaname => 'MYHOME' );
dbms_application_info.set_session_longops
( rindex => g_index,
slno => g_slno,
op_name => 'GETTING MEMBERS',
sofar => l_count + 1,
totalwork => l_table.COUNT + 1,
target_desc => 'GETTING MEMBERS' );
END IF;
EXCEPTION
WHEN others THEN
p_audit.failed_load
( p_auditkey => g_audit_key,
p_comments => SQLCODE || ' ' || SQLERRM );
RAISE;
END get_member_data;
Labels: dbms_application_info, development, instrumentation, plsql, sql, work
¶ 12/02/2007 10:04:00 PM 0 Comments Links to this post
Friday, November 30, 2007
Ling Chi II
or Death By a Thousand Cuts. I wrote about it before.
Today was one of the worst days I've had at this job.
I got home last night knowing I had to run and test a new line of business in our test environment. This was a fairly small subset so it should run fairly fast, two or three hours. Around 1:30 AM, it completed. I did my quick sanity checks and realized that it just wasn't working right.
Crap, I forgot to load our new rates. That has to be it.
So I reran the rates and then reran our process.
It's now 3:00 AM. I run my sanity check and it's exactly the same as the last one.
I quickly realize that some of the rates hadn't been updated like I assumed they had. This test was null and void. I went to sleep...mad.
I woke up around 8:15 with my son telling me something about Transformers. What? Oh, OK, I'm in his bed. I take him to school and get to work around 9:15.
I'm cranky because the other developer didn't do his part, but probably more mad at myself for not making sure they completed their piece.
I throw a piece of candy. Better.
Of course my colleague isn't in today, so I have to make sure the DBAs deploy this to our test environment.
Finally I get the process working and it finishes around noon. I send out a note to the business and QA letting them know that they can begin.
I get a call from the business around 2:30, it's not working the way they expected. One of our rates overlaps another causing invalid results to return.
Thanks for telling us that sooner. The day before deployment and were getting new requirements. Awesome!
I walk over to my boss' desk with my badge in hand, ready to quit, kind of, to tell him the news. He talks me off the ledge.
We then head over to their building to discuss and indeed it's something they didn't realize. OK, fair enough. Had they seemed to appreciate me/us a little bit more over the past eight months I probably wouldn't have been so mad. It was just one more thing though.
My boss decides how this will be remedied. I argue (gently) that this is not an application issue but a data issue. He agrees. We'll just put one job on hold and make the necessary changes to the [driving] data.
Two IT VPs, one business VP and the CIO need to sign off on two change requests, one for the driving data and one for the application.
We're slated to deploy tomorrow pending UAT signoff, though that shouldn't be a problem.
Ten to twenty percent of my time is spent writing code, the rest is paperwork. I believed I had mastered the administrative stuff only to get this requirement change at the last minute.
One more thing...just one more thing.
Ling Chi indeed.
Today was one of the worst days I've had at this job.
I got home last night knowing I had to run and test a new line of business in our test environment. This was a fairly small subset so it should run fairly fast, two or three hours. Around 1:30 AM, it completed. I did my quick sanity checks and realized that it just wasn't working right.
Crap, I forgot to load our new rates. That has to be it.
So I reran the rates and then reran our process.
It's now 3:00 AM. I run my sanity check and it's exactly the same as the last one.
I quickly realize that some of the rates hadn't been updated like I assumed they had. This test was null and void. I went to sleep...mad.
I woke up around 8:15 with my son telling me something about Transformers. What? Oh, OK, I'm in his bed. I take him to school and get to work around 9:15.
I'm cranky because the other developer didn't do his part, but probably more mad at myself for not making sure they completed their piece.
I throw a piece of candy. Better.
Of course my colleague isn't in today, so I have to make sure the DBAs deploy this to our test environment.
Finally I get the process working and it finishes around noon. I send out a note to the business and QA letting them know that they can begin.
I get a call from the business around 2:30, it's not working the way they expected. One of our rates overlaps another causing invalid results to return.
Thanks for telling us that sooner. The day before deployment and were getting new requirements. Awesome!
I walk over to my boss' desk with my badge in hand, ready to quit, kind of, to tell him the news. He talks me off the ledge.
We then head over to their building to discuss and indeed it's something they didn't realize. OK, fair enough. Had they seemed to appreciate me/us a little bit more over the past eight months I probably wouldn't have been so mad. It was just one more thing though.
My boss decides how this will be remedied. I argue (gently) that this is not an application issue but a data issue. He agrees. We'll just put one job on hold and make the necessary changes to the [driving] data.
Two IT VPs, one business VP and the CIO need to sign off on two change requests, one for the driving data and one for the application.
We're slated to deploy tomorrow pending UAT signoff, though that shouldn't be a problem.
Ten to twenty percent of my time is spent writing code, the rest is paperwork. I believed I had mastered the administrative stuff only to get this requirement change at the last minute.
One more thing...just one more thing.
Ling Chi indeed.
Labels: development, sql, work
¶ 11/30/2007 10:03:00 PM 0 Comments Links to this post
Wednesday, August 29, 2007
Fun with SQL
My group held it's first Peer Code Review today. It was my code (the code I've screwed up a couple of times). One of the requirements (I stuck to them this time, yeah for me!) was to limit the data we were pulling from another system to only the last 48 months. There were three different procedures that needed this date but I didn't want to break the existing automated job so I defaulted everything to:
p_date_from DATE DEFAULT ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), -48 )
I wanted the last full 48 months and this worked just fine.
This is a fairly long process which takes anywhere from 8 to 12 hours to run, depending on system resources. One of my colleagues asked what would happen in the event of a crash (on the last day of the month) and the required re-start (on the first day of the month). One set of data would have everything going back 49 months and the other 48 months which would create a lot of errors. A great catch.
Since we're in maintenance mode now (re-architecting the whole thing), we decided not to stop the deployment, we looked at the calendar to see when the first occurence was in which the 1st fell on a Sunday (the job is scheduled weekly on Sunday)...which now that I think about it, was probably wrong. We should have been looking for a Sunday in which was also the last day of the month. Anyway...
I like trying to answer questions with SQL. So I wanted to find the occurences on which Sunday was also the first day of the month (despite while writing this realizing that we should have been looking for the last day, again, digression). Here's my resultant query:
I'm not so sure it impressed my colleagues, but it certainly reinforced the notion that I'm a nerd.
¶ 8/29/2007 10:04:00 PM 2 Comments Links to this post
p_date_from DATE DEFAULT ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), -48 )
I wanted the last full 48 months and this worked just fine.
This is a fairly long process which takes anywhere from 8 to 12 hours to run, depending on system resources. One of my colleagues asked what would happen in the event of a crash (on the last day of the month) and the required re-start (on the first day of the month). One set of data would have everything going back 49 months and the other 48 months which would create a lot of errors. A great catch.
Since we're in maintenance mode now (re-architecting the whole thing), we decided not to stop the deployment, we looked at the calendar to see when the first occurence was in which the 1st fell on a Sunday (the job is scheduled weekly on Sunday)...which now that I think about it, was probably wrong. We should have been looking for a Sunday in which was also the last day of the month. Anyway...
I like trying to answer questions with SQL. So I wanted to find the occurences on which Sunday was also the first day of the month (despite while writing this realizing that we should have been looking for the last day, again, digression). Here's my resultant query:
SELECT
ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum )first_day_on_sunday
FROM
(
SELECT rownum myrownum
FROM dual
CONNECT BY LEVEL < 100
)
WHERE TO_CHAR( ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum ), 'D' ) = 1
AND TO_CHAR( ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum ), 'DD' ) = 1
/
FIRST_DAY_
----------
06/01/2008
02/01/2009
03/01/2009
11/01/2009
08/01/2010
05/01/2011
01/01/2012
04/01/2012
07/01/2012
09/01/2013
12/01/2013
06/01/2014
02/01/2015
03/01/2015
11/01/2015
I'm not so sure it impressed my colleagues, but it certainly reinforced the notion that I'm a nerd.
¶ 8/29/2007 10:04:00 PM 2 Comments Links to this post
Tuesday, August 21, 2007
SQL Puzzle, Sort of...
My group was once asked, "What's the first number that contains an 'A' in it?"
This has become a once a week exercise for fun.
I of course started counting from one to see where it might happen. Then I decided to use the magic of SQL to help me go through all the numbers.
On 10g:
Pretty cool stuff. ¶ 8/21/2007 02:11:00 AM 0 Comments Links to this post
This has become a once a week exercise for fun.
I of course started counting from one to see where it might happen. Then I decided to use the magic of SQL to help me go through all the numbers.
On 10g:
I found the format model in the documention and moved on from there. I think my original query was a bit more elegant, but this works.
SELECT num
FROM
(
SELECT
TO_CHAR(TO_DATE('01/01/4712 BC', 'MM/DD/YYYY BC') + (rownum - 1), 'JSP' ) num
FROM DUAL
CONNECT BY LEVEL < 1000
)
WHERE INSTR( num, 'A' ) > 0;
NUM
-------------------
ONE THOUSAND
Pretty cool stuff. ¶ 8/21/2007 02:11:00 AM 0 Comments Links to this post
