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.

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.

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.

I get a lot of email, and comments on blog entries, asking questions about many different topics. I am going to try to answer some of the more common questions as FAQs. Today I am starting with some NUMBER FAQs. I am specifically talking about Oracle and I am specifically talking about the NUMBER data type, not numerics in general. I’ve written about NUMBERs before.

Q: What is the difference between an INTEGER and a NUMBER.

A: INTEGER is a subtype of NUMBER. INTEGER is declared as NUMBER(38,0). That means it is a “constrained” NUMBER.

LewisC’s An Expert’s Guide To Oracle Technology

An ITToolbox user recently asked a question on the EnterpriseDB discussion group, Oracle equilant UTL_I18N.STRING_TO_RAW IN ENTERPRISEDB.

Basically, Sreenivas asked which functions in EnterpriseDB could be used to implement dbms_crypto, hex_to_raw, string_to_raw, etc. I believe he is using EnterpriseDB Postgres Plus Advanced Server which is the product that gives Oracle Compatibility. The short answer to his question is that right now, there are no compatibility functions for those. The long answer is that you can re-implement that functionality using native PG functionality.

If you look at Sreenivas’s message you can see how his existing code works.

Do you remember Foxpro? xBase? Clipper? I’m an old clipper head from way back. Anyway, I ran across something in my recent internet travels that caught my fancy. It may have the longest name of any utility I have ever seen.
FoxPro PostgreSQL Import, Export & Convert Software will transfer tables to and from PostgreSQL and FoxPro.
I haven’t downloaded it yet so I can say how well it works but it’s a neat idea. I’m guessing it can handle DBF files so that would make it compatible with dBase, Foxpro, Clipper and may other tools.

Stefan Arentz posted an entry on his blog, about a hack he’s done for PostgreSQL. He’s added a native UUID to the database. This is pretty slick.
Most of your users will never notice but it can help on databases that need it. He lists some advantages on the google code page:

Database Size – Using a native UUID type on a table that also has an index on the uuid column saves 47% space. This is good because there is less data on disk and in memory. Which really matters if you have potentially hundred of millions of records.

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 is a continuation of Object Orientation in Oracle. I guess I should have called it the Sorcerer of OOO. This is a technical entry.

Anyway, in part 1 I covered the basics of creating an Oracle object type and showing how it compared to a Java class. Today I’m going to cover the concepts of comparing objects and Inheritance. In part 3, I will cover type evolution and Polymorphism and in part 4 I will cover Object Views and Object Tables.

Comparing Objects

Many times, you will be working with multiple instances of the same object.

I could have called this OO In Oracle: A Techie’s View. But the Sorcerer of OO is a much cooler title. You have to pronounce OO as ooh! as in Ooh and Aah!

What is OO? OO stands for Object Oriented. OOD is OO Design, OOA is OO Analysis and OOP is Object Oriented Programming. Oracle is an ORDBMS, an Object Relational Database Management System. In this article, I am going to present a sample java class and show how you would implement the same class in Oracle. This is a technical article comparing Oracle objects with Java.