Database Geek Blog

• DB Geek says: Twitter Weekly Updates for 2009-06-21 http://tinyurl.com/m5asn2 #
• cannot sign into his toolbox.com account to post a blog entry. Can anyone else sign in or is it just me? #
• RT @eddieawad [Video] Stanley the ACE Vest at ODTUG Kaleidoscope 2009 http://ff.im/-4hA1Q #
• Is ambassador for bryson stewart, what to know before selecting owb. #odtug s365 http://myloc.me/51Zp #
• #odtug #att still sucks http://myloc.me/520M #
• my session, cloud computing with oracle and amazon, #odtug s148, start at 1pm. Live cloud demo! http://myloc.me/5272 #
• #odtug s365 the biggest benefit of any graphical etl tool is that it is self documenting, cutom tranforms break that http://myloc.me/529x #
• Thanks Chris.

• DB Geek says: Twitter Weekly Updates for 2009-06-14 http://tinyurl.com/m4h9s2 #
• Ever feel like life is an RPG and you picked the wrong class? #
• I’ve had to reboot my blackberry 3 times so far today. Anyone know of any BB problems going on? #
• @Matt_Holt Hi Matt. Cool. I’ll be tweeting while there. in reply to Matt_Holt #
• RT @Matt_Holt saw a black honda pilot with lic plate JEDIH8R w/ darth vader and ‘got vader?’ stickers in window. seems a bit much. #
• AT&T sucks. I can’t keep a 3g connection for more than a couple of minutes at a time. #
• @aejes ROFL!

Well, it’s not exactly new. It’s a product called Optim Development Studio. This product used to be called Data Studio Developer and also sometimes refered to as DB2 Data Studio.
Yes folks, IBM has joined the PL/SQL band wagon. With DB2 9.7, you can now develop PL/SQL applications that run within the database. There are limits to the support (inherited from enterprisedb) but it does support packages and plenty of Oracle syntax. I’ll have more on this in the future.
Optim can develop PL/SQL in DB2 but it can also connect to Oracle databases and explore or compile code.

• Blog Talk: Comment by shekhar on Learn PL/SQL: Declaring Variables, Named vs Anonymous Blocks http://tinyurl.com/l69ac2 #
• Anyone else having a problem connecting to tahiti.oracle.com? #
• This site is experiencing technical difficulty. We are aware of the issue and are working as quick as possible to correct the issue. #
• This should not be allowed. RT @abcactionnews Death row inmate sues for murder victim’s truck http://bit.ly/Fw8Ve #
• Blog Talk: Comment by LewisC on Learn PL/SQL: Declaring Variables, Named vs Anonymous Blocks http://tinyurl.com/lsjxny #
• Blog Talk: Comment by chet on The Purpose of IT and Who is your Customer http://tinyurl.com/kslpuw #
• Blog Talk: Comment by Tom Falconer on The Purpose of IT and Who is your Customer http://tinyurl.com/nvm763 #
• DB Geek says: Twitter Weekly Updates for 2009-06-07:
  DB Geek says: Twitter Weekly Updates for 2009..

From The Database Geek

I like playing with new tools and software so I thought I would start a new weekly series, Cool Tool of the Week. I will just cover a tool that I have used or discovered that I think is cool. I’m not necessarily endoring it, just giving it a little publicity so that others will know about it and can give it a try.

This week is the open source data modeling tool Open ModelSphere. This Open Source project is available for windows with a GPL license. It was originally known as SilverRun. I used this windows-based CASE tool when I was at Yale.

• DB Geek says: Twitter Weekly Updates for 2009-05-31 http://tinyurl.com/n3h8ru #
• @kissmygumbo Google for ugly feet and send one of those. Hairy, big yellow, curly toe nails. Now that’s the way to start a week! in reply to kissmygumbo #
• @jpiwowar My whole family, including in-laws have the same. Thanks Disney Cruise Lines! in reply to jpiwowar #
• @cspenn Marking things older than 1 month done - is that a GTD thing? Sounds like a good idea. in reply to cspenn #
• Blogged “Documentation is a waste of time. Or is it? ” http://it.toolbox.com/trd/46/2/31972/3/ #
• Nice!

• DB Geek says: Twitter Weekly Updates for 2009-05-24 http://tinyurl.com/p6md6s #
• Blog Talk: Comment by “Yasser Mohd” on Learn PL/SQL: Procedures and Functions http://tinyurl.com/qvvw75 #
• Blog Talk: Comment by Yasser Mohd on Learn PL/SQL: Procedures and Functions http://tinyurl.com/qguv8l #
• Cloud Post: Using and Managing AWS - Part 6: SSH Key Pairs: http://tinyurl.com/poffz3 #
• Blog Talk: Comment by kaleesh on What is the difference between Oracle, SQL and PL/SQL? http://tinyurl.com/phfpnr #
• Blog Comments: Comment on Ora_Tweet - Tweet From Oracle, A PL/SQL Twitter API by Barry Cooper: Hi .. http://tinyurl.com/mr5z75 #
• Blogged “Part 2 of Data Mart Design” http://it.toolbox.com/trd/46/2/31935/3/ #
• Blogged “PL/JSON v0.6.2 on SourceForge” http://it.toolbox.com/trd/46/2/31936/3/ #
• Blogged “PuTTY and SSH for Newbies” http://it.toolbox.com/trd/46/2/31937/3/ #
• @oraclenerd Can’t do rac in aws.

• DB Geek says: Twitter Weekly Updates for 2009-05-17 http://tinyurl.com/ogkrhx #
• RT @msutic Public Oracle Yum server http://public-yum.oracle.com/ works great. Finally I don’t have to bother searching for various packages #
• Cloud Comments: Comment on Amazon Web Services - Amazon DevPay by Gary: “billed desperately”
  Don.. http://tinyurl.com/p75mqj #
• Cloud Comments: Comment on Amazon Web Services - Amazon DevPay by Lew: heh. I guess sometimes it’.. http://tinyurl.com/pn2tl4 #
• DB Geek says: Designing the Data Mart - Part 2 http://tinyurl.com/oenjj5 #
• Blog Comments: Comment on Designing the Data Mart - Part 1 by Designing the Data Mart - Part 2 | D..

Continuing from Part 1.

So now we have our transactional model and a basic user story:

Our first request from the business for our data mart is that they want to be able to query all of the orders by date, by customer and/or by region (state, city or country). They want to be able to aggregate (sum and average) across those items.

The first thing we need to do is talk to the business and find out exactly what that request means. Do the users want to see information about daily orders in general?

• DB Geek says: Twitter Weekly Updates for 2009-05-10 http://tinyurl.com/otrppu #
• Playing with FreeNAS and iSCSI today. Very nice! #
• My life is now complete. @evernote The 3 words you’ve wanted to hear us say: Evernote…for…BlackBerry http://twurl.nl/0vtn6a #
• @oraclenerd Yep, That’s me, Eddie Awad, Arup Nanda, Mark Rittman, Tim Hall and Brad Brown. We did a BOF at OOW’08. in reply to oraclenerd #
• @oraclenerd Birds of a feather. in reply to oraclenerd #
• I played Men At Work:Who can it be now for my 6 year old. He now has it stuck in his head and can’t stop singing it.

As I mentioned a while back (a loooong while back), I have been thinking about writing up how I design data marts. The problem with that is that it is a huge topic. Even converting an existing schema (which doesn’t always exist) to a data mart (star schema style), still takes plenty of behind the scenes data analysis and prepwork. Still, I am not going to take a shot at it.

I could start with a laundry list of requirements but I don’t think that would be interesting to very many people.

• DB Geek says: Twitter Weekly Updates for 2009-05-03 http://tinyurl.com/cg2ew3 #
• This week is a week of documentation and data analysis. #
• @dannorris Hey, congrats! I tried to post a congrats on your blog but my corporate firewall blocks disqus. #
• RT @TheOnion Nation Ready To Be Lied To About Economy Again http://bit.ly/wV7Zh #
• If you’re not getting enough swine flu info via @cdcflu, the news, radio, etc, you can get it on your mobile: http://m.cdc.gov #
• @topperge A 2 hour lunch every day? I went into the wrong business. in reply to topperge #
• Another day of data analysis and documentation. I’ve already been crazy productive today!

It was pointed out to me that I had a few errors in the previous release.  Those have been fixed and the install should go a lot smoother now.  No additional functionality here, just bug fixes.  If you tweaked the previous release to install it, you don’t need this one.

As always, you can download the new code directly from the PL/JSON SourceForge page.

Change Log:

Version: 0.6.2
Fixed exit commands in various code files
Removed the “CREATE OR REPLACE” on the string_handler.printf function

Version: 0.6.1
Added missing string_handler package

Version: 0.6
Added support for creating JSON from text input

Version 0.5
Initial release
Includes a JSON data type
Can create tables and columns of JSON
Supports API creation of JSON data type

LewisC

• DB Geek says: Twitter Weekly Updates for 2009-04-26 http://tinyurl.com/d8c5od #
• Blog Talk: Comment by garcia phillip on 10 Things About Telecommuting Everyone Should Know http://tinyurl.com/cgg7yn #
• Blog Talk: Comment by “Brian Tkatch” on What is the difference between Oracle, SQL and PL/SQL? http://tinyurl.com/cv9v9f #
• Gave my son his netbook yesterday (for b-day). Totally unimpressed. Said he wanted a toy instead. ;-( #
• @jfroebe The wife claimed it. in reply to jfroebe #
• @jpiwowar He’ll start the next pirate bay. heh in reply to jpiwowar #
• @oraclenerd No he’s spolied. Just kidding. I hope they grow out of it. Very frustrating. in reply to oraclenerd #
• Awful but funny.

From the Database Geek.

Part of the ANSI SQL standard calls for an INFORMATION_SCHEMA. This schema contains a standardized data dictionary that is (or is supposed to be) common across various databases. Most database vendors offer a native data dictionary and a sub-set INFORMATION_SCHEMA (called info schema from this point on). Oracle is the only major database vendor (that I know of) that doesn’t even offer a subset of the info schema.

Oracle’s data dictionary (especially if you include the V$ views in that) is the most robust of all the databases I have used.

From the Database Geek.

PL/JSON v0.6.1 has now been released to Sourceforge.net. The reason for the .1 is that I forgot a dependency on the 0.6 release.

Anyway, PL/JSON can now create JSON via the API or through parsing a text string. The parser is implemented as a stand along package. That means you can write your own parser (or use an external procedure) if you don’t like mine. This will make it very extensible. I also plan to add PL/SQL callbacks to the parser at some point for custom processing.

Example of parsing:

SET SERVEROUTPUT ON

DECLARE
  v_json json;
  v_json2 VARCHAR2(32000) :=
   '{
      "abc": "dpkxvdvvcxz\"vxasasa   ",
      "def": 12345,
      "ghi":{"isit":true, "nope":false,"denada":true },
      "jkl": [1234, 45678.99, 121211, 21323232, 00000]
   }';
BEGIN

  v_json := json(v_json2);
  v_json.print;
END;
/

{
"abc":"dpkxvdvvcxz\"vxasasa   ",
"def":12345,
"ghi":{
"isit":true,
"nope":false,
"denada":true}
,
"jkl":[1234,45678.99,121211,21323232,0]}

Output from PL/JSON checks out on JSONLint.

This is a guest post. If you would like to write a guest post, please leave a comment or email me. I would prefer posts to be database related but career topics and other topics that would interest the majority of my readers will also be accepted. Readers, let me know what you think of guest posts. LewisC

Using a Degree to Ward Off a Layoff

Millions of people have been affected by the recent recession and the global financial crisis that perpetrated it. There have been countless loss of jobs, many more pay cuts, and an equal number of loss in bonuses and raises.

• DB Geek says: Stupid Database Tricks - Shutdown the server from a stored procedure: From the Databa.. http://tinyurl.com/cq8vgx #
• DB Geek says: Twitter Weekly Updates for 2009-04-19 http://tinyurl.com/dc2dxl #
• Blog Comments: Comment on Ora_Tweet - Tweet From Oracle, A PL/SQL Twitter API by When Twitter meet.. http://tinyurl.com/d93cbd #
• Excellent news! via @serge_a_storms Oracle buys Sun - http://is.gd/trnH #
• @fuadar “mysql could replace xe” Bite your tongue! in reply to fuadar #
• Larry Ellison: . “We’ll engineer the Oracle database and Solaris operating system together.” #
• ZDNet: Oracle with Sun appears to be the Apple of the enterprise #
• Oracle gets: mysql, java, solaris, storage hw, servers. Aything else?

From the Database Geek.

I recently needed to query all of the arguments to the various stored procedures, functions, packaged objects and types. Oracle provides all of this information in the data dictionary in the ALL_PROCEDURES and ALL_ARGUMENTS views. The problem with this is that it’s kind of kludgey to suck out all of the pertinent data.

One problem is that arguments for procedures and functions in packages is access differently than arguments for stand alone procedures and functions. What I did was combine the two calls into a single pipelined function that returned the data I needed.

• Blog Talk: Comment by Krystle Cheah on Oracle Streams Configuration: Change Data Capture http://tinyurl.com/cr6pn8 #
• DB Geek says: Twitter Weekly Updates for 2009-04-12 http://tinyurl.com/dju9ak #
• Another week of informatica training. I’m in the dev II, advanced developer, training. Supposed to be intense. Sweet! #
• Blog Talk: Comment by LewisC on Oracle Streams Configuration: Change Data Capture http://tinyurl.com/clc8b5 #
• I’m starting a new group: People for the Ethical Treatment of URLs (PETU). Save the short URL! http://bit.ly/SGwID #
• Before class (as in days), informatica sends out an email with links to test the connections. We still ALWAYS spend an hour fixing people.

From the Database Geek.

I’m not even going to try to explain why I wrote this. Let’s just say that it comes in handy on occasion.

What this very simple procedure does is use DBMS_SCHEDULER to execute a shutdown command in windows. This could easily be adapted to Unix and/or Linux.

Use at your own risk. By default you get a 30 or 60 second delay (depending on windows version, I think) before the shutdown/reboot.

If you pass FALSE in to the procedure, or don’t pass anything, the server will shutdown. If you pass in a TRUE, the server will reboot.

• DB Geek says: Twitter Weekly Updates for 2009-04-05 http://tinyurl.com/cv5zlq #
• Just a thought. Now that there is no more IBM=>SUN, what do you think would happen if MS bought Sun? Win7 on Sparc? #
• DB Geek says: Calculating Business Days and Business Days Between http://tinyurl.com/czpehr #
• @neilkod Thanks. My current warehouse doesn’t need it. I have a function I wrote a few years ago that calculates fed holidays. in reply to neilkod #
• My picture went away. What’s up with that? #
• Looks like a lot of people’s picture has gone away. A new kind of fail whale? #
• Oracle ApEx Webinar.

Paul Gallagher, of the Tardate blog, emailed me this morning (or I guess it was last night) and sent me some code to enhace ORA_Tweet.  He wrote a package to call out to an is.gd API to convert a long URL to a short URL.  The really cool thing is that he used a regular expression to find matches INSIDE the comment body.  That means that ANY URLs will be replaced with a short URL, you don’t have to call the API individually.

His API is easily called and can be used for projects other than just ORA_Tweet.  Check out his write up on the SHORTURL package:  Tweeting from your database with short urls

I updated the ora_tweet code so you can download the entire set of code if you would like.

From the Database Geek.

I recently had a requirement to populate the day dimension of a data mart (I won’t put all of the code here as it’s pretty large). That’s not that big deal but part of the requirement was to set several columns: BUSINESS_DAY_FLAG, BUSINESS_DAY_NO and BUSINESS_DAYS_REMAINING_NO.

• The BUSINESS_DAY_FLAG is Y is the date is MON-FRI and N is the date is SAT or SUN.
• BUSINESS_DAY_NO is the business day of the month. There are 5 business days per week so if the month started on a monday, the second monday would be business day 6.
• BUSINESS_DAYS_REMAINING_NO is the number of business days remaining in the month.

• @bex That’s “Sir Tim”. in reply to bex #
• RT @Blackberry_News Skype for BlackBerry coming in May: An official “for BlackBerry” Skype application.. http://tinyurl.com/cyc3×2 #
• If you use JSON from a database, check out PL/JSON (v0.5) - a PL/SQL implementation of JSON. http://bit.ly/deF7D #
• IBM sucks. They lay off 5000, move the jobs to India and now they’re asking for US stimulus dollars. http://bit.ly/19AekX #
• Stupid question? After thinking about it, I have to wonder why having skype on a phone is a good thing? It’s already a phone! #
• @Das_mole @alannd I use skype for my home office but never really needed a phone on my phone.

Recently I posted an article about ORA_Tweet, an Oracle Twitter Client. I was asked by someone reading the code why I put several variables in the BODY of the package rather than the SPEC. The question was posed something like this:

Why not put the variables in the package spec where they are modifiable? That would involve less maintenance.

I specifically put them in the body so that they are not modifiable. I don’t see public global variables as a particularly good thing. To understand why, let’s take a look at the benefits of a package.

First, you can group logical functions and procedures together.

Hi all.

This post is just a catchup of what I have been doing recently.

I cleaned out my email inbox. I had 5000 unread emails. I have a problem that if I get an email that is not spam but is not critical, I put it aside “to read later”. Sometimes, I actually do go back and read them. More often, I never see it again. I would *LIKE* to respond to every email and blog post that I get. Unfortunately, I just don’t always have time. By the time I do have time, I have plenty of newer posts and emails.

• Blog Talk: Comment by Terence Fernando on Oracle and XML In Action - A Real World Example http://tinyurl.com/cyqx9m #
• DB Geek says: Twitter Weekly Updates for 2009-03-22 http://tinyurl.com/cljmws #
• Blog Comments: Comment on Ora_Tweet - Tweet From Oracle, A PL/SQL Twitter API by Bob Watkins: Not .. http://tinyurl.com/c2ua5b #
• Blog Comments: Comment on Ora_Tweet - Tweet From Oracle, A PL/SQL Twitter API by Lewi: Bob,

  Goo.. http://tinyurl.com/c9z4p2 #

• Smooth move: Online Backup Company Carbonite Loses Customers’ Data, Blames And Sues Suppliers. http://bit.ly/mHxqK #
• @fritshoogland Licensing and support. Will Oracle support the db on a non-standard AMI? I don’t know but I wouldn’t think so. in reply to fritshoogland #
• Web minion.

From the database geek.

Update March 29, 2009:  PL/JSON is now available as an open source project (MIT License).  Read the FAQ at http://oracleoss.com

Do you use JSON? Do you use Oracle? How would you like to use JSON in Oracle? How would you like to store it, generate it and read it? I am creating a new JSON data type that will work like XMLType.

I am assuming you are familiar with Oracle (or you probably wouldn’t be reading this blog). If you are not familiar with JSON it is JavaScript Object Notation, a lightweight data-interchange specification. Think of it as a replacement to XML.

• Blog Talk: Comment by LewisC on Twitter From Oracle - ORA_TWEET http://tinyurl.com/cpkqnx #
• Blog Talk: Comment by Jake on Twitter From Oracle - ORA_TWEET http://tinyurl.com/dazkgt #
• DB Geek says: Twitter Weekly Updates for 2009-03-15 http://tinyurl.com/cdvlch #
• @dannorris I had three papers accepted for IOUG but just realized I won’t be able to make it. It’s only an hour from my house, too. ;-( in reply to dannorris #
• @chriscmuir Thirty-odd going 3 - the best direction to be going. in reply to chriscmuir #
• Apple is evil reason#153: http://bit.ly/HprjP apple requires 3rd party ipod headphone license fees #
• @cloudberryman I haven’t. Thanks for the info. I will look into it.