tag:blogger.com,1999:blog-88845844045760034872024-03-17T05:24:04.929-04:00ORACLENERDoraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.comBlogger789125tag:blogger.com,1999:blog-8884584404576003487.post-55606140342044421862016-08-23T16:10:00.001-04:002016-08-23T16:11:25.557-04:00Real World SQL and PL/SQL: Advice from the Experts<div style="text-align: left;">
</div>
<a href="https://www.amazon.com/Real-World-SQL-PL-Experts/dp/1259640973" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh579EZW-pGNHjzX3GpdjGzL9Fl0Gzfm2SSK3BfIOPxSRmBZzmmtHFJNt271fj4abvlL0ggYihSVyWf8end_peZLGcUPMVglhGNydnZTFvA7frOOkfRLPeMcHu3T8k0UAPSyCrAMPcL69A/s200/Screenshot+from+2016-08-22+21%253A12%253A16.png" width="162" /></a><br />
<br />
<i>Because my hero is <a href="https://twitter.com/carymillsap">Cary Millsap</a>, I'm going to <a href="http://carymillsap.blogspot.com/2008/07/christian-antogninis-new-book.html">do what he did</a> and publish my <strike>foreword</strike> Preface. All joking aside, I consider myself incredibly fortunate to have been included in this project. I learned...a lot, by simply trying to find the author's mistakes (and there were not many). There was a lot more work than I expected, as well. (Technical) Editing is lot easier than writing, to be sure.</i><br />
<br />
Brendan Tierney and Heli Helskyaho approached me in March 2015 about being an author on this book, along with Arup Nanda and Alex Nuijten. Soon after, we picked up Martin Widlake. To say that I was honored to be asked would be a gross understatement. Rather quickly though, I realized that I did not have the mental energy to devote to the project and didn’t want to put the other authors at risk. Still wanting to be part of the book, I suggested that I be the Technical Editor and they graciously accepted my new role.<br />
<br />
This is my first official role as Technical Editor, but I’ve been doing it for years through work; checking my work, checking others work, etc. Having a touch of Obsessive Compulsive Disorder (OCD) helps greatly.<br />
<br />
All testing was done with the pre-built <a href="http://www.oracle.com/technetwork/community/developer-vms-192663.html">Database App Development VM</a> provided by OTN/Oracle which made things easy. Configuration for testing was simple with the instructions provided in those chapters that required it.<br />
<br />
One of my biggest challenges was the multi-tenant architecture of Oracle 12c. I haven’t done DBA type work in a few years, so trying to figure out if I should be doing something in the root container (CDB) or the pluggable database (PDB) was fun. Other than that though, the instructions provided by the authors were pretty easy to follow.<br />
<br />
Design (data modeling, Edition Based Redefinition, VPD), Security (Redaction/Masking, Encryption/Hashing), Coding (Reg Ex, PL/SQL, SQL), Instrumentation, and “Reporting” or turning that raw data into actionable information (Data Mining, Oracle R, Predictive Queries). These topics are covered in detail throughout this book. Everything a developer would need to build an application from scratch.<br />
<br />
Probably my favorite part of this endeavor is that I was forced to do more than simply see if it works. Typically when reading a book, or blog entry, I’ll grab the technical solution and move on often skipping the Why, When, and Where. How, to me, is relatively easy. I read AskTom daily for many years, it was my way of taking a break without getting in trouble. At first, it was to see how particular solutions were solved, occasionally using it for my own problems. After a year or two, I wanted to understand the Why of doing it a certain way and would look for those responses where Tom provided insight into his approach.<br />
<br />
That’s what I got reviewing this book. I was allowed into their minds, to not only see How they solved technical problems, but Why. This is invaluable for developer’s and DBAs. Most of us can figure out How to solve specific technical issues, but to reach that next level we need to understand the Why, When and Where. This book provides that.<br />
<br /><div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com0tag:blogger.com,1999:blog-8884584404576003487.post-76372247288286278132015-07-09T15:04:00.001-04:002015-07-09T15:04:53.745-04:00Kscope15 - It's a Wrap, Part IIAnother fantastic Kscope in the can.
<br /><br />
This was my final year in an official capacity which was a lot more difficult to deal with than I had anticipated. Here's my record of service:
<br />
<ul>
<li>2010 (2011, Long Beach) - I was on the database abstract review committee run by Lewis Cunningham. I ended up volunteering to help put together the Sunday Symposium and with the help of <a href="https://twitter.com/ddelmoli">Dominic Delmolino</a>, <a href="https://twitter.com/carymillsap">Cary Millsap</a> and <a href="https://twitter.com/krisrice">Kris Rice</a>, I felt I did a pretty decent job.</li>
<li>2011 (2012, San Antonio) - Database track lead. I believe this is the year that Oracle started running the Sunday Symposiums. Kris again led the charge with some input from those other two from the year before, i.e. DevOps oriented</li>
<li>2012 (2013, New Orleans) Content co-chair for the traditional stuff (Database, APEX, ADF), Interview Monkey (<a href="https://www.youtube.com/watch?v=c7C2t-lp37M">Tom Kyte OMFG!</a>), OOW/ODTUG Coordinator, etc.</li>
<li>2013 (2014, Seattle) Content co-chair for the traditional stuff (Database, APEX, ADF), Interview Monkey, OOW/ODTUG Coordinator, etc.</li>
<li>2014 (2015, Hollywood, FL) Content co-chair for the traditional stuff (Database, APEX, ADF)</li>
</ul>
<br />
This has been a wonderful time for me both professionally and, more importantly to me, personally. Obviously I had a big voice in the direction of content. Also and maybe hard to believe, I actually presented for the first time. Slotted against Mr. Kyte. I reminded everyone of that too. Multiple times. It seemed to go well though. Only a <a href="https://twitter.com/Troy_Ligon/status/612973988990590976">few</a> made fun of me.
<br /><br />
I was constantly recruiting too. "Did you submit an abstract?" "No, why not?" and I'd go into my own personal diatribe (ignoring my own lack of presenting) into why they should present. <a href="https://twitter.com/epm_queen">Sarah Craynon Zumbrum</a> summed it up pretty well in a recent <a href="http://epmqueen.com/2015/07/01/on-abstracts/">article</a>.
<br /><br />
But it was the connections I made, the people I met, the stories I shared (#ampm, #cupcakeshirt, etc), and the friends that I made, that's what has had the most impact on me. Kscope is unique in that way because of it's size...at Collaborate or OOW, you'll be lucky to see someone more than once or twice, at Kscope you're running into everyone constantly.
<br /><br />
How could I forget? #tadasforkate! This year was even more special. For those that don't know, <a href="http://www.oraclenerd.com/search/label/kate">Katezilla</a> is my profoundly delayed but equally profoundly happy 10 y/o daughter. Just prior to the conference her physical therapist taught her "tada!" and Kate would hold her hands up high in the air and everyone around would yell, Tada! I got this crazy idea to ask others to do it and I would film it. Thirty or forty videos and hundreds of participants later...
<br/><br />
<iframe width="640" height="480" src="https://www.youtube.com/embed/ilrctDbFE_4" frameborder="0" allowfullscreen></iframe>
<br /><br />
So a gigantic thank you to everyone who made this possible for me.
<br />
Here's a short list of those that had a direct impact on me...
<ul>
<li>Lewis Cunningham - he asked me to be a reviewer which started all of this off.</li>
<li>Mike Riley - can't really say enough about Mike. After turning me away a long time ago (jerk), he was probably my biggest supporter over the years. (Remind me next year to you tell you about "The Hug."). Mike, and his family, are very dear to me.</li>
<li>Monty Latiolais (rhymes with Frito Lay I would tell myself) - How can you not love this guy?</li>
<li>Natalie Delemar - Co-chair for EPM/BI and then boss as Conference Chair.</li>
<li>Opal Alapat - Co-chair for EPM/BI and one of my favorite humans ever invented. I aspire to be more organized, assertive, and bad-ass like Opal.</li>
</ul>
That list is by no means exhaustive. It doesn't even include staff at YCC, like Crystal Walton, Lauren Prezby and everyone else there. Nor does it include the very long list of Very Special People I've met. I consider myself very fortunate and incredibly grateful.
<br /><br />
<b>What's the future hold?</b><br /> I have no idea. My people are in talks with <a href="https://twitter.com/helenjsanders">Helen J. Sander's</a> people to do one or more presentations next year, so there's that. Speaking of which...it's in Chicago. Abstract submissions start soon, I hope you plan on submitting. If you're not ready to submit, I hope you take try to take part in shaping the content by finding one of about 10 abstract review committees. Who knows where they may lead you?
<br /><br />
Finally, here's the <i>It's a Wrap</i> video from Kscope15 (see Helen's story there). Here's <a href="http://kscope16.com/">Kscope16's site</a>. Go sign up.
<br /><br />
<iframe width="640" height="360" src="https://www.youtube.com/embed/7ev7xwpoKkw" frameborder="0" allowfullscreen></iframe>
<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com2tag:blogger.com,1999:blog-8884584404576003487.post-79222955498890346262015-04-22T22:57:00.000-04:002015-04-22T22:58:59.012-04:00Is MERGE a bug? A few years back I pondered whether <a href="www.oraclenerd.com/2009/01/is-distinct-bug.html">DISTINCT was a bug</a>.
<br/><br/>
My premise was that if you are depending on DISTINCT to return a correct result set, something is seriously wrong with your table design. I was reminded of this again recently when I ran across Kent Graziano's post on <a href="http://kentgraziano.com/2013/08/25/better-data-modeling-are-you-making-these-3-beginner-mistakes-in-your-data-models">Better Data Modeling: Are you making these 3 beginner mistakes in your data models?</a>. Specifically:
<br/>
<blockquote>
Instead of that, you should be defining a natural, or business, key for every table in your system. A natural key is a an attribute or set of attributes (that occur naturally in the data set) required to uniquely identify a row in that table. In addition you should define a Unique Key Constraint on those attributes in the database. Then you can be sure you will not get any duplicate data into the tables.
<br/><br/>
CLARIFICATION: This point has caused a lot of questions and comments. To be clear, the mistake here is to have ONLY defined a surrogate key. i believe that even if using surrogate keys is the best solution for your design, you should ALSO define an alternate unique natural key.</blockquote>
So why MERGE?
<br/><br/>
I learned about the MERGE statement in 2008. During an interview, <a href="http://obieeone.com/">Frank Davis</a> asked me about when I would use it. I didn't even know what it was (and admitted that) but I went home that night and...wait...I think he asked me about <a href="http://www.oraclenerd.com/2008/05/multi-table-inserts.html">multi table inserts</a>. Whatever, credit is still going to Mr. Davis. Where was I? OK, so I had been working with Oracle for about 6 years at that point and I didn't know about it. My initial reaction was to use it everywhere (not really)! You know, shiny object and all. Look! Squirrel!
<br/><br/>
Why am I considering MERGE a bug? Let me be more specific. I was working with a couple of tables and had not written the API for them yet and a developer was writing some PL/SQL to update the records from APEX. In his loop he had a MERGE. I realized at that moment there was 1, no surrogate key and 2, no natural key defined (which ties in with Kent's comments up above). Upon realizing the developer was doing this, I knew immediately what the problem was (besides not using a PL/SQL API to nicely encapsulate the business logic). The table was poorly designed.
<br/><br/>
Easy fix. Update the table with a surrogate key and define a natural key. I was thankful for the reminder, I hadn't added the unique constraint yet. Of course had I written the API already I probably would have noticed the design error, either way, a win for design.
<br/><br/>
Now, there are perfectly good occasions to use the MERGE statement. Most of those, <a href="http://www.oraclenerd.com/2013/04/caveats.html">to me anyway</a>, relate to legacy systems where you don't have the ability to change the underlying table structures (or it's just cost prohibitive) or ETL, where you want to load/update a dimension table in your data warehouse.
<br/><br/>
Noons, how's that? First time out in 10 months. Thanks for the push.
<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com10tag:blogger.com,1999:blog-8884584404576003487.post-40911880323428746002014-06-04T18:13:00.000-04:002014-06-04T18:13:26.302-04:00Fun with SQL - Silver Pockets Full<a href="http://www.snopes.com/inboxer/trivia/fivedays.asp">Silver Pockets Full</a>, send this message to your friends and in four days the money will surprise you. If you don't, well, a pox on your house. Or something like that. I didn't know what it was, I just saw this in my FB feed:
<br /><br />
<img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgh7xQ76kPdE4HBSD5H08ba2PxdmzA4bF0QekhJSgV3MKoxEag2BvgzWEtJ_SjX_qgH-HWLbDPBNcqv22vufOWGxYnHusxK7t8jOlKKLnM2kM7vmqG5cJDsAaLfgGMP-BM0tXLiCwk1j0A/s800/10256953_1482404708655173_9115989789244265213_n.jpg" style="padding-left: 25px;" />
<br /><br />
Back in <a href="http://www.oraclenerd.com/2013/11/fun-with-sql-my-birthday.html">November</a>, I checked to see the frequency of having incremental numbers in the date, like 11/12/13 (my birthday) and 12/13/14 (kate's birthday). I don't want to hear how the rest of the world does their dates either, I know (I now write my dates like YYYY/MM/DD on everything, just so you know, that way I can sort it...or something).
<br /><br />
Anyway, SQL to test out the claim of once every 823 years. Yay SQL.
<br /><br />
OK, I'm not going to go into the steps necessary because I'm lazy (and I'm just lucky to be writing here), so here it is:<pre class="code">select *
from
(
select
to_char( d, 'yyyymm' ) year_month,
count( case
when to_char( d, 'fmDay' ) = 'Saturday' then 1
else null
end ) sats,
count( case
when to_char( d, 'fmDay' ) = 'Sunday' then 1
else null
end ) suns,
count( case
when to_char( d, 'fmDay' ) = 'Friday' then 1
else null
end ) fris
from
(
select to_date( 20131231, 'yyyymmdd' ) + rownum d
from dual
connect by level <= 50000
)
group by
to_char( d, 'yyyymm' )
)
where fris = 5
and sats = 5
and suns = 5</pre>So over the next 50,000 days, this happens 138 times. I'm fairly certain that doesn't rise to the once every 823 years claim. But it's cool, maybe.<pre class="code">YEAR_MONTH SATS SUNS FRIS
---------- ---------- ---------- ----------
201408 5 5 5
201505 5 5 5
201601 5 5 5
201607 5 5 5
201712 5 5 5
128 more occurrences...
214607 5 5 5
214712 5 5 5
214803 5 5 5
214908 5 5 5
215005 5 5 5
138 rows selected </pre>I'm not the only dork that does this either, here's one in <a href="http://perlbuzz.com/2013/03/debunking-the-five-weekends-every-823-years-myth-with-perl.html">perl</a>. I'm sure there are others, but again, I'm lazy.<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com4tag:blogger.com,1999:blog-8884584404576003487.post-77910892157198946712014-04-10T22:44:00.002-04:002014-04-10T22:44:31.650-04:00The Riley Family, Part III<img style="align: center;" src="https://lh5.googleusercontent.com/-x20BjnHQj-8/U0dMGlCfYlI/AAAAAAACcx4/YPx_UHyDwKM/s800/1509531_566260413449373_759515834_o.jpg" />
<br />
<br />
That's Mike and Lisa, hanging out at the hospital. Mike's in his awesome cookie monster pajamas and robe...must be nice, right? Oh wait, it's not. You probably remember why he's there, Stage 3 cancer. The joys.
<br />
<br />
In October, we helped to send the entire family to <a href="http://www.oraclenerd.com/2013/11/the-riley-family-part-ii.html">Game 5 of the World Series</a> (Cards lost, thanks Red Sox for ruining their night).
<br />
<br />
In November I started a GoFundMe <a href="http://www.gofundme.com/fmcuta">campaign</a>, to date, with your help, we've raised $10,999. We've paid over 9 thousand dollars to the Riley family (another check to be cut shortly).
<br />
<br />
In December, Mike had surgery. Details can be found <a href="http://www.odtug.com/p/bl/et/blogid=1&blogaid=315">here</a>. Shorter: things went fairly well, then they didn't. Mike spent 22 days in the hospital and lost 40 lbs. He missed Christmas and New Years at home with his family. But, as I've learned over the last 6 months, the Riley family really knows how to take things in stride.
<br />
<br />
About 6 weeks ago Mike started round 2 of chemo, he's halfway through that one now. He complains (daily, ugh) about numbness, dizziness, feeling cold (he lives in St. Louis, are you sure it's not the weather?), and priapism (that's a lie...I hope).
<br />
<br />
Mike being Mike though, barely a complaint (I'll let you figure out where I'm telling a lie).
<br />
<br />
Four weeks ago, a chilly (65) Saturday night, Mike and Lisa call. "Hey, I've got some news for you."
<br />
<br />
"Sweet," I think to myself. Gotta be good news.
<br />
<br />
"Lisa was just diagnosed with breast cancer."
<br />
<br />
WTF?
<br />
<br />
ARE YOU KIDDING ME? (Given Mike's gallows humor, it's possible).
<br />
<br />
"Nope. Stage 1. Surgery on April 2nd."
<br />
<br />
FFS
<br />
<br />
(Surgery was last week. It went well. No news on that front yet.)
<br />
<br />
Talking to them two of them that evening you would have no idea they BOTH have cancer. Actually, one of my favorite stories of the year...the hashtag for Riley Family campaign was #fmcuta. Fuck Mike's Cancer (up the ass). I thought that was hilarious, but I didn't think the Riley's would appreciate it. They did. They loved it. I still remember Lisa's laugh when I first suggested it. They've dropped the latest bad news and Lisa is like, "Oh, wait until you hear this. I have a hashtag for you."
<br />
<br />
"What is it?" (I'm thinking something very...conservative. Not sure why, I should know better by now).
<br />
<br />
#tna
<br />
<br />
I think about that for about .06 seconds. Holy shit! Did you just say tna? Like "tits and ass?"
<br />
<br />
(sounds of Lisa howling in the background).
<br />
<br />
Awesome. See what I mean? Handling it in stride.
<br />
<br />
"We're going to need a bigger boat." All I can think about now is, "what can we do now?"
<br />
<br />
First, I raised the campaign goal to 50k. This might be ambitious, that's OK, cancer treatments are expensive enough for one person, and 10K (the original amount) was on the low side. So...50K.
<br />
<br />
Second, <a href="http://spendolini.blogspot.com/">Scott Spendolini</a> created a very cool APEX app, ostensibly called the Riley Support Group (website? gah). It's a calendar/scheduling app that allows friends and family coordinate things like meals, young human (children) care and other things that most of us probably take for granted. Pretty cool stuff. For instance, <a href="http://evdbt.com/">Tim Gorman</a> provides pizza on Monday nights (Dinner from pizza hut...1 - large hand-tossed cheese lovers, 1 - large thin-crispy pepperoni, 1 - 4xpepperoni rolls, 1 - cheesesticks).
<br />
<br />
Third. There is no third.
<br />
<br />
So many of you have donated your hard earned cash to the Riley family, they are incredibly humbled by, and grateful for, everyone's generosity. They aren't out of the woods yet. Donate more. Please. If you can't donate, see if there's something you can help out with (hit me up for details, Tim lives in CO, he's not really close). If you can't do either of those things, send them your prayers or your good thoughts. Any and all help will be greatly appreciated.
<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com0tag:blogger.com,1999:blog-8884584404576003487.post-4738849343596800752013-11-12T23:13:00.001-05:002013-11-12T23:54:59.401-05:00The Riley Family, Part IIYou didn't miss <a href="http://www.odtug.com/p/bl/et/blogid=1&blogaid=290">Part I</a>, at least not here you didn't.
<br /><br />
Many of you know Mike Riley. If you don't, here's a little history. He's the past president of ODTUG (for like 37 years or something) and for the last two years, he's served as Conference Chair for Kscope. Yeah, that doesn't really follow, but you know I'm a bit...scattered.
<br /><br />
Did you read the link above? OK, well, here's the skinny. Mike has rectal cancer. Stage III. If it weren't for the stupid cancer part, the jokes would abound. Oh wait, they do anyway. Mike was diagnosed shortly after #kscope13, right around his 50th birthday (Happy Birthday Mike, Love, Cancer!). Ugh. (I want to say, "are you shittin' me?" see what I mean about the jokes? I can't help myself, I'm 14). Needless to say, cancer isn't really a joke. We all know someone affected by it. It is...well, it's not fun.
<br /><br />
Go read his post if you haven't already. I'm going to give my version of that story. I'll wait...
<br /><br />
So, Sunday morning, Game 3 of the World Series went to the Cardinals in a very bizarre way. I was watching highlights that morning as I had missed the end of the game (doesn't everyone know that I'm old and can't stay up that late to watch baseball?). Highlights. Mike lives in St. Louis. He's a Cardinal's fan. Wouldn't it be cool if he and his family could go to the game (mostly just his family, I don't like Mike that much). So I make some phone calls to see what people think of my idea. My idea is met with resistance. OK, I'll skip the people. Let's call Lisa (Mike's wife).
<br /><br />
Apparently Sunday's are technology free days in the Riley household, no response. I go for a bike ride, but I take my phone, just in case Lisa calls me back. After the halfway point, my phone rings, I jump off the bike to answer.
<br /><br />
So I talked to Lisa about my idea, can Mike handle the chaos of a World Series game?
<br /><br />
We hang up and she goes to work. BTW, I asked her to keep my name out of it, but she didn't. We'll have words about that in the future.
<br /><br />
She calls back (I think, it may have been over text, 2 weeks is an eternity to me). "He doesn't think he can do it."
<br /><br />
So I call Mike directly (Lisa had already spoiled the surprise.)
<br /><br />
"What about Box seats? You know, where the people with top hats and monocles sit? Away from the rift-raft, much more comfortable and free food and beer."
<br /><br />
Backstory. Mike had finished his first round of chemo less than a week before Sunday. To make things worse, he decided it was a good time to throw out his back. He wasn't in the best of shape.
<br /><br />
Mike said he thought he could do it.
<br /><br />
OK, nay-sayers aside, let's see what we can do. I emailed approximately 50 people, mostly ODTUG people; board members, content leads, anyone I had in my address book. "Hey, wouldn't it be great to send Mike and his family to Game 5 of the World Series? We need to do this quick, tickets will probably double in price tonight especially if the Cardinals win." (that would mean Game 5 would be a clincher for the Cardinals, at home, muy expensive).
<br /><br />
Within about 20 minutes, a couple of people pledged $600.
<br /><br />
Holy shit!
<br /><br />
At the prices I had seen, I was hoping to get between $50 and $100 from 50 people, <i>hoping</i>. I had $600 already. Game starts. Now it's up to $1100 in pledges. Holy shit, Part II. This might just be possible. Another 30 minutes and were about an hour into Game 4. Ticket prices have already gone up by $250 a ticket. Given that maybe 4 people have responded and I have $1600 in pledges, I pull the trigger. I bought 4 box seat tickets for the Riley family. (I had to have a couple of beers because I was about to drop a significant chunk of change without actual cash in hand, I could be out a lot of money, liquid courage is awesome).
<br /><br />
Tickets sent to the Riley family. Pretty good feeling.
<br /><br />
Like I said, I was confident, but I was scared. Before the end of the night though, there was over $5K pledged to get Mike and family to Game 5. Holy shit, Part III.
<br /><br />
By midday Monday, pledges were well over $7K. I'll refer you back to Mike's <a href="http://www.odtug.com/p/bl/et/blogid=1&blogaid=290">post</a> for more details. Shorter: jerseys for the family and a limo to the game.
<br /><br />
Here's the breakdown: 35 people pledged, and paid, $8,080. Holy shit, Part IV. Average donation was $230.86. Median was $200. Low was $30 and high was $1000. Six people gave $500 or more. Nineteen people gave $200 or more. The list is a veritable Who's Who in the Oracle community.
<br /><br />
Tickets + Jerseys + Limo = $6027.76
<br /><br />
Riley family memory = Priceless.
<br /><br />
So, what happened to the rest? Well, they have bills. Lots of bills. With the remainder, $2052.24, we paid off some hospital bills of $1220.63. There is currently $831.61 that will be sent shortly. It doesn't stop there though. Cancer treatment is effing expensive. Mike has surgery in December. He'll be on bed rest for some time. His bed is 17 years old. He needs a new one. After that, more chemo and more bills.
<br /><br />
"Hey Chet, I'd love to help the Riley family out, can I give you my money for them?"
<br /><br />
Yes, absolutely. Help me help them. I started a <a href="http://www.gofundme.com/fmcuta">GoFundMe</a> campaign. Goal is $10K. Any and all donations are welcome. Gifts include a thank you card from the Riley family and the knowledge that you helped out a fellow Oracle (nerd, definitely a nerd) in need. You can find the campaign <a href="http://www.gofundme.com/fmcuta">here</a>.
<br /><br />
If you can't donate money, I've also created a hashtag so that we can show support for Mike and his family. It's #fmcuta (I'll let you figure out what it means). Words of encouragement are welcome and appreciated.
<br /><br />
Thank you to the 35 who have already so generously given. Thank you to the rest of you who will donate or send out (rude) tweets.
<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com1tag:blogger.com,1999:blog-8884584404576003487.post-1429805372327150922013-11-06T11:26:00.000-05:002013-11-06T11:26:04.638-05:00Fun with SQL - My BirthdayThis year is kind of fun, my birthday is on November 12th (next Tuesday, if you want to send gifts). That means it will fall on 11/12/13. Even better perhaps, <a href="http://www.oraclenerd.com/search/label/kate">katezilla's</a> birthday is December 13th. 12/13/14. What does this have to do with SQL?
<br /><br />
Someone mentioned to me last night that this wouldn't happen again for 990 years. I was thinking, "wow, I'm super special now (along with the other 1/365 * 6 billion people)!" Or am I? I had to do the math. Since date math is hard, and math is hard, and I'm good at neither, SQL to the rescue.
<br /><br />
<pre class="code">select
to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
sysdate + ( rownum - 1 ) actual
from dual
connect by level <= 100000</pre>
(In case you were wondering, 100,000 days is just shy of 274 years. 273.972602739726027397260273972602739726 to be more precise.)
<br /><br />
That query gives me this:
<pre class="code">MONTH_OF DAY_OF YEAR_OF ACTUAL
-------- ------ ------- ----------
11 06 13 2013/11/06
11 07 13 2013/11/07
11 08 13 2013/11/08
11 09 13 2013/11/09
11 10 13 2013/11/10
11 11 13 2013/11/11
...</pre>
So how can I figure out where DAY_OF is equal to MONTH_OF + 1 and YEAR_OF is equal to DAY_OF + 1? In my head, I thought it would be far more complicated, but it's not.
<pre class="code">select *
from
(
select
to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
sysdate + ( rownum - 1 ) actual
from dual
connect by level <= 100000
)
where <b>month_of + 1 = day_of
and day_of + 1 = year_of</b>
order by actual asc</pre>
Which gives me:
<pre class="code">MONTH_OF DAY_OF YEAR_OF ACTUAL
-------- ------ ------- ----------
11 12 13 2013/11/12
12 13 14 2014/12/13
01 02 03 2103/01/02
02 03 04 2104/02/03
03 04 05 2105/03/04
04 05 06 2106/04/05
05 06 07 2107/05/06
...</pre>
OK, so it looks closer to 100 years, not 990. Let's subtract. LAG to the rescue.
<pre class="code">select
actual,
lag( actual, 1 ) over ( partition by 1 order by 2 ) previous_actual,
actual - ( lag( actual, 1 ) over ( partition by 1 order by 2 ) ) time_between
from
(
select
to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
sysdate + ( rownum - 1 ) actual
from dual
connect by level <= 100000
)
where month_of + 1 = day_of
and day_of + 1 = year_of
order by actual asc</pre>
Which gives me:
<pre class="code">ACTUAL PREVIOUS_ACTUAL TIME_BETWEEN
---------- --------------- ------------
2013/11/12
2014/12/13 396
2103/01/02 32161
2104/02/03 397
2105/03/04 395
2106/04/05 397
2107/05/06 396
2108/06/07 398
2109/07/08 396
2110/08/09 397
2111/09/10 397
2112/10/11 397
2113/11/12 397
2114/12/13 396
2203/01/02 32161</pre>
So, it looks like every 88 years it occurs and is followed by 11 consecutive years of matching numbers. The next time 11/12/13 and 12/13/14 will appear is in 2113 and 2114. Yay for SQL!<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com6tag:blogger.com,1999:blog-8884584404576003487.post-23445566009143046902013-10-29T14:36:00.000-04:002013-10-29T14:37:06.728-04:00Why I'm voting for Danny Bryant and You Should TooI'm talking about the <a href="http://www.odtug.com/odtug-board">ODTUG Board of Directors</a>.
<br />
<br />
This.
<br />
<br />
<img src="https://lh3.googleusercontent.com/-Swnem86AcYc/T_Ib_U2_1yI/AAAAAAAB39M/pDHQFEw1hIM/s640/AwcDUvfCIAIdds6.jpg" style="padding-left: 10px;" />
<br />
<br />
That's really all you need isn't it?
<br />
<br />
Fine.
<br />
<br />
Today wraps up the voting period for the ODTUG Board of Directors. If you're asking me what ODTUG is, stop reading now. If you are a member of ODTUG, then please give me a few minutes to pontificate (that's a word I heard <a href="http://thatjeffsmith.com">Jeff Smith</a> use once, hopefully it makes sense here).
<br />
<br />
Your favorite Oracle conference, <a href="http://kscope14.com/">KScope</a>, is largely successful based on the efforts of the Board, along with the expert advice of the YCC group. In addition, if you think ODTUG should "do more with Essbase" or "charge more for memberships" these decisions are made and carried out by the board.
<br />
<br />
So if you like being in ODTUG, and you want to help it get better and grow, and be as awesome as possible, you only need to do one thing today. <a href="http://www.odtug.com/2014_election">Go vote</a>. Midnight tonight (10/29) is the deadline. <a href="http://www.youtube.com/watch?v=Ra70O9nps6E">Do it</a>.
<br />
<br />
You get to vote for several people. I suggest you read their <a href="http://www.odtug.com/2014_election">bios</a>. I'll save you the time for at least one vote, and that's for Danny Bryant.
<br />
<br />
Besides that awesome photo (#kscope12 in San Antonio) up above, here are several more reasons.
<br />
<br />
1. He's into everything. OBIEE. EBS. Essbase. SQL Developer. Database. Not very many people have their hands in everything, he does. He will be able to represent the entire spectrum of ODTUG members.
<br />
2. He's a fantastic human being. It's not just because he takes pictures of himself wearing ORACLENERD gear everywhere (doesn't hurt though), he's just, awesome.
<br />
3. <a href="https://www.facebook.com/photo.php?fbid=10200297574152537&set=pcb.165633503632563&type=1&theater">This (Part II)</a>
<br />
4. He also always answers the phone, tweets, and emails I send him. He might be sick, or he might just be that responsive. The ODTUG Board member responsibilities will fit nicely on his shoulders I believe.
<br />
<br />
So <a href="http://www.odtug.com/2014_election">go vote</a>. Now.
<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com1tag:blogger.com,1999:blog-8884584404576003487.post-24576783463820293372013-10-21T20:50:00.000-04:002013-10-21T20:50:44.188-04:00Excel VS BI in Financial Analysis: why the fight was over before it started<i>by Victor Fagundo</i>
<br/><br/>
The argument over why Businesses should abandon Excel in favor of more structured tools has been raging for as long as I have had more than a casual exposure to Oracle products. From the standpoint of an IT user Excel appears to be a simplistic, flat-file-based, error-prone tool that careless people use, despite its obvious flaws. Petabytes of duplicative Excel spreadsheets clog network drives across the globe; we as IT users know it, and it drives us crazy. Why, oh why, can’t these analysts, project managers, and accountants not grasp the elegant beauty of a centralized database solution that ensures data integrity, security, and has the chops to handle gobs of data, and abandon their silly Excel sheets?<br />
<br />
I’ll tell you why: Excel is better. Excel the most flexible and feature-rich tool for organizing and analyzing data. Ever. Period.<br />
<br />
For the past few years I have lived in a hybrid Finance/IT role, and in coming from IT, I was shocked at how much Excel was used, for everything. But after working with Excel on a daily basis for several years, I am a convert. An adept Excel user can out-develop any tool ( BI, Apex, Hyperion, Crystal Reports ) handily. (when dataset size is not an issue). Microsoft has done too much work on Excel, made it too extendable, too intuitive, built in so much, that no structured tool like BI, APEX, SAP, Hyperion will EVER catch up to its usability/flexibility.<br />
<br />
Take this real-world example that came across my desk a few months ago: for a retail chain define a by-week, by-unit sales target, and create a report that compares actual sales to this target. Oh, and the weekly sales targets get adjusted each quarter based on current financial outlook.<br />
<br />
How quickly could you turn around a DW/BI solution to this problem? What would it involve?<br />
• Create table to house targets<br />
• Create ETL process to load new targets<br />
• Define BMM/Presentation Layers to expose targets<br />
• Develop / test / publish report.<br />
<br />
A day? Maybe? If one person handled all steps (unlikely, since the DB layers and RPD layers are probably handled by different people.)<br />
<br />
I can tell you how long it took me in Excel: 3 hours (OBIEE driven data-dump, married with target sheet supplied to me). I love OBIEE, but Excel was still miles faster/more efficient for this task. And I could regurgitate 6 other examples like this one off at a moment’s notice.<br />
<br />
Case in point: 95% of the data that C-level executives use to make strategic decisions is Excel based.<br />
<br />
If you’ve ever sat in on a presentation to a CEO or other C-level executive at any medium to large sized company, you know that people are not bringing up dashboards, or any other applications. They are presenting PowerPoints with a few (less than 7) carefully massaged facts on them. If you trace the source of these numbers back down the rabbit hole, your first stop is always Excel. Within these Excel workbooks you will find “guesses” and “plugs” that fill gaps in solid data, to arrive at an actionable bit of information. It’s these “guesses” and “plugs” that are very hard to code for in an environment like OBIEE (or any other application). Can it be done? Yes, of course, with gobs of time and money. And during the fitful and tense development, the creditably of the application is going to take major hits.<br />
<br />
Given the above, the usefulness of OBIEE might seem bleak. But I strongly feel that applications such as OBIEE do have a proper place in the upper organizational layers of modern business: Facilitating the Tactical business layers, and providing data-dumps to the Strategic Business layers.<br />
<br />
Since this post is mainly about Excel, I will focus on how OBIEE can support the analyses that are inevitably going to be done in Excel.<br />
<br />
Data Formatting, Data Formatting, FORMATTING!! I can’t stress this enough. For an analyst, having to re-format numbers that come out of an export so that you can properly display them or drive calcs off them in Excel is infuriating, and wasteful. My favorite examples: in a BI environment I worked with percentages were exported as TEXT, so while they looked fine in the application, as soon as you exported them to Excel and built calcs off them, your answer was overstated by a factor of 100 (Excel understood “75%” to be the number 75 with a text character appended, not the number 0.75).<br />
<br />
Ask your users how they would like to SEE a fact in Excel: with decimals or not? With commas or not? Ensure that when exported to Excel, facts and attributes function correctly.<br />
<br />
“Pull” refreshes of information sources in Excel. In the finance world, most Excel workbooks are low to medium complexity financial models, based off a data-dump from a reporting system. When the user wants to refresh the model, they refresh the data-dump, and the Excel calculations do the rest. OBIEE currently forces a user to “push” a new data-dump by manually running/exporting from OBIEE and then pasting the data into the data-dump tab in the workbook. What an Excel user really desires is to have a data dump that can be refreshed automatedly, using values that exist on other parts of the workbook to define filters of data-dump. Then all the user needs to do is trigger a “pull” and everything else is automated. Currently OBIEE has no solution to this problem that is elegant enough for the common Excel user. (Smartview must have its filters defined explicitly in the Smartview UI each time an analysis is pulled.)<br />
<br />
The important part to take away from these 2 suggestions, and this entire post, is that to maximize the audience of OBIEE, we must acknowledge that Excel is the preferred tool of the Finance department, due to its flexibility, and support friendly exports to Excel as a best practice. We must also understand that accounting for this flexibility in OBIEE is daunting, and probably not the best use of the tool. If your users are asking for a highly complex attribute or fact, that is fraught with exceptions and estimations, chances are they are going to be much happier if what you give them is reliable information in a data-dump form, and allow them to handle the exceptions and estimations in Excel.<br />
<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com0tag:blogger.com,1999:blog-8884584404576003487.post-3658508258594619112013-09-16T22:41:00.000-04:002013-09-16T22:42:23.887-04:00#OOW13I'm going to be busy.
<br /><br />
Here's my list of events:
<br /><br />
<ul>
<li>Saturday
<ul>
<li>I arrive in San Francisco on Saturday around 1 PM. If you're arriving at around the same time, let me know, we can share a cab into the city.</li>
<li>Beer. After arriving I plan on finding a very cold <a href="http://russianriverbrewing.com/brews/pliny-the-elder/">Pliny the Elder</a>. Or three.</li>
<li>ODTUG Dinner. I'm crashing this one. It's Board members only to my knowledge and until someone says I cannot go (especially if fueled by more than one Pliny the Elder), I'm going.</li>
</ul>
</li>
<li>Sunday
<ul>
<li><a href="https://www.facebook.com/events/202355076600435/">Open World Bridge Run</a>. Not sure if I can make it, but I'm going to try. I'm presenting at 10:15 so it will be a tough decision.</li>
<li>10:30 to 11:30. <a href="https://oracleus.activeevents.com/2013/connect/sessionDetail.ww?SESSION_ID=10009">Thinking Clearly About Performance</a>. Somehow I managed to con <a href="https://twitter.com/CaryMillsap">Cary Millsap</a> into a duet of sorts. I have him convinced it is the other way around. Either way, it should be fun (I am not nervous!).</li>
<li>2:15 to 4:30, Software Development in the Oracle Ecosystem, <a href="https://oracleus.activeevents.com/2013/connect/sessionDetail.ww?SESSION_ID=9932">Part I</a> and <a href="https://oracleus.activeevents.com/2013/connect/sessionDetail.ww?SESSION_ID=9948">Part II</a>. I'm moderating the aforementioned Mr. Millsap, <a href="https://twitter.com/stenvesterli">Sten Vesterli</a>, <a href="https://twitter.com/myfear">Markus Eisele</a> and <a href="http://www.linkedin.com/pub/jerry-brenner/0/40/b86">Jerry Brenner</a> (My first boss was scheduled to speak as well, but he had a last minute change of plans, jerk).</li>
<li>Oracle ACE Dinner. Evening.</li>
<li>Post Oracle ACE Dinner drinks...wherever the night takes me.</li>
</ul>
</li>
<li>Monday
<ul>
<li>Oracle OpenWorld - San Francisco Bay Swim - Part II, 7:30 AM. We had almost 20 last year, 33 have signed up (on the page anyway) this year. Come along. Cool t-shirts too, sponsored by <a href="http://www.oracle.com/technetwork/index.html">Oracle Technology Network</a> and designed by <a href="http://www.linkedin.com/pub/lauren-prezby/14/246/59b">Lauren Prezby</a>.
<br /><br />
<a href="http://4.bp.blogspot.com/-D3h-0owmqIM/Uje6qB2VCTI/AAAAAAACPK8/7jlHZo-FYg8/s1600/swim_shirts.png" imageanchor="1" ><img border="0" src="http://4.bp.blogspot.com/-D3h-0owmqIM/Uje6qB2VCTI/AAAAAAACPK8/7jlHZo-FYg8/s320/swim_shirts.png" /></a>
<br /><br />
Let's not forget the swim caps! Sponsored by the encouragable <a href="https://twitter.com/brost">Bjoern Rost</a> of The <a href="http://portrix-systems.de/">portrix group</a> (he's like me, afraid of capital letters) (designed by Lauren Prezby).
<br /><br />
<a href="http://4.bp.blogspot.com/-szh1Py4wVbg/Uje85-YH8zI/AAAAAAACPLs/9G3fqjGXMXw/s1600/1238833_10200164954677133_1587862987_n.jpg" imageanchor="1" ><img border="0" src="http://4.bp.blogspot.com/-szh1Py4wVbg/Uje85-YH8zI/AAAAAAACPLs/9G3fqjGXMXw/s320/1238833_10200164954677133_1587862987_n.jpg" /></a>
</li>
<li><a href="http://www.kylehailey.com/oaktable-world/">Oaktable World</a>. You'll most likely catch me here after the swim and before the...</li>
<li><a href="https://www.facebook.com/events/175411045980422/">Wear Your ORACLENERD Gear Day</a>, 3 PM to 4:30 PM. We'll be taking a group photo around 4:30 PM. If you can't make it for that, come by when you can and get a picture with me. You know I like that sh...stuff. You don't have a shirt/hat/sticker/random-item? OTN Lounge is giving away one hundred cool red t-shirts (Lauren Prezby, again).
<br /><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKym5byeY1OC-7-LH-qYsUY82ooW3w8khm2OUnHPoTWxJsS22lGn62tTo6x8A0mGyfwSszNZNVeJmmJfxhNKkXAvnj2RuxZjUs6H8OnVcofJ09QakeZtXhIsURQUDN0VY63ScLuwDnF7A/s1600/1267567_10200173693295593_1519911008_o.jpg" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKym5byeY1OC-7-LH-qYsUY82ooW3w8khm2OUnHPoTWxJsS22lGn62tTo6x8A0mGyfwSszNZNVeJmmJfxhNKkXAvnj2RuxZjUs6H8OnVcofJ09QakeZtXhIsURQUDN0VY63ScLuwDnF7A/s320/1267567_10200173693295593_1519911008_o.jpg" /></a>
<br /><br />
This picture is coinciding with the APEX Developer Challenge which goes from 3 - 7:30 that afternoon/evening. I might even give it a go (fueled, hopefully, by Pliny the Elder).
</li>
</ul>
<li>Tuesday
<ul>
<li><a href="http://www.kylehailey.com/oaktable-world/">Oaktable World</a>.</li>
<li>Leave at noon.</li>
</ul>
</li>
</ul>
Somewhere in there I'll get a chance to breathe. I'll also attend some sessions, hopefully.
<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com1tag:blogger.com,1999:blog-8884584404576003487.post-90796948912467694692013-08-26T17:01:00.001-04:002013-08-26T17:17:10.653-04:00DBA or Developer?I've always considered myself a developer and a <span class="code">LOWER(DBA)</span>. I may have recovered perhaps one database and that was <a href="http://www.oraclenerd.com/2009/09/learning-bybreaking.html">just a sandbox</a>, nothing production worthy. I've built out instances for development and testing and I've installed the software a few hundred times, at least. I've done DBA-like duties, but I just don't think of myself that way. I'm a power developer maybe? Whatevs.
<br /><br />
I'm sure it would be nearly impossible to come up with <b>One True Definition of The DBA ™</b>. So I won't.
<br /><br />
I've read that <a href="http://asktom.oracle.com/pls/apex/f?p=100:1:0">Tom Kyte</a> does not consider himself a DBA, but I'm not sure most people know that. From Mr. Kyte himself:
<br /><br />
<iframe style="padding-left: 25px;" width="420" height="315" src="//www.youtube.com/embed/vE9zjPLBMiI?rel=0" frameborder="0" allowfullscreen></iframe>
<br /><br />
At the same conference, I asked <a href="http://method-r.com/component/content/article/58-biographies/70-cary-millsap">Cary Millsap</a> the same question:
<br /><br />
<iframe style="padding-left: 25px;" width="420" height="315" src="//www.youtube.com/embed/luCzsQq8tw4?rel=0" frameborder="0" allowfullscreen></iframe>
<br /><br />
I read Cary for years and always assumed he was a DBA. I mean, have you read his papers? Have you read <i><a href="http://www.amazon.com/gp/product/059600527X/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=059600527X&linkCode=as2&tag=o0430-20">Optimizing Oracle Performance</a></i>? Performance? That's what DBAs do (or so I used to think)!
<br /><br />
It was only after working with him at #kscope11 on the Building Better Software track that I learned otherwise.
<br /><br />
Perhaps I'll make this a standard interview question in the future...
<br /><br />
Semi-related discussions:
<br /><br />
1. <a href="http://www.oraclenerd.com/2008/02/application-developers-vs-database.html">Application Developers vs. Database Developers</a>
<br />
2. <a href="http://www.oraclenerd.com/2008/12/application-developers-vs-database.html">Application Developers vs. Database Developers: Part II</a>
<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com7tag:blogger.com,1999:blog-8884584404576003487.post-38778970246783801982013-08-13T16:24:00.002-04:002013-08-13T16:24:24.703-04:00Conditional Formatting of Calculated Items in OBIEE 11g<b><i><span style="font-size: x-small;">By Victor Fagundo</span></i></b><br />
<br />
<div class="MsoNormal">
Calculated items in OBIEE Pivot tables can be very useful in certain reporting circumstances, either for ease of development, or to meet specific report requirements. While calculated items in OBIEE are easy, and flexible, they do have one important drawback: they take on the data and display formatting of the fact column they are calculated against.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The most common case is the calculation of a % change across a time dimension in financial reporting ( Year over Year, Quarter over Quarter, etc.). <sup><a href="#footnote1">1</a> </sup>This type of calculation usually takes the form of a percent change calculation similar to below:<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<pre class="code"> <span style="text-indent: 0.5in;">(( $2 - $1 ) / $1) *100 </span></pre>
</div>
<div class="MsoNormal">
<span style="text-indent: 0.5in;"><br />
</span></div>
<div class="MsoNormal">
By default, if you perform this calculation against a numerical fact ( sales, customers) you will run into the problem of how to display the % change in the correct format, since the calculated % will want to take the form of the fact it is calculated against, as can be seen in the example <sup><a href="#footnote2">2</a></sup> below:<br />
<o:p></o:p></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://lh4.googleusercontent.com/-eySPv16EW84/UgBNuWHm_yI/AAAAAAACN3I/P6b7mNE_9ck/s800/Fig%25201.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="125" src="https://lh4.googleusercontent.com/-eySPv16EW84/UgBNuWHm_yI/AAAAAAACN3I/P6b7mNE_9ck/s800/Fig%25201.jpg" width="600" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 1 - Pivot Table</td></tr></tbody></table>
<br /><br />
<div class="MsoNormal">
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYH8R2IQ7GJPHvnRRlnqemnh3bzgB-pDg5SM6xdCch2GigozcJ7-YnJy0v8vjSNcptYaAqCVoL91t3PQBhfUc9p1nTP4DMhGqYZ5MT471NNrV8fSIQvKu673jhZLV_rrxQTZ5AgRVoONI/s800/Fig%25202.jpg" imageanchor="1" style="clear:left; margin-left: auto; margin-right: auto;"><img border="0" height="350" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYH8R2IQ7GJPHvnRRlnqemnh3bzgB-pDg5SM6xdCch2GigozcJ7-YnJy0v8vjSNcptYaAqCVoL91t3PQBhfUc9p1nTP4DMhGqYZ5MT471NNrV8fSIQvKu673jhZLV_rrxQTZ5AgRVoONI/s800/Fig%25202.jpg" width="600" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 2 - Calculated item</td></tr>
</tbody></table>
<br />
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiy3rcMq6yEkkVPKWCRMriAn8YQcl8SMa4BDjuYg0z_xUUyOl_dy5qy36gz5YbqDf-GhhGIipIx6JviKYB7qyrx2jErhFLMyziEEzNkCcGgPmGUq7ZtiDGhm5BgCAd9s8vP07rlTHpVsEY/s800/Fig%25203.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="122" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiy3rcMq6yEkkVPKWCRMriAn8YQcl8SMa4BDjuYg0z_xUUyOl_dy5qy36gz5YbqDf-GhhGIipIx6JviKYB7qyrx2jErhFLMyziEEzNkCcGgPmGUq7ZtiDGhm5BgCAd9s8vP07rlTHpVsEY/s800/Fig%25203.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><div class="MsoCaption" style="margin-left: .5in; text-indent: .5in;">
Figure 3 - Results</div>
</td></tr>
</tbody></table>
<br />
<div class="MsoNormal">
Not very pretty at all.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
As people searched for a work around to this problem 3 common solutions have arisen:<br />
<ol>
<li><a href="https://forums.oracle.com/thread/859619" target="_blank">Use HTML formatting tricks to “hide” trigger text in the results, then conditionally format off those triggers.</a> While inventive, as the comments note, this solution falls flat if the report is ever printed, as the PDF engine will pick up and display all of the hidden characters.<br /><br />
</li>
<li><a href="http://gerardnico.com/wiki/dat/obiee/presentation_service/obiee_conditionnal_formating_on_pivot" target="_blank">Convert the pivot table to a regular table with some complex column formulas.</a> Very time consuming and cumbersome, would also not solve the requirement of showing the dimension values noted as noted in <a href="http://www.blogger.com/#footnote1">Footnote1</a>.<br /><br />
</li>
<li> <a href="https://forums.oracle.com/thread/2391722" target="_blank">Convert the calculated result to text and manually add your formatting characters</a>. I don’t think this actually works since the calculated fields won’t accept logical SQL functions, and this would be very cumbersome.</li>
</ol>
<div class="MsoNormal">
<div class="separator" style="clear: both; text-align: center;">
</div>
Now with 11g providing conditional formatting that allows you to override the default data format, this is possible via the following steps:<o:p></o:p></div>
<ol>
<li>Add a column that is a COUNT DISTINCT on the dimension that you are calculating across ( in the displayed example, “Time T05 Per Name Year”. This column will serve as your “trigger” to apply your conditional formatting.<br /><br /><table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://lh5.googleusercontent.com/-mCv6pZGgvyc/UgBNvzb5swI/AAAAAAACN3I/zT-tgmNmEYc/s800/Fig%25204.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="117" src="https://lh5.googleusercontent.com/-mCv6pZGgvyc/UgBNvzb5swI/AAAAAAACN3I/zT-tgmNmEYc/s800/Fig%25204.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><div class="MsoCaption" style="margin-left: .5in; text-indent: .5in;">
Figure <!--[if supportFields]><span
style='mso-element:field-begin'></span><span
style='mso-spacerun:yes'> </span>SEQ Figure \* ARABIC <span style='mso-element:
field-separator'></span><![endif]-->4<!--[if supportFields]><span
style='mso-element:field-end'></span><![endif]--> - Column Formula<o:p></o:p></div>
</td></tr>
</tbody></table>
<br />
<div style="text-align: left;">
</div>
</li>
<li>For each of your facts, apply a conditional format that is triggered when the above column value is zero. In the formatting, apply whatever visual and data formats you desire. In this example we will format the data as a percent, with one decimal place.<br /><br /><table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhijofTr_CR-8z7yS9cjUlzCKtRa_E2b4fID-fIOu0rYp86Qn7ImKY3VaR9L6h0qJkAkqhh5wDV9m6OOiJXYeBZ8k6w49__9wgeV331Flb7QDde5n5vAGV8hB1qdyPosPiJHqH0-qNqEsc9/s800/Fig%25205.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="142" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhijofTr_CR-8z7yS9cjUlzCKtRa_E2b4fID-fIOu0rYp86Qn7ImKY3VaR9L6h0qJkAkqhh5wDV9m6OOiJXYeBZ8k6w49__9wgeV331Flb7QDde5n5vAGV8hB1qdyPosPiJHqH0-qNqEsc9/s800/Fig%25205.jpg" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 5 - Condition</td></tr>
</tbody></table>
<br /><br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://lh4.googleusercontent.com/-QTTYEBwqgmg/UgBNwDffutI/AAAAAAACN3I/N4CrSvyMUb0/s800/Fig%25206.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="255" src="https://lh4.googleusercontent.com/-QTTYEBwqgmg/UgBNwDffutI/AAAAAAACN3I/N4CrSvyMUb0/s800/Fig%25206.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 6 - Format when condition is met</td></tr>
</tbody></table>
<br />
</li>
<li>Exclude the “trigger” column from your pivot view. View your results and be satisfied:<br /><br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://lh3.googleusercontent.com/-87AqWHN3Yic/UgBNwYRlchI/AAAAAAACN3I/idBhOV-sCvA/s800/Fig%25207.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="113" src="https://lh3.googleusercontent.com/-87AqWHN3Yic/UgBNwYRlchI/AAAAAAACN3I/idBhOV-sCvA/s800/Fig%25207.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 7 - Correct formatting of calculated item.</td></tr>
</tbody></table>
</li>
</ol>
<br />
* <i>Note that this would also allow you to apply visual formatting if you wanted to distinguish this row/column as a total.</i>
<br /><br />
<h2>Why it works</h2>
The use of conditional formatting that applies a data type as part of the format is a straightforward leap of logic, but what to use as the trigger? Most people will try to use the dimension they have setup the calculation in. However, if you try to use the text description given to the calculated item you will find that the condition is never applied:<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://lh4.googleusercontent.com/-9gaRCbzMONs/UgBNwRtqFaI/AAAAAAACN3I/XaxnuveILbQ/s800/Fig%25208.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="141" src="https://lh4.googleusercontent.com/-9gaRCbzMONs/UgBNwRtqFaI/AAAAAAACN3I/XaxnuveILbQ/s800/Fig%25208.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 8 - Condition on dimension</td></tr>
</tbody></table>
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFEs1oKMGzkSW1Ja96dCOjnzLs_kiEVdf_Ezvq2dl44eDIqa-jGgm6fCVtbKPvo_Z9IWOcU1N-ZJfea5POGxnVu6WS9cULoUKPNy6Pnxn6bKlbI3xNKnLWBYVUxP_x6jtQcusKptz3DCsy/s800/Fig%25209.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="119" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFEs1oKMGzkSW1Ja96dCOjnzLs_kiEVdf_Ezvq2dl44eDIqa-jGgm6fCVtbKPvo_Z9IWOcU1N-ZJfea5POGxnVu6WS9cULoUKPNy6Pnxn6bKlbI3xNKnLWBYVUxP_x6jtQcusKptz3DCsy/s800/Fig%25209.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 9 - Condition never met, format never applied</td></tr>
</tbody></table>
<br />
If you try to setup a filter that is true when the dimension is not in reasonable range of values ( in this example we try to format off all years not in the 2000s ) you will find that your calculated item is skipped as well (this has the added vulnerability of being very explicit):<br />
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3g9GyRvwQbxbIjsy2clkHVITrqJ8uYqGntV2bDOTlCiSBwBICf7ZDQ9IExFb7hUjbTi7Hr-I-RHfa0Cn0x_Bi8OMvtCaYhAo0bVYUFp-DPMtbKRRKGFZnB_9TFkO2JrNNmFSQvik2yDQ/s800/Fig%252010.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="123" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3g9GyRvwQbxbIjsy2clkHVITrqJ8uYqGntV2bDOTlCiSBwBICf7ZDQ9IExFb7hUjbTi7Hr-I-RHfa0Cn0x_Bi8OMvtCaYhAo0bVYUFp-DPMtbKRRKGFZnB_9TFkO2JrNNmFSQvik2yDQ/s800/Fig%252010.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 10 - Condition on dimension values</td></tr>
</tbody></table>
<br />
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwQnv3c2eRP_2Wkd_rPw3032IeFHUINZMWTAjDTILhyZlrfWLTmbdW5S827mdeao0e-pzqpAXOO_vZRExlAPS2zEADpiu-Yz0VSinIQLwxvghOXUECYEoT8KnJmeD0wwAEspR8ya1gF1g/s800/Fig%252011.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="108" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwQnv3c2eRP_2Wkd_rPw3032IeFHUINZMWTAjDTILhyZlrfWLTmbdW5S827mdeao0e-pzqpAXOO_vZRExlAPS2zEADpiu-Yz0VSinIQLwxvghOXUECYEoT8KnJmeD0wwAEspR8ya1gF1g/s800/Fig%252011.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 11- Condition never met, format never applied</td></tr>
</tbody></table>
</div>
<div class="MsoNormal">
<br />
The reason for all this is that the calculated item “borrows” EACH of the dimension values it operates against. Hence, no matter how inventive your filter is, as long as you are trying to somehow separate the calculated member away from the members it is operating on, you will never succeed. This member “borrowing” is apparent if you add the dimension it operates against to the query a 2nd time, and look at the table view.</div>
<div class="MsoNormal">
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeol7DDdBWAM1cC0_zHxNInU70tCcITq0YyvDOIlk21TsnPX_wOOyiTV5Q6I57ycC0fg6xVblDy-dAItnZOm2WOOTA2yTjqU9JgVUPNz1Xe3ykGYvp4UnIzHx12uiQKhOnZTIRw5q0Txc/s800/Fig%252012.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="261" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeol7DDdBWAM1cC0_zHxNInU70tCcITq0YyvDOIlk21TsnPX_wOOyiTV5Q6I57ycC0fg6xVblDy-dAItnZOm2WOOTA2yTjqU9JgVUPNz1Xe3ykGYvp4UnIzHx12uiQKhOnZTIRw5q0Txc/s800/Fig%252012.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 12 - Calculated item "borrows" members</td></tr>
</tbody></table>
<br />
But since the “member value” given to the calculated item does not actually exist in the dimension, if you try to perform a count distinct against it, you will always get zero.</div>
<div class="MsoNormal">
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://lh6.googleusercontent.com/-wuE89T3RL40/UgBNvZ4cnCI/AAAAAAACN3I/2Kyx_ThelXQ/s800/Fig%252013.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="167" src="https://lh6.googleusercontent.com/-wuE89T3RL40/UgBNvZ4cnCI/AAAAAAACN3I/2Kyx_ThelXQ/s800/Fig%252013.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Figure 13 - Count distinct against dimension</td></tr>
</tbody></table>
<br />
There is your difference; there is your “trigger.” The rest is basic formatting.
<br />
<br />
<span id="footnote1">1: </span> <i>You might suggest that this requirement is better served using column(s) with time series calculations, and you might be right. However, more often than not the user will want to SEE the time periods being compared ( 2012 vs 2011, or 08/07/2012 vs 08/07/2011). When using facts with time series calculations you will only be able to show “this year” vs “last year” since the column heading of the time series calculated fact will always be static. In these cases you will need to use the base fact and a time dimension, along with the solution provided here.</i>
<br />
<br />
<span id="footnote2">2: </span><i>All screen shots, and examples used in this post are performed in <a href="http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html" target="_blank">Sample App V305</a>. An XML of the final correctly formatted report can be downloaded <a href="https://docs.google.com/file/d/0B4GW2X8EUcQIV3NwYzgyVDBPcE0/edit?usp=sharing" target="_blank">here</a>.</i></div>
<div class="MsoNormal" style="text-indent: .5in;">
<o:p></o:p></div><div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com17tag:blogger.com,1999:blog-8884584404576003487.post-37610717372895484382013-07-30T23:02:00.002-04:002013-07-30T23:02:55.911-04:00Learn To ______ In A YearIt started at The <a href="http://thetalentcode.com/">Talent Code</a> blog by Daniel Coyle a few weeks back, <a href="http://thetalentcode.com/2013/07/01/whats-your-lq-learning-quotient/"><i>What's Your LQ (Learning Quotient)?</i></a>. That led me to <a href="http://www.nytimes.com/2013/07/01/sports/baseball/diamondbacks-goldschmidt-has-little-ego-and-few-limits.html"><i>Diamondbacks’ Goldschmidt Has Little Ego and Few Limits</i></a>. I like baseball stories. I especially like this passage:
<br /><br />
<div class="documentation">“A lot of kids have so much pride that they want to show the coaches and the front office that they know what they’re doing, and they don’t need the help,” Zinter said. “They don’t absorb the information because they want us to think they know it already. Goldy didn’t have an ego. He didn’t have that illusion of knowledge. He’s O.K. with wanting to learn.”</div>
<br />
I identify with that. I believe part of my success is because I ask questions.
<br /><br />
Back to the original article. Then I end up here, <i><a href="http://blogs.kqed.org/mindshift/2011/11/can-everyone-be-smart-at-everything/">Can Everyone Be Smart at Everything?</a></i> I seem to lack the ability to focus for extended periods of time. Well, not quite true. I have the ability to focus, but I like to focus on a million different things. Does that count? I don't know.
<br /><br />
I'm often envious of my friends who have been DBAs for 20 years, or worked with OBIEE for 10 years (don't argue with me...I know Oracle hasn't owned it for 10 years, I'm looking at you <a href="https://twitter.com/nephentur">Christian</a>), or APEX for 10 years (that's safe to say). I've flirted with all of those, but I've never committed...See how I get distracted easily? Wow.
<br /><br />
<div class="documentation">And just as importantly, that mistakes are part of good learning. As a Wired article recently reported about why some are more effective at learning from mistakes, “the important part is what happens next.” People with a “growth mindset” — those who “believe that we can get better at almost anything, provided we invest the necessary time and energy” — were significantly better at learning from their mistakes.</div>
<br />
and then...
<br /><br />
<div class="documentation">“The meaning of difficulty changes. Difficulty means trying harder, trying a different strategy. They understand that change is possible, and progress occurs over time.”</div>
<br /><br />
OMFG. Focus!
<br /><br />
Back to the original article and I'm reading through the comments. Someone links up to this young lady who taught herself how to <a href="http://www.danceinayear.com/story/">dance in a year</a>. Watch it.
<br /><br />
<iframe width="560" height="315" src="//www.youtube.com/embed/daC2EPUh22w?rel=0" frameborder="0" allowfullscreen></iframe>
<br /><br />
Which finally brings me back to The Talent Code, <i><a href="http://thetalentcode.com/2013/07/18/to-improve-faster-think-like-a-startup/">To Improve Faster, Think Like a Startup</a></i>. Staying with me? How about this?
<br /><br />
<img style="left-padding: 25px;" src="https://lh3.googleusercontent.com/-ZG_1kFUVy-c/Ufh9Qk3UTtI/AAAAAAACNow/kYfASrorb6A/s800/the_crazy_loop2.png" />
<br /><br />
Finally, there's a point. I want to do this. Maybe not dance (as much fun as that may be), but something else. Krav Maga? Algebra? Calculus (I'm pursuing my physics or engineering degree in 2035, I need to study my math). I want to test out her technique. Small, discrete steps practiced daily towards some end goal (pass a calc test, take a real estate licensing test, whatever). The problem for me, if you haven't noticed, is focus. This method may help.
<br /><br />
If you were to try something like this, what would you set out to learn?<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com7tag:blogger.com,1999:blog-8884584404576003487.post-11017264426864251962013-07-08T11:54:00.001-04:002013-07-08T11:54:25.316-04:00Write It OutThis one was sitting in the drafts folder for a week or two, then I saw this post on Twitter:
<br />
<blockquote class="twitter-tweet"><p>I wonder what percentage of people ask a question and figure it out on their own before you help them. What about in your experience?</p>— Amy Caldwell (@amyccaldwell) <a href="https://twitter.com/amyccaldwell/statuses/354257005839519745">July 8, 2013</a></blockquote>
<script async src="//platform.twitter.com/widgets.js" charset="utf-8"></script>
<br />
Years ago I had a boss who was my technical superior (he may still be). I used to pop in and out of his office, or try to, and ask questions. Most of them were silly, n00b questions.
<br /><br />
He was nice, but busy. It didn't take me very long to "read" that. So I slowed down my pace of questions. I began to write things up via <a href="http://www.oraclenerd.com/2010/06/why-i-love-email.html">email</a> so that he could respond when <i>he</i> the had time. I started to use forums as well. Then I <strike>found</strike> was directed to <i><a href="http://www.catb.org/esr/faqs/smart-questions.html">How To Ask Questions The Smart Way</a></i>.
<br /><br />
One of the things that became evident quickly is that I didn't always have to hit Send (email) or Submit (forum post), just the act of writing it out forced me to think through the issue and more often than not, I would figure out the answer on my own.
<br /><br />
Flash forward five or six years and I started to receive all these questions, either in person or via chat. "Send me an email" was usually my response, especially if I was in the middle of something (see: <a href="http://www.oraclenerd.com/2013/07/context-switching-example.html">Context Switching</a>). I was happy to help, just not at that moment. With email, I could get to it when I got a break (or needed one).
<br /><br />
One of my favorite people, Jason Baer, who has worked for RittmanMead for the last couple of years, took this to heart. We started working together in December of 2009 and he would pepper me with questions constantly. I could never keep up. "Email the question Jason."
<br /><br />
I didn't realize it, but I started getting fewer and fewer emails/questions from him. He began to figure them out on his own. It seemed most of the time he had just missed something, other times he just figured out another way to do something.
<br /><br />
Jason is a smart guy. I think I'm smart. Sometimes it's just easier to ask the question without thinking it through. In fact, I do that quite a bit on The Twitter Machine ™, especially those errors that I seem to know but just don't have the bandwidth to research (think DBA type questions). I believe the types of questions that <b><strike>should</strike></b> <b>must</b> be written down are those that deal with Approach (design, architecture, etc). Any of those ORA errors better come along with a link to the error code in the documentation and some proof that you've researched it a bit yourself...but then that's getting into <i><a href="http://www.catb.org/esr/faqs/smart-questions.html">How To Ask Questions The Smart Way</a></i>.
<br /><br />
Go out and practice. Next time you have a (technical) question for someone, anyone, write it down and see what happens.
<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com9tag:blogger.com,1999:blog-8884584404576003487.post-29610305733423739342013-07-01T16:45:00.000-04:002013-07-01T16:51:33.926-04:00Context Switching: An ExampleLast week at #kscope13 I saw an outstanding example of context switching. If you don't know what it is, Tom Kyte explains it <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:60122715103602">here</a>.
<br /><br />
<div class="documentation">The two environments are just "different", separate and distinct. You can do plsql without SQL, you can do SQL (and many times do) without invoking plsql. There is a call
overhead to go from SQL to PLSQL (the "hit" is most evident when SQL invokes PLSQL - not so much the other way, when SQL is embedded in PLSQL). Even if this hit is very very small (say 1/1000th of a second) - if you do it enough, it adds up. So, if it can be avoided - it should be.</div>
<br />
The session was <i><a href="http://kscope13.com/component/seminar/seminarslist#Using Kanban and Scrum to Increase Your Development Throughput">Using Kanban and Scrum to Increase Your Development Throughput</a></i> presented by Stew Stryker (not to be confused with <a href="http://www.imdb.com/character/ch0006142/">Ted Striker</a>) of Dartmouth College (Stew gave me a gallon of Vermont Maple Syrup which exploded in my bag on the flight home, a gift for sharing my hotel room. Thanks Stew! ;)). So here's the example he gave to demonstrate context switching.
<br /><br />
Take a list of names and time yourself writing out the first letter of each name, then the second, until you are finished.
<br /><br />
<img style="margin-left: 25px;" src="https://lh4.googleusercontent.com/-axsXJNC0DHU/UdHo7QKhXpI/AAAAAAACMJ8/VEITVcNAQ3U/s800/Screenshot%2520from%25202013-07-01%252016%253A32%253A32.png" />
<br /><br />
Now, same list of names and write them out the way you normally would, left to right.
<br /><br />
<img style="margin-left: 25px;" src="https://lh4.googleusercontent.com/-CEb_N8ox-zA/UdHo7aoFsgI/AAAAAAACMJ8/XHplUeXq7qE/s800/Screenshot%2520from%25202013-07-01%252016%253A32%253A46.png" />
<br /><br />
If the first method was faster, you are a freak of nature. <div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com2tag:blogger.com,1999:blog-8884584404576003487.post-75037865020652549492013-06-12T14:10:00.002-04:002013-08-29T18:59:05.193-04:00Required ReadingIt's not often that I run across articles that really resonate with me. Last night was one of those rare occasions. What follows is a sampling of what I consider to be required reading for any IT professional with a slant towards database development.
<ul>
<li><a href="#bad_carma">Bad CaRMa</a></li>
<li><a href="#big_ball_of_mud">Big Ball of Mud</a></li>
<li><a href="#how_to_ask_a_question">How To Ask Questions The Smart Way</a></li>
<li><a href="#business_logic">Business Logic - PL/SQL Vs Java - Reg</a></li>
<li><a href="#thinking_clearly">Thinking Clearly About Performance</a></li>
<li><a href="#gloves">The Complicator's Gloves</a></li>
</ul>
<h2 id="bad_carma">Bad CaRMa</h2>
<blockquote class="twitter-tweet"><p>how NOT to design a database schema - super classic article. Every data architect should read this ! <a href="https://t.co/ktRcO7NZSy" title="https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/">simple-talk.com/opinion/opinio…</a> @<a href="https://twitter.com/timothyjgorman">timothyjgorman</a></p>— Kyle Hailey (@dboptimizer) <a href="https://twitter.com/dboptimizer/status/344573030179147777">June 11, 2013</a></blockquote>
<script async src="//platform.twitter.com/widgets.js" charset="utf-8"></script>
<br />
That led me to <i><a href="https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/">Bad CaRMa</a></i> by <a href="http://www.linkedin.com/in/timgorman">Tim Gorman</a>. This was an entry in <i><a href="http://www.amazon.com/gp/product/1590593871/ref=as_li_qf_sp_asin_il_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=1590593871&linkCode=as2&tag=o0430-20">Oracle Insights: Tales of the Oak Table</a></i>, which I have not read, yet.
<br /><br />
A snippet:
<br /><br />
<div class="documentation">...The basic premise was that just about all of the features of the relational database were eschewed, and instead it was used like a filing system for great big plastic bags of data. Why bother with other containers for the data—just jam it into a generic black plastic garbage bag. If all of those bags full of different types of data all look the same and are heaped into the same pile, don't worry! We'll be able to differentiate the data after we pull it off the big pile and look inside.
<br /><br />
Amazingly, Randy and his crew thought this was incredibly clever. Database engineer after database engineer were struck dumb by the realization of what Vision was doing, but the builders of the one-table database were blissfully aware that they were ushering in a new dawn in database design...</div>
<br />
This is from 2006 (the book was published in 2004). Not sure how I missed that story, but I did.
<h2 id="big_ball_of_mud">Big Ball of Mud</h2>
I've read this one, and sent it out, many times over the years. I can't remember when I first encountered it, but I read this once every couple of months. I send it out to colleagues about as often. You can find the article <a href="http://www.laputan.org/mud/">here</a>.
<br /><br />
<div class="documentation">A BIG BALL OF MUD is haphazardly structured, sprawling, sloppy, duct-tape and bailing wire, spaghetti code jungle. We’ve all seen them. These systems show unmistakable signs of unregulated growth, and repeated, expedient repair. Information is shared promiscuously among distant elements of the system, often to the point where nearly all the important information becomes global or duplicated. The overall structure of the system may never have been well defined. If it was, it may have eroded beyond recognition. Programmers with a shred of architectural sensibility shun these quagmires. Only those who are unconcerned about architecture, and, perhaps, are comfortable with the inertia of the day-to-day chore of patching the holes in these failing dikes, are content to work on such systems.</div>
<br />
Read it. Remember it.
<h2 id="how_to_ask_a_question">How To Ask Questions The Smart Way</h2>
Ever been in a forum? Has anyone ever given you the "RTFM" answer? Here's how you can avoid it. <i><a href="http://www.catb.org/esr/faqs/smart-questions.html">How To Ask Questions The Smart Way</a></i>. I read this originally about 9 or 10 years ago. I've sent it out countless times.
<br /><br />
<div class="documentation">The first thing to understand is that hackers actually like hard problems and good, thought-provoking questions about them. If we didn't, we wouldn't be here. If you give us an interesting question to chew on we'll be grateful to you; good questions are a stimulus and a gift. Good questions help us develop our understanding, and often reveal problems we might not have noticed or thought about otherwise. Among hackers, “Good question!” is a strong and sincere compliment.
<br /><br />
Despite this, hackers have a reputation for meeting simple questions with what looks like hostility or arrogance. It sometimes looks like we're reflexively rude to newbies and the ignorant. But this isn't really true.
<br /><br />
What we are, unapologetically, is hostile to people who seem to be unwilling to think or to do their own homework before asking questions. People like that are time sinks — they take without giving back, and they waste time we could have spent on another question more interesting and another person more worthy of an answer. We call people like this “losers” (and for historical reasons we sometimes spell it “lusers”).</div>
<h2 id="business_logic">Business Logic - PL/SQL Vs Java - Reg</h2>
The article can be found <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:883929178230">here</a>.
<br /><br />
I'm don't believe this is the one that I would read just about every day during my first few years working with Oracle, but it's representative (I'll link up the original when I find it). I cut my teeth in the Oracle world by reading AskTom every single day for years. Some of my work at the time included working with java server pages (jsp) - at least until I found APEX. I monkeyed around with BC4J for awhile as well, but I believe these types of threads on AskTom kept me from going off the cliff. In fact, I got to a point where I would go to an interview and then debate the interviewer about this same topic. Fun times.
<br /><br />
<div class="documentation">if it touches data -- plsql.
<br /><br />
If it is computing a fourier transformation -- java.
<br /><br />
If it is processing data -- plsql.
<br /><br />
If it is generating a graph -- java.
<br /><br />
If it is doing a transaction of any size, shape or form against data -- plsql.</div>
<h2 id="thinking_clearly">Thinking Clearly About Performance</h2>
<a href="http://www.linkedin.com/pub/cary-millsap/0/296/118">Cary Millsap</a>. Most of the people seem to know Cary from <a href="http://www.amazon.com/gp/product/B00BJ9A8SU/ref=as_li_qf_sp_asin_il_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=B00BJ9A8SU&linkCode=as2&tag=o0430-20">Optimizing Oracle Performance</a>, I didn't. I first "met" Cary virtually and he was gracious enough to help me understand my questions around <i><a href="http://www.oraclenerd.com/2009/07/logging-debugging-instrumentation-and.html">Logging, Debugging, Instrumentation and Profiling</a></i>. Anyway, what I've learned over that time, is that Cary doesn't think of himself as a DBA, he's a Developer. That was shocking for me to hear...I wonder how many others know that. So I've read this paper about 20 times over the last couple of years (mostly because I'm a little slow). I organize events around this topic (instrumentation, writing better software, etc) and this fits in perfectly. My goal is to one day co-present with Cary, while playing catch, on this topic (I don't think he knows that, so don't tell him). Link to his paper can be found <a href="http://method-r.com/downloads/doc_details/44-thinking-clearly-about-performance">here</a>. Enjoy!
<h2 id="gloves">The Complicator's Gloves</h2>
One of my favorite articles from <a href="http://thedailywtf.com/">The Daily WTF</a> of all time. Find the article <a href="http://thedailywtf.com/Articles/The_Complicator_0x27_s_Gloves.aspx">here</a>. The gist of the story is this: an internal forum where people were discussing how to warm a given individuals hands on his bike ride to work. The engineers then proceeded to come up with all kinds of solutions...they spent all day doing this. Finally, someone posts, "wear gloves." End of discussion. Love it. I wrote about it years ago in <i><a href="http://www.oraclenerd.com/2007/11/keeping-it-simple.html">Keeping it Simple</a></i>. For a few years I considered buying up thecomplicatorsgloves.com and try to gather related stories, but I got lazy. You should read this often, or better, send it out to colleagues on a regular basis to remind them of their craziness.
<br /><br />
I'll continue to add to this list as time goes on. If you have any suggestions, leave a comment and I'll add them to the list.<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com2tag:blogger.com,1999:blog-8884584404576003487.post-62632596830102055252013-04-18T22:00:00.000-04:002013-08-19T13:08:59.318-04:00caveats
I always find myself putting an asterisk (if only mentally) next to certain statements. I shall now put all those statements here and link back.
<br />
<ol>
<li>I don't know <i>everything</i></li>
<li>I'm not the best developer in the world, but I <a href="http://www.oraclenerd.com/2007/11/i-want-to-be-better-than-tom-kyte.html">constantly work at getting better</a>...</li>
<li>If I make a statement about something, that's been <i><b>my</b></i> experience. Your results may vary.</li>
<li>I am not a salesman.</li>
<li>I <b>do not</b> work for <insert company name which I just pitched here></li>
</ol>
That's it...for now.<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com2tag:blogger.com,1999:blog-8884584404576003487.post-74127978006123032632013-04-06T18:40:00.000-04:002013-04-06T18:50:18.419-04:00Ubuntu 12.10 + nvidiaI updated my host OS a few months back after getting repeated notifications (yes, I know, I can shut them off) that 10.04 (I think) was moving out of support.
<br /><br />
Since then, I've had an issue with my Nvidia drivers. Basically, I get video on a single monitor (dual set up) and that single monitor resolution is like 200 x 400 (no, it's not really that, but it is gigantic). Thank goodness for The Google Machine™. That originally led me <a href="http://askubuntu.com/questions/214150/ubuntu-12-10-wont-display-properly-after-kernel-upgrade">here</a> on StackOverflow. <i>(Another reason to do things from the command line, you can remember things with </i><span class="code">history | grep nvidia</span>).
<br /><br />
I'm on the 4th time of going through this exercise. Each time the kernel is updated, nvidia breaks. Fortunately for me, that guy on StackOverflow gave me all the information I needed. This time after reboot and the gigantic screen, I removed the nvidia drivers and then reinstalled them. No go. <span class="code">uname -r</span> gave me the following: 3.5.0-26-generic and <span class="code">dpkg -l|grep headers</span> showed an older version of the kernel headers. So I updated those, reinstalled nvidia-current and rebooted. Yay.
<br /><br />
Many "small" issues like this recently have me pondering a move back to, gasp, Windows or perhaps even a Mac. The Mac ecosystem scares me because it is expensive...but it's difficult to square when so many of my friends (technical and otherwise) swear by Macs. Something for another day I guess...<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com5tag:blogger.com,1999:blog-8884584404576003487.post-43471953958999979372013-04-04T17:28:00.001-04:002013-04-04T17:28:52.154-04:00Fun with CHARI'm busy deriving file layouts from PL/SQL. Probably close to 100 file definitions...each of them slightly different, each of them defined in the code. Fun!
<br /><br />
There are a mixture of types too, fixed width, csv, etc. Thankfully, I've read enough of the code now that it's relatively easy to figure out. The fixed width variety is what this is about though.
<br /><br />
In much of the code, there's a type that's defined, something like this:<pre class="code">type my_record is record
(
column_01 CHAR(10),
column_02 CHAR(10),
column_03 CHAR(10)
);</pre>
That's then used to receive assignments from incoming variables. I'll hardcode my variables for this exercise.<pre class="code">declare
type my_record is record
(
column_01 CHAR(10),
column_02 CHAR(10),
column_03 CHAR(10)
);
l_rec my_record;
begin
l_rec.column_01 := '1';
l_rec.column_02 := '3';
l_rec.column_03 := '6';
end;</pre>Littered throughout those assignments though, are things like LPAD and RPAD. You're going to say, "well, yeah, if it's a number, you may want it right aligned or something." Fair enough. But I'm not talking about those, I'm talking about this:<pre class="code"> l_rec.column_01 := rpad( ' ', 10 );
l_rec.column_02 := '3';
l_rec.column_03 := RPAD( ' ', 10 );</pre>Ostensibly, these columns once held data. Instead of forcing the client (application, business, whatever) to change their processing bit, the file was left the same. Makes sense.
<br /><br />
Then I started to think about it...it's a CHAR. CHAR is already fixed width. To wit:<pre class="code">drop table t purge;
create table t
(
x CHAR(10)
);
insert into t ( x ) values ( ' ' );
insert into t ( x ) values ( null );
select
rownum,
length( x ),
x
from t;
ROWNUM LENGTH(X) X
---------- ---------- ----------
1 10
2 </pre>
I inserted a single space in the first record. It has a length of 10 despite only inserting a single character there.
<br /><br />
So what's the purpose of those <span class="code">RPAD( ' ', 10 )</span> calls? I'm not sure.
<br /><br />
The only reason I even began to think about it was that I ran across one type set up with VARCHAR data types. <i><b>There</b></i> it makes sense, using RPAD I mean. With the CHAR field, it's a waste of typing IMO. Perhaps it was just for readability...who knows?<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com2tag:blogger.com,1999:blog-8884584404576003487.post-27775949212240237112013-03-26T11:14:00.004-04:002013-03-26T11:29:54.985-04:00On Work/Life Balance<a href="http://en.wikipedia.org/wiki/Nolan_Bushnell">Nolan Bushnell</a>, founder of Atari, had <a href="http://mashable.com/2013/03/25/steve-jobs-atari-book-exclusive/">this</a> to say via his new book, <i>Find the Next Steve Jobs</i>.
<br /><br />
<div class="documentation">(It’s been said that many people in high tech cannot balance their personal and work lives. Here’s another way to look at it: Their jobs are so interesting that it’s difficult to figure out what is work and what is play. Creative projects produce this kind of excitement.)</div>
<br />
I've struggled with the work/balance thing. I'm better now than I was a year ago, but it takes a lot of work. That quote definitely illustrates <i>one</i> aspect of how it could get so out of balance...I thoroughly enjoy what I do.<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com2tag:blogger.com,1999:blog-8884584404576003487.post-16498059495540509352013-03-25T22:59:00.001-04:002013-03-25T23:04:13.940-04:00Analysis Tools...I've taken on an effort to port a custom data integration (PL/SQL, Java, etc) application.
<br /><br />
In that regard, I'm doing a fair amount of analysis right now. So I need help finding two tools:<br />
1. A tool that will allow me to map (visually or otherwise) a single data point from source to target(s). I typically use Excel. It's easy to use and available everywhere. Where it falls apart, slightly, is that a single data point may have one or more middle steps (i.e. not target) and one or more targets. I think I want something like this:
<br /><br />
<img style="margin-left: 25px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQeDv7kiyL8WMx3rit-EeFJUebC9aWqSPNhNgkCD8GOwndpC77e0Y1mP4A7z-ma9G5U9OJ12ikQM_t94UPdlP43vc-HRyiN56zK-NwbiOz6-YgHZrjReqGTGKCFoe0CBWIp0mmu6HZpfo/s800/code_flow.png" />
<br /><br />
Keep in mind though, I have potentially hundreds of columns in a system with thousands upon thousands of...
<br /><br />
A couple of people have suggested using an ETL tool like Informatica, Pentaho or ODI. Yes. But I don't see it yet. Besides, I don't want to map to actually do something...most of the conversion has already been done and I'm picking it up at a particular step (near the beginning). What's missing is that mapping document that I want to create for everything...but that's another story.
<br /><br />
2. I want to to look at a view and know where those stupid unaliased columns are sourced from. A very, very basic example:<pre class="code">SELECT
hs.column_1,
hs.column_2,
add.address_line_1,
var_value_01,
var_value_02
FROM
big_table hs,
address_table add,
other_random_table ran
WHERE hs.address_id = add.address_id
AND hs.random_id = ran.random_id</pre>VAR_VALUE_01 and VAR_VALUE_02, why don't you have aliases? Why did your developer neglect you so? Why can't every single developer just remember that someone, someday, will have to look at their code? Please? Pretty please? Or did you know it would be me and thus you did it on purpose? If so, I'm not talking to you again.
<br /><br />
Anyway, it doesn't take me very long to figure where those columns are sourced from. What if there are 10's of those in a view with 100's of columns? Yes, not enjoyable. What if there are many views just like this that you have to analyze? Yes!
<br /><br />
Data Dictionary?!
<br /><br />
Not yet. DBA_TAB_COLUMNS? Nope. Come on! It's got to be there somewhere...when you compile a view Oracle checks to make sure everything is a-ok right? Doesn't it store that information somewhere? <b>It must!</b>. I took to Twitter, naturally, and Steve Karam, aka <a href="https://twitter.com/OracleAlchemist">@OracleAlchemist</a> found this possible gem:
<br /><br />
<blockquote class="twitter-tweet" data-conversation="none"><p>@<a href="https://twitter.com/oraclenerd">oraclenerd</a> @<a href="https://twitter.com/thatjeffsmith">thatjeffsmith</a> @<a href="https://twitter.com/eaviles94">eaviles94</a> Check this out Chet. <a href="http://t.co/aaPe33Frvm" title="http://rwijk.blogspot.com/2008/10/dbadependencycolumns.html">rwijk.blogspot.com/2008/10/dbadep…</a></p>— Steve Karam (@OracleAlchemist) <a href="https://twitter.com/OracleAlchemist/status/316366400379817984">March 26, 2013</a></blockquote>
<script async src="//platform.twitter.com/widgets.js" charset="utf-8"></script>
<br />
I'm also requesting a feature in SQL Developer...or, trying to anyway. Back channels of course.
<br /><br />
I've done this kind of analysis in the past, but it is usually a one off, so there never seemed to be a need to make it repeatable. Now, there is a need. A <b>giant</b> need. If you've got any ideas for me, let me know...<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com3tag:blogger.com,1999:blog-8884584404576003487.post-52080694887595482672013-03-20T21:56:00.000-04:002013-03-20T21:56:54.373-04:00The InternetHave you seen this State Farm ad?
<br /><br />
<iframe width="853" height="480" src="http://www.youtube.com/embed/rmx4twCK3_I" frameborder="0" allowfullscreen></iframe>
<br /><br />
I think it's hilarious.
<br /><br />
Riding to batting practice with LC, he starts up with me...
<img src="https://lh5.googleusercontent.com/-pu9BN7WJW_E/S0GFyCLLSoI/AAAAAAABV0o/3LQNkUQuIqw/s144/DSC08979.jpg" style="float:right;padding:10px;" />
<br /><br />
<b>LC</b>: (in response to some statement I made) "Where'd you hear that?"<br />
<b>Me</b>: "The Internet"<br />
<b>LC</b>: "And you believed it?"<br />
<b>Me</b>: "Yeah, they can't put anything on the internet that isn't true."<br />
<b>LC</b>: "Where'd you hear that?"<br />
<b>Together</b>: "The Internet"
<br /><br />
We also do the "<a href="http://www.youtube.com/watch?v=K7luMp6lb9M">And then...?</a>" skit from <a href="http://www.imdb.com/title/tt0242423/">Dude, Where's My Car?</a>. He used to be able to rattle off the saying from <a href="http://www.imdb.com/title/tt0114694/">Tommy Boy</a>, "You can get a good look at a t-bone by sticking your head up a bull's..." - I'm pretty sure this is better than that. <div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com1tag:blogger.com,1999:blog-8884584404576003487.post-9579958631507259512013-03-19T22:08:00.000-04:002013-03-19T22:08:55.459-04:00dbms_output.put_lineI've been scratching my eyes out lately trying to reverse engineer <strike>some</strike> lots of PL/SQL.
<br /><br />
One thing I've seen a lot of is calls to <span class="code">dbms_output.put_line</span>. Fortunately, I've seen some <span class="code">dbms_application_info.set_module</span> and other system calls too. But back to that first one.
<br /><br />
1. When I used <span class="code">dbms_output</span>, I would typically only use it in development. Once done, I would remove all calls to it, test and promote to QA. It would never survive the trip to production. <br />
2. Typically, when I used it in development, I would tire of typing out <span class="code">d b m s _ o u t p u t . p u t _ l i n e</span> so I would either a, create a standalone procedure or create a private procedure inside the package, something like this (standalone version).<pre class="code">CREATE OR REPLACE
PROCEDURE p( p_text IN VARCHAR2 )
IS
BEGIN
dbms_output.put_line( p_text );
END p;</pre>Easy. Then, in the code, I would simply use the procedure p all over the place...like this:<pre class="code"> l_start_time date;
l_end_time date;
begin
l_start_time := sysdate;
p( 'l_start_time: ' || l_start_time );
--do some stuff here
--maybe add some more calls to p
l_end_time := sysdate;
p( 'l_end_time: ' || l_start_time );
end;</pre>Since the procedure is 84 characters long, I only have to use the p function 4 times to get the benefit. Yay for me...I think. Wait, <a href="http://www.oraclenerd.com/2011/01/my-new-typing-tutor.html">I like typing</a>.<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com5tag:blogger.com,1999:blog-8884584404576003487.post-56072206086659151992013-03-12T23:35:00.001-04:002013-03-12T23:41:03.709-04:00#kscope13Back in <a href="http://www.oraclenerd.com/2012/09/odtugkscope-content-chair.html">September</a>, I was asked, and agreed, to become to Content Chair for "The Traditional" track at Kscope 13. Like I mentioned there, I had been involved for the past couple of years and it seemed like a natural fit. Plus, I get to play with some really fun people. If you are ready to take advantage of Early Bird Registration, go <a href="http://kscope13.com/registration">here</a>. (save $300)
<br /><br />
Over the past few weeks we've finalized (mostly) the Sunday Symposium schedule. We're currently working on finalizing Hands-on-Labs (HOL).
<br /><br />
Beginning last year, we've had the Oracle product teams running the Sunday Symposia. This gives them an opportunity to showcase their wares and (hopefully) provide a bit of a road map for the future of said wares. This year, we have three symposia: APEX, ADF and Fusion Development and The Database and Developer's Toolbox.
<br /><br />
<b><a href="http://kscope13.com/content/symposiums#Fusion">ADF and Fusion Development</a></b>
<br /><br />
- <i>Oracle Development Tools – Where are We and What’s Next</i> - Bill Patakay, Oracle<br />
- <i>How to Get Started with Oracle ADF – What Resources are Out There?</i> - <a href="https://blogs.oracle.com/shay/">Shay Shmeltzer</a> and Lynn Munsinger, Oracle<br />
- <i>The Cloud and What it Means to Oracle ADF and Java Developers</i> - <a href="https://blogs.oracle.com/dana/">Dana Singleterry</a>, Oracle<br />
- <i>Going Mobile – What to Consider Before Starting a Mobile Project</i> - Joe Huang, Oracle<br />
- <i>Understanding Fusion Middleware and ADF Integration</i> - Frederic Desbiens, Lynn Munsinger, and <a href="https://blogs.oracle.com/shay/">Shay Shmeltzer</a>, Oracle<br />
- <i>Open Q&A with the ADF Product Management</i>
<br /><br />
I love that they are opening up the floor to questions from their users. I wish more product teams would do that.
<br /><br />
<b><a href="http://kscope13.com/content/symposiums#APEX">Application Express</a></b>
<br /><br />
- <i>Oracle Database Tools</i> - <a href="http://michaelhichwa.blogspot.com/">Mike Hichwa</a>, Oracle<br />
- <i>Technology for the Database Cloud</i> - Rick Greenwald, Oracle<br />
- <i>Developing Great User Interfaces with Application Express</i> - Shakeeb Rahman, Oracle<br />
- <i>How Do We Build the APEX Builder? </i> - Vlad Uvarov, Oracle<br />
- <i>How to Fully Utilize RESTful Web Services with Application Express</i> - John Snyders, Oracle<br />
- <i>Update from APEX Development</i> - <a href="http://joelkallman.blogspot.com/">Joel Kallman</a>, Oracle
<br /><br />
<i>(If you see Joel Kallman out and about, make sure you you mispronounce <a href="http://joelkallman.blogspot.com/2009/11/apeks.html">APEX</a>).</i> This is a fantastic group of people (minus Joel of course). Not mentioned above is the affable <a href="http://dpeake.blogspot.com/">David Peake</a> who helps put all this together. The community surrounding APEX is second-to-none.
<br /><br />
Finally, The Database and Developer's Toolkit. I'm partial to this one because I've been involved in the database track for the past couple of years. Like last year, this one is being put together by <a href="http://krisrice.blogspot.com/">Kris Rice</a> of Oracle. There are no session or abstract details for this one as it will be based mainly on the upcoming 12c release of the database. However, we do have the list of speakers lined up. If you could only come for one day of this conference, Sunday would be the day and this symposium would be the one you would attend.
<br /><br />
This symposium will start off with Mike Hichwa (above) and then transition to the aforementioned (too many big words tonight) Mr. Rice. He'll be accompanied by <a href="http://www.thatjeffsmith.com/">Jeff Smith</a> of SQL Developer fame, <a href="https://blogs.oracle.com/optimizer/">Maria Colgan</a> from the Optimzer team and <a href="asktom.oracle.com">Tom Kyte</a>.
<br /><br />
How'd we do? I think pretty darn good.
<br /><br />
Don't forget to sign up. Early Bird Registration ends on March 25, 2013. <a href="http://kscope13.com/registration">Save $300</a>.
<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com0tag:blogger.com,1999:blog-8884584404576003487.post-29094758141855699502013-02-21T20:53:00.000-05:002013-02-21T20:55:36.381-05:00Run Scripts in SQL DeveloperI <i>finally</i> decided to save a script that cleans out a couple of tables for me.
<br /><br />
Now I have a script, how do I run it in SQL Dev? In SQL*Plus, I would run it like <span class="code">@clean_tables</span>. Two things to note there, 1, I didn't have to put the extension on the file and b, I assumed SQL*Plus was running from the directory where my file was located. If I was running the script from a different directory, I would have to use either a relative path...or something, but I digress.
<br /><br />
I wanted to be able to run my script in a SQL Developer worksheet. How?
<br />
<pre class="code">
@clean_tables
Error starting at line 38 in command:
@clean_tables
Error report:
Unable to open file: "clean_tables.sql"
</pre>
<br />Twitter. <a href="http://www.thatjeffsmith.com/">Jeff Smith</a> hangs out there, a lot. He supposedly has a real job as the Senior Assistant Principal Skinner Product Dude for SQL Developer at Oracle. Crazy title, I know. Back to Twitter.
<blockquote class="twitter-tweet"><p>@<a href="https://twitter.com/oraclenerd">oraclenerd</a> working directory would be directory of parent file</p>— Jeff Smith (@thatjeffsmith) <a href="https://twitter.com/thatjeffsmith/status/304762379827302400">February 22, 2013</a></blockquote>
<script async src="//platform.twitter.com/widgets.js" charset="utf-8"></script>
<br /><br />
Since he lives there (Twitter) (and I'm glad he does), I got an immediate response. Yay for Jeff.
<br /><br />
Wait, what? Parent file? WTF are you talking about?
<br /><br />
(I then remove the snark and try to put more details)
<br /><br />
(oh, and I don't like that I can't just embed a single tweet...sorry, their fault, not mine)
<br /><br />
<blockquote class="twitter-tweet"><p>@<a href="https://twitter.com/thatjeffsmith">thatjeffsmith</a> "@ test(.sql)" I just want to know where to put test.sql.</p>— oraclenerd (@oraclenerd) <a href="https://twitter.com/oraclenerd/status/304762730206871552">February 22, 2013</a></blockquote>
<script async src="//platform.twitter.com/widgets.js" charset="utf-8"></script>
<br />
Two seconds later:
<br /><br />
<blockquote class="twitter-tweet"><p>@<a href="https://twitter.com/oraclenerd">oraclenerd</a> i think this, Tools > Preferences > Database > Worksheet > Select default path to look for scripts. Please test :)</p>— Jeff Smith (@thatjeffsmith) <a href="https://twitter.com/thatjeffsmith/status/304764069242621952">February 22, 2013</a></blockquote>
<script async src="//platform.twitter.com/widgets.js" charset="utf-8"></script>
<br />
Tested, and it works. Yay for me. Yay for Jeff.
<br /><br />
<img style="margin-left: 25px;" src="https://lh4.googleusercontent.com/-4tDCi3f5T84/USbO9IZt7qI/AAAAAAACG3I/iL1b1bsL-Mw/s800/Screenshot%2520from%25202013-02-21%252020%253A27%253A53.png" />
<br /><br />
In case it isn't obvious, I'm being sarcastic. Jeff is a fantastic advocate for SQL Developer. Yes, he gets paid to do it, but he goes above and beyond on a daily basis. Oracle is lucky to have him.<div class="blogger-post-footer"><div><script type="text/javascript"><!--google_ad_client = "pub-3853911845992923";/* atom feed */google_ad_slot = "1428191201";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script></div></div>oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.com2