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.

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.

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.

LewisC’s An Expert’s Guide to Oracle Technology

Is it possible to build a PL/SQL parser using nothing but PL/SQL? To answer that question, I guess I need to define “PL/SQL parser”. What would be the intention of this parser?

I would like a way to parse a PL/SQL code block and let me determine some statistics: number of lines, number of keywords, number of DML statements. It would need to recognize comments. I also want to be able to format the code for pretty printing. I want to be able to apply user defined checks for adherence to coding standards.

When it comes to data issues (too many rows, no data found, etc), in Oracle stored procedures, I am used to having exceptions raised that I then handle. PL/pgSQL does not raise exceptions for the same conditions in the same way. The Postgres docs are pretty complete though and through some reading this weekend, I discovered a new keyword.
For example, assuming that I have this table (which is empty) in both Oracle and Postgres:

CREATE TABLE empty_table
(
  empty_col integer
);

In Oracle this procedure:

CREATE OR REPLACE PROCEDURE no_data_found_test
AS
  v_int_field INTEGER;
BEGIN

  SELECT empty_col
    INTO v_int_field
    FROM empty_table;

END;

When run:

BEGIN
  no_data_found_test;
END;

Produces an error:

Error starting at line 1 in command:
BEGIN
  no_data_found_test;
END;
Error report:
ORA-01403: no data found
ORA-06512: at "HR.NO_DATA_FOUND_TEST", line 6
ORA-06512: at line 2
01403. 

This is just a quick overview of the availability of programming for Postgres. With Oracle, your choice in the database is Java or PL/SQL. In general, PL/SQL is the preferred language.
One of the things that attracted me to Postgres was the ability to choose one of many languages to program in. I usually choose PL/pgSQL but if I can’t do something with that, it’s nice to be able to fall back to TCL, Perl or one of the many other languages supported by Postgres.
Here is a (incomplete, I’m sure) list of supported languages:

• PL/pgSQL – Standard Procedural Language for Postgres
• PL/TCL – TCL, Tool Command Language, easy to learn but powerful
• PL/Perl – Practical Extraction and Report Language, has grown way beyond its origins
• PL/Java – Java, meh
• PL/PHP – PHP: Hypertext Preprocessor, over hyped language
• PL/Python – Object Oriented, dynamic language.

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.

It looks like the job market for Oracle is opening up. I’m seeing many of my friends find new jobs and I’m doing a lot of interviewing at work. I hope this is a trend that continues. In this entry, I share my method for interviewing Oracle resources and provide some sample interview questions and answers for developers.

Interviewing anyone can be difficult. Interviewing technical resources is very difficult. To me, probably the hardest thing is pinpointing exactly what you want this person to do. What, exactly, will this person be doing in their day-to-day job?

I’m going to start a new daily entry in addition to my other entries. I’ll call it the Thought of the Day. It will be an interesting link or thought or joke or just something I read somewhere. It will usually pertain, somehow, to Oracle but like today it might not.

Today’s thought is a link to the best Bash Shell Script guide I’ve ever seen. It’s a reference and a tutorial. I’m no Linux guru and certainly no scritping guru but I really like this guide.

Advanced Bash-Scripting Guide

Thanks,

Lewis