LewisC’s An Expert’s Guide To Oracle Technology

Ok, I know you you’ve heard of them. Heck, in some cases, you’ve been beaten over the head with them. So why   aren’t you using them?

I’ve heard some people saying that it’s easier to code without bind variables. Really? Concatenating strings,   potentially with embedded quotes and/or varying formats, is easier than using bind variables?

I got an email the other day from a reader who asked to remain anonymous. He said that he read that he should use   bind variables and understood why but wasn’t sure he understood the concept of actually implementing code with   bind variables.

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

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.

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.

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.

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.

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.

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.

Get the latest source from the ORA Tweet project page at sourceforge.

I had some free time this last Saturday night (after the family was all in bed) and I wanted to do a little mini-project. I don’t get to do enough PL/SQL anymore so I like to look for utilities and smaller fun things to code. While I was thinking about it, I was also following along on twitter to the posts that the people I follow had recently made.

I decided to put together a twitter procedure that would let me make posts from the database.

Welcome to my blog. My name is Lewis R Cunningham and I’m an Oracle geek. I love playing with Oracle, working with Oracle and sharing what I know about Oracle. In my travels as a consultant and employee, I have found that, in the Oracle world at least, managers, project leads, etc. often have to make decisions regarding their projects, employees and day to day operations with very little, and/or erroneous, information. These decisions often negatively impact the business bottom line and ongoing projects. The lack of information can be caused by an incomplete team, a lack of communication inside the team or even ego or attitude problems.

My goal with this blog will be to remove some of the mystery surrounding Oracle technology and to describe ways to integrate that technology in various projects and business areas.

From the Database Geek.

As regular readers of my blogs know, one of the things I am is a non-absolutist. I try to never say never or always. Take WHEN OTHERS. I have worked in places where using WHEN OTHERS was banned. I think that’s silly. Nothing wrong with a WHEN OTHERS that logs an error. Right?

We recently had a situation that did just that. The problem was that the exception handler logged the SQLCODE and the SQLERRM (no stack info). This is an old piece of code being updated for some new functionality. Not a complete re-write, just some changes.

From the Database-Geek.

Continuing with the parser, begun week in PL/SQL Parser Part 1 and PL/SQL Parser Part 2, today I am going to modify the code to account for keywords, operators and data. By data, I don’t mean strings. I mean anything not a keyword, not a comment and not an operator. Data may be a quoted string (which we accounted for in Part 2), but it is also non-language functions and procedures. If you call a user defined procedure, that procedure call is considered data (at least it is here, for now).

In the code presented below, I have done several things.

Does Cloud Computing Change Anything?

Cloud computing, aka utility computing, aka SaaS, aka PaaS, aka IaaS, etc. is definitely the buzz word du jour (or buzz word 2009).
Cloud computing is pretty much whatever a particular vendor wants it to be as long as it will allow them to be 100% buzz word compliant.
For the sake of this article, let's say that cloud computing is a service based offering that allows dynamic allocation of virtualized resources from remote

From the Database-Geek.

Continuing with the parser, begun last week in Building a PL/SQL Code Parser Part 1, today I am going to modify the code to allow for comments. Rather than dive back into code already covered, I will discuss what I have added and then provide the full code below.

I changed my sample code and test proc to a new format:

declare

  v_string varchar2(32000) :=
  'CREATE OR REPLACE PROCEDURE yada' || chr(10)  ||
      ' AS ' || chr(10) ||
      ' /* This is a comment */  ' || chr(10) ||
     ' BEGIN' || chr(10) ||
     '   DBMS_OUTPUT.PUT_LINE(''string with a space'' ||' || chr(10) ||
     '   ''string with emb''''edd''''''''ed quotes'');' || chr(10) ||
     '   -- second_func(''test'');' || chr(10) ||
     ' END;' ;

begin

  v_string := lrc_plsql_parser.parse_line(v_string);

END;

Notice that in addition to the comments, I have added line feeds.

The OTN license specifically says “The programs may be installed on one computer only, and used by one person in the operating environment identified by us.” So if you install Oracle on a server and more than 1 person is using the server to develop, technically you need a license.

But the agreement also says that if you fail to comply with it, Oracle has the right to terminate the agreement and you must then destroy the software. But there is no penalty or fee to worry about if you ar

Bind variables are used to ease code maintenance and to save memory and processing on the server. When you save memory and processing power, you improve the overall performance of the server. The inner details of how this saves memory has been enumerated in other places on the web. This article is designed to help developers users bind variables when running queries against Postgres Plus Advanced Server.

What are bind variables?

The easiest way to think of a bind variable is to consider it just another variable. Instead of it being a variable to be used by your application (and whatever language you happen to be using), think of it as a variable to be used by SQL.

I started working on a task for a project and decided that there were some very good properties that I could use to help me teach people PL/SQL and XML. I expanded a bit on the idea and ended up with a scripting language, implemented in XML, that can be passed into a PL/SQL procedure. The script can execute stored procedures, it has looping logic, conditional (CASE) logic, user defined variables, etc.

The nice thing about it is that it’s very easy to read and modify. The XML structure is also very easy to read.

Oracle provides a lot of XML functionality in the database. This entry will cover the different tools Oracle provides and future entries will get into the details of each.

The three primary XML tools that oracle provides are: XML Developers Kit (XDK), XML DB and SQL Extensions for XML (SQLX and XMLType). Below is a definition of each.

XDK

The XDK is a complete XML development toolkit that was introduced for 8i. I think the first version was labeled XDK9i but the toolkit actually was available for 8i. I remember using the beta product.

In Part 1 of this series, we talked about how Oracle objects compare to Java and how to create Oracle Objects. In Part 2, I covered we covered object comparison and type inheritance. Today, we’ll talk about polymorphism and type evolution.

Like parts 1 and 2, this will be a technical discussion.

Polymorphism

First a definition. What is Polymorphism and why is it important? One of the best definitions I have found is this link at OnJava. You can skip the part about object serialization. Not really that important to Oracle’s OO as you’re already in the database.

Let’s go back to our (most basic) calculator example.

CREATE OR REPLACE TYPE Calculator AS OBJECT (
  value NUMBER,
  MEMBER PROCEDURE print,
  MEMBER PROCEDURE add1( p_amt IN NUMBER ),
  MEMBER PROCEDURE subtract( p_amt IN NUMBER )
  )
  NOT FINAL;
/

CREATE OR REPLACE TYPE BODY Calculator AS
  MEMBER PROCEDURE print IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( TO_CHAR( value ) );
  END;

  MEMBER PROCEDURE add1( p_amt IN NUMBER )  IS
  BEGIN
    value := value + p_amt;
  END;

  MEMBER PROCEDURE subtract( p_amt IN NUMBER )  IS
  BEGIN
    value := value - p_amt;
  END;

END;
/

And our Advanced Calculator:

CREATE OR REPLACE TYPE AdvancedCalculator UNDER Calculator (
  pi FLOAT,
  CONSTRUCTOR FUNCTION AdvancedCalculator
     RETURN SELF AS RESULT ,
  MEMBER PROCEDURE multiply( p_amt IN NUMBER ),
  MEMBER PROCEDURE divide( p_amt IN NUMBER ),
  OVERRIDING MEMBER PROCEDURE print
   )
NOT FINAL;
/

This isn’t exactly the same AdvancedCalculator.

This continues the blog began in Part 1 of A Day with Ralph Kimball.

So, on to the seminar! Please remember that this isn’t what Ralph said as much as it’s my interpretation of what Ralph said. I’m trying to explain what you’ll get from his seminar but it’s through my eyes not yours. That’s why this is not a replacement for his seminar. Hopefully, I will induce you to attend if he makes it to your town. You should go just so that you can tell me where I got it wrong if for nothing else.

When we last saw our intrepid heroes they were devouring their lunch and asking Ralph inane questions.