Looking forward to another great conference.
I’ll presenting again this year, this time doing two sessions:
What an Oracle Developer Ought to Know
Why Developers Need to Think Like DBAs, Why DBAs Need to Think Like Developers
Filling in missing functionality with Oracle Interval types
Oracle supports several methods for constructing Interval types including the NUMTODSINTERVAL and NUMTOYMINTERVAL functions.
As their names imply, these simply convert a number of some units (day,hour,minute, second or month,year respectively) into an interval type. There is however no built-in method for reversing the process.
The two functions below fill in this hole. I hope you find them helpful.
CREATE OR REPLACE FUNCTION dsintervaltonum( p_interval IN INTERVAL DAY TO SECOND, p_unit IN VARCHAR2 ) RETURN NUMBER DETERMINISTIC IS -- .///. -- (0 o) ---------------0000--(_)--0000--------------- -- -- Sean D. Stuber -- sean.stuber@gmail.com -- -- oooO Oooo --------------( )-----( )--------------- -- \ ( ) / -- \_) (_/ illegal_argument EXCEPTION; PRAGMA EXCEPTION_INIT(illegal_argument, -1760); BEGIN CASE UPPER(p_unit) WHEN 'SECOND' THEN RETURN EXTRACT(DAY FROM p_interval) * 86400 + EXTRACT(HOUR FROM p_interval) * 3600 + EXTRACT(MINUTE FROM p_interval) * 60 + EXTRACT(SECOND FROM p_interval); WHEN 'MINUTE' THEN RETURN EXTRACT(DAY FROM p_interval) * 1440 + EXTRACT(HOUR FROM p_interval) * 60 + EXTRACT(MINUTE FROM p_interval) + EXTRACT(SECOND FROM p_interval) / 60; WHEN 'HOUR' THEN RETURN EXTRACT(DAY FROM p_interval) * 24 + EXTRACT(HOUR FROM p_interval) + EXTRACT(MINUTE FROM p_interval) / 60 + EXTRACT(SECOND FROM p_interval) / 3600; WHEN 'DAY' THEN RETURN EXTRACT(DAY FROM p_interval) + EXTRACT(HOUR FROM p_interval) / 24 + EXTRACT(MINUTE FROM p_interval) / 1440 + EXTRACT(SECOND FROM p_interval) / 86400; ELSE RAISE illegal_argument; END CASE; END dsintervaltonum;
CREATE OR REPLACE FUNCTION ymintervaltonum( p_interval IN INTERVAL YEAR TO MONTH, p_unit IN VARCHAR2 ) RETURN NUMBER DETERMINISTIC IS -- .///. -- (0 o) ---------------0000--(_)--0000--------------- -- -- Sean D. Stuber -- sean.stuber@gmail.com -- -- oooO Oooo --------------( )-----( )--------------- -- \ ( ) / -- \_) (_/ illegal_argument EXCEPTION; PRAGMA EXCEPTION_INIT(illegal_argument, -1760); BEGIN CASE UPPER(p_unit) WHEN 'MONTH' THEN RETURN EXTRACT(MONTH FROM p_interval) + EXTRACT(YEAR FROM p_interval) * 12; WHEN 'YEAR' THEN RETURN EXTRACT(MONTH FROM p_interval) / 12 + EXTRACT(YEAR FROM p_interval); ELSE RAISE illegal_argument; END CASE; END ymintervaltonum;
Sample usage:
SELECT dsintervaltonum(dy, 'hour') hours,
ymintervaltonum(ym, 'month') months
FROM
(SELECT INTERVAL '7 2:07:38' DAY TO SECOND(9) dy,
TO_YMINTERVAL('3-1') ym
FROM DUAL);
5 years of fun helping others
Last week was the 5 year anniversary of answering my first question on Experts Exchange and on Monday of this week I answered my 6000th question. With coincidental milestones like that it seems like a good time to reflect on what I’ve accomplished since I started participating. In that time I’ve also published 13 articles, recorded a video tutorial and participated in two conferences at EE headquarters. In 2009 I was asked to be a Zone Advisor and most recently I was inducted into the inaugural class of “Most Value Experts.”
Along with the accolades I’ve had the opportunity to work with some really stellar people; both technically and socially. My friendship and rivalry with fellow volunteer slightwv was so well known we were invited to participate in a head-to-head “Throwdown” to see who could accumulate the most points in one week. I lost… by a lot, but that was expected (at least by me.) While I have a pretty good track record, I’ve never had his speed and tenacity; it was still fun though.
I’ve had the pleasure of working with some great experts, both online and in person, from around the world including members from England, Luxembourg, Australia, Germany and all over the US. I’ve also, unfortunately, felt sadness at the loss of a long time member and frequent participant who lost his life to cancer. I met him and his wife at my first EE conference in San Luis Obispo, CA. While I can’t say we grew close from that brief meeting, I was still shocked and saddened to hear of his passing and EE misses his passionate contributions.
Friends, family and coworkers ask why I do it. People pay good money for Oracle consultants and I here I help for free. I get an occasional t-shirt or other gift from EE but I haven’t been paid a dime for any of those 6000 answers. The simple answer is: it’s fun. I get a kick out of helping people figure out their problems, especially when they are thankful for my efforts. When someone responds with “Wow! That’s amazing! I didn’t know that was possible” or “Thank you, thank you, that just saved me weeks of work!” it makes it all worthwhile.
On a less magnanimous note, I’ll admit it. It’s also fun to show off my skills when I come up with something clever. 
A lot of the questions asked are pretty easy and don’t require much flexing of tech-muscle; but some of the questions are tricky and I’m proud of the solutions I came up with.
Sometimes it’s great to take a question that I don’t know how to answer just for the exercise of figuring it out. I do a lot of XML processing; but I answered a ton of questions on EE before my real job required much of it. When a project did come along though, I jumped right in with several options largely due to my exposure to multiple problems on EE.
In the last 5 years I’ve also started presenting at large Oracle conferences like Collaborate (IOUG) and KScope (ODTUG). All of the material I’ve presented so far is based on question trending I’ve seen on EE. When I see the same questions asked dozens or even hundreds of times, clearly it’s an area of instruction where developers and administrators need help. Thus my presentations were born. I’m currently getting my abstracts together for next year’s conferences and again what I’ve seen on EE will be driving the bulk of my submissions.
Experts Exchange gets a lot of grief from some people but I’ve had overwhelmingly successful and pleasant experiences with the staff and other members. I won’t say it’s 100% perfect. There are some things I’ve reported to HQ that I wish were different and I have butted heads with a couple people over the years; but those are a tiny, tiny minority of all my interactions.
My level of participation waxes and wanes as other responsibilities and interests occupy my time but I keep coming back to EE and plan to continue to do so for the foreseeable future. It’s too fun working with others, too rewarding helping people and too valuable as a resource to walk away.
If you’re ever in need of tech help, I recommend giving it a try. If your question is in Oracle, there is a good chance I’ll see it and, if I’m able, I’ll try to get you an answer.
KScope12 Day 1
First day was unintentionally APEX heavy for me. Not that that’s a bad thing, just sort of surprising to me in hindsight. Saw some really neat apps and ideas with HTML5 and dynamic actions. Also saw some new tools I’ll be checking out to make my own development more productive like aptana studio and apexlib.
Also went to the always great Steven Feuerstein and Tom Kyte sessions. Steven’s presentation “Cleaning up your APEX PL/SQL code” was pretty much what you’d expect. Lots of good pl/sql advice but with an eye toward apex. I’m not trying to trivialize it, but rather reiterate the theme: follow good encapsulation and de-duplication practices. Those practices apply when using apex or not, but given the structure of some of the APEX declarative framework it’s easy to implement functionality in such a way as to hamper maintainability.
Tom’s presentation “Five Things You Probably Didn’t Know About SQL” was very entertaining. Taking “select * from t” and tuning it. Looking at the impact of sqlnet compression and array fetching as well as filter impacts of 11g sql monitoring and histograms. In many ways it was mostly stuff I already knew but put together with excellent examples to help it sink in better. I definitely want to bring these lessons back to the office to help the developers there.
After dinner went to Cary Millsap’s keynote address on life, learning and happiness. Very good and while I always recommend his book for technical content, this non-technical presentation was a must see.
Then finished the day with more APEX at the APEX Open Night for more neat apps and tools.
Looking forward to tomorrow.
Timestamps for the year 12800?!
Just found this data yesterday, not sure when or how it got into the real table.
Ironically the column that is corrupt is the mod_date which could have told me when.
Note I can only display 2 digits of the year. If I try using yyyy format I’ll get an ORA-01877.
I think it’s interesting that even though the data is invalid, Oracle is still able to evaluate it correctly.
SQL> select count(*) from save_bad_timestamp_example where mod_date > TIMESTAMP '9999-12-31 23:59:59.999999999';
COUNT(*)
----------
15
SQL> select mod_date,dump(mod_date) from save_bad_timestamp_example;
MOD_DATE DUMP(MOD_DATE)
---------------------------- -------------------------------------
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
24-JUL-00 08.37.58.000000 PM Typ=180 Len=7: 228,100,7,24,21,38,59
15 rows selected.
SQL>
Nifty math trick with hierarchical query
Using the algorithm discovered by David Bailey, Peter Borwein, and Simon Plouffe, you can easily generate values of pi with a simple recursive query…
SQL> SELECT d, to_char(bbp, rpad('0.',55, '9')) bbp,
2 TO_CHAR(
3 SUM(bbp) OVER (ORDER BY d),
4 '9.' || RPAD('9', d - 1, '9')
5 ) pi
6 FROM (SELECT LEVEL d,
7 POWER(16, -(LEVEL - 1))
8 * ( 4 / (8 * (LEVEL - 1) + 1)
9 - 2 / (8 * (LEVEL - 1) + 4)
10 - 1 / (8 * (LEVEL - 1) + 5)
11 - 1 / (8 * (LEVEL - 1) + 6))
12 bbp
13 FROM DUAL
14 CONNECT BY LEVEL <= 38);
D BBP PI
--- ------------------------------------------------------------ ----------------------------------------
1 3.13333333333333333333333333333333333333000000000000000 3.
2 0.00808913308913308913308913308913308913308800000000000 3.1
3 0.00016492392411510058568882098293862999745400000000000 3.14
4 0.00000506722085385878489326765188834154351396000000000 3.142
5 0.00000018789290093772001666738508843772001666720000000 3.1416
6 0.00000000776775121517735681309382263783112139415700000 3.14159
7 0.00000000034479329305086272635969401468053759158800000 3.141593
8 0.00000000001609187715553700527429096273205488602519000 3.1415927
9 0.00000000000077957029540010122791277882390414359723220 3.14159265
10 0.00000000000003887115259909751224518898399125507993590 3.141592654
11 0.00000000000000198322539359813099744403743678780728487 3.1415926536
12 0.00000000000000010309712169788873230460615359913961954 3.14159265359
13 0.00000000000000000544347406057178666434000298497848856 3.141592653590
14 0.00000000000000000029121117943841783833432916474186319 3.1415926535898
15 0.00000000000000000001575498009770082070311785298679383 3.14159265358979
16 0.00000000000000000000086069263270039599262676755656370 3.141592653589793
17 0.00000000000000000000004742046744556226855262717169064 3.1415926535897932
18 0.00000000000000000000000263228669401317585860675420461 3.14159265358979324
19 0.00000000000000000000000014709093902773314327264205417 3.141592653589793238
20 0.00000000000000000000000000826833002827638605906177487 3.1415926535897932385
21 0.00000000000000000000000000046727110163528570518908320 3.14159265358979323846
22 0.00000000000000000000000000002653485901449924370022705 3.141592653589793238463
23 0.00000000000000000000000000000151345479607531561281484 3.1415926535897932384626
24 0.00000000000000000000000000000008666828560347770253967 3.14159265358979323846264
25 0.00000000000000000000000000000000498130681533106648748 3.141592653589793238462643
26 0.00000000000000000000000000000000028727019197415804591 3.1415926535897932384626434
27 0.00000000000000000000000000000000001661838244489253463 3.14159265358979323846264338
28 0.00000000000000000000000000000000000096413362728707559 3.141592653589793238462643383
29 0.00000000000000000000000000000000000005608493642017818 3.1415926535897932384626433833
30 0.00000000000000000000000000000000000000327065934758869 3.14159265358979323846264338328
31 0.00000000000000000000000000000000000000019117544868650 3.141592653589793238462643383280
32 0.00000000000000000000000000000000000000001119879586043 3.1415926535897932384626433832795
33 0.00000000000000000000000000000000000000000065734562356 3.14159265358979323846264338327950
34 0.00000000000000000000000000000000000000000003865856221 3.141592653589793238462643383279503
35 0.00000000000000000000000000000000000000000000227760336 3.1415926535897932384626433832795029
36 0.00000000000000000000000000000000000000000000013441452 3.14159265358979323846264338327950288
37 0.00000000000000000000000000000000000000000000000794528 3.141592653589793238462643383279502884
38 0.00000000000000000000000000000000000000000000000047036 3.1415926535897932384626433832795028842
38 rows selected.
SQL>
11gR2 on OEL6? yes and no
According to recent press release http://www.oracle.com/us/corporate/press/1563775 Yes- you can install 11gR2 on Oracle’s latest release of Linux.
However, I was not successful in my attempts. Upon further research it seems the certification of db and os is only for 11.2.0.3 which is only available through download from Oracle support. That’s fine for work where we have a CSI, but no go for my personal use in learning/testing things. So back to v5.8 for me and install went flawlessly.
