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/cyc3x2 #
• 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.