So what can I say? One of the phrases I hate most is “We’ve had problems with that.” I have seen that, or some form of that, question used as an excuse to not try new features, to not use best practices, keep a head in the sand, stick to Oracle7 features, etc.

As an example, in my early days with data modeling, I once created a new schema. It wasn’t a very big schema; probably 10 tables or less. I spent a lot of time doing the analysis to make sure I got the data structures correct. The ERD was sent around and got approval from everyone.

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.

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.

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.

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.

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.