Notice:

This page has been converted from a dynamic Wordpress article to a static HTML document. As a result, some content may missing or not rendered correctly.

PL/JSON Library ~ Wed, 26 Jan 2011 20:11:01 +0000

At work, I write a good bit of PL/SQL to support various web applications that I maintain. That is, I write a lot of web services directly in the database so that I have to maintain less code. Unless there is a good reason not to, and there aren't many, I default to using JSON as the output format of my web services. It's a simple format that is able to describe many types of data, and is very easy to parse in many different languages -- except PL/SQL. As of now, Oracle doesn't include a package/library for handling JSON. So I had been doing things like this:

SELECT "foo" INTO bar FROM dual;
json_output := '{ "bar" : "' || bar || "' }'; -- { "bar" : "foo" }
htp.print(json_output);

Clearly, that can get old really fast. It also leaves a lot of room for error. Luckily, someone else has addressed these problems by writing and releasing the PLJSON library. After installing PLJSON, and poking around it, I learned that it was missing one important feature for me -- JSONP output. So I created a little patch and submitted it. A new version of PLJSON was released last week that includes my patch. So let's take a look at how to use it.

First, let's establish some data to work with:

First Name	Last Name	Sex	Age	Email
John	Doe	Male	42	john.doe@example.com
Jane	Doe	Female	38	jane.doe@example.com
Billy	Bob	Male	26	billy.bob@example.com
Mary Jane	Lynn	Female	30	maryjane.lynn@example.com

Let's call this table of data "contacts". We will query this table to get the data, iterate through it to build a JSON array of objects, and output the result to an HTTP request as JSONP. Here is the package body for doing that:

CREATE OR REPLACE PACKAGE BODY jsonp_example IS
  PROCEDURE get_contacts(js IN VARCHAR2 DEFAULT NULL) IS
    json_obj json := json(); -- A basic JSON object
    json_ar  json_list := json_list(); -- A JSON array
    results  SYS_REFCURSOR;
    TYPE contact_record IS RECORD(
      fname   VARCHAR2(60),
      lname   VARCHAR2(60),
      sex     VARCHAR2(6),
      age     NUMBER(3),
      email   VARCHAR2(255));
    r contact_record;
  BEGIN
    OPEN results FOR
      SELECT * FROM contacts;

    LOOP
      FETCH results
        INTO r;
      EXIT WHEN results%NOTFOUND;
      -- Add properties to our basic JSON object
      json_obj.put('fname', r.fname);
      json_obj.put('lname', r.lname);
      json_obj.put('sex', r.sex);
      json_obj.put('age', r.age);
      json_obj.put('email', r.email);

      -- Add the JSON object to our JSON array
      json_ar.add_elem(json_obj.to_json_value);
    END LOOP;
    CLOSE results;

    -- Print to OWA with the JSONP format
    IF js IS NULL THEN
      json_ar.htp(jsonp => 'contacts_jsonp');
    ELSE
      json_ar.htp(jsonp => js);
    END IF;
  END get_contacts;
END jsonp_example;

Querying that package via HTTP will result in the following JSON (formatted here for clarity):

contacts_jsonp([
  {"fname":"John","lname":"Doe","sex":"Male","age":42,"email":"john.doe@example.com"},
  {"fname":"Jane","lname":"Doe","sex":"Female","age":38,"email":"jane.doe@example.com"},
  {"fname":"Billy","lname":"Bob","sex":"Male","age":26,"email":"billy.bob@example.com"},
  {"fname":"Mary Jane","lname":"Lynn","sex":"Female","age":30,"email":"maryjane.lynn@example.com"}
])

I think you'll agree that using the library is much easier than trying to build that string yourself. So, if you find yourself dealing the PL/SQL, and need/want to handle JSON data, do yourself a favor and install PLJSON to your schema.

* UPDATE *

Because of confusion in the comments, I have adjusted this post slightly. I changed the example procedure declaration to include a js parameter. This allows you to change the JavaScript function call that the procedure returns. The procedure will still default to outputting contacts_jsonp if no value is passed in for js.

So, given the change, here is how you would call the procedure with jQuery:

$.getJSON('http://example.com/exampleDB.jsonp_example.get_contacts?js=?', function(data) {
  console.log("Results:");
  console.dir(data);
});

Code, JavaScript, PLSQL, Technology

Comments

Ola said (2011-02-05 23:35:15 GMT):

Hello !

Thanks for the example above. All works just fine. Now I am tryning to get data back using jquery See my code here. I am not getting any data back from the server. The sql-statement has been parsed ( select * from contacts , from SGA ). What is wrong ?

HTP.print(''); HTP.print(''); HTP.print(' '); HTP.print(''); HTP.print('$(document).ready(function(){ '); HTP.print(' $("button").click(function(){ '); HTP.print(' $.getJSON("http://localhost:8080/myepgdad/jsonpexample.getcontacts", '); HTP.print(' function(data){ alert(data); } ); '); HTP.print('}); '); HTP.print('}); '); HTP.print(''); HTP.print(''); HTP.print(''); HTP.print('Let AJAX change this text'); HTP.print('Change Content'); HTP.print(''); HTP.print('');

Regards Ola

Ola said (2011-02-07 22:33:59 GMT):

Hi !

I am trying too get the values from the ouput ( contacts_jsonp ) with jquery but I an not able too get any result. Do you have any idea to solve that with $.getJSON ?

Regards Ola

James Sumners said (2011-02-07 23:44:01 GMT):

(Update: This was in reply to your second post. The first one got caught in my spam filter and I didn't see it until after I replied.)

You haven't given me much information for me to help you with. So here is a guess.

Let's assume your database procedure is accessible through the URL http://example.com/myDatabase.mySchema.jsonp_example.get_contacts. Then you could request, and parse, the data with jQuery like so:

var contacts_jsonp = function(data) { var i = 0, max = data.length;



for (i; i &lt; max; i += 1) {
    // Assuming you are using Firebug or Web Inspector
    console.log(&quot;Result &quot; + (i+1) + &quot; is:&quot;);
    console.dir(data[i]);
}


};

$.getJSON('http://example.com/myDatabase.mySchema.jsonpexample.getcontacts?callback=contacts_jsonp');

A couple things to note here:

1) The JSONP callback function must be a global function. It would be better to let jQuery handle it with a 'callback=?' parameter. But we have already defined 'contacts_jsonp' in the example, so I'm sticking with it.

2) This is probably going to fail if you just copy and paste the procedure from above. The above procedure does not expect any passed in parameters (i.e. the 'callback' parameter). So you would need to fix that problem. It was just a quickly conceived example.

James Sumners said (2011-02-09 23:44:35 GMT):

It sounds to me like you don't understand how JSONP works in the jQuery library. You should read the documentation.

Anyway, check out this jsfiddle for an example. Notice that the URL I query for data has a "callback=?" parameter. jQuery looks for parameters in JSON requests that are either "callback=someFunctionName" or "someParameterName=?" and recognizes that this request is a JSONP request. So, in the jsfiddle example, jQuery notices that we want JSONP because the "callback=?" parameter is present. jQuery then creates a random global function and substitutes that for the "?" in the parameter value. When the remote server sends its response it will include the function call that jQuery invented.

In the case being discussed here, we have told jQuery that the callback function is going to be named "contacts_jsonp" because the procedure is hard coded to return that function call.

Ola said (2011-02-11 13:31:59 GMT):

Example 1

HTP.print(' $.getJSON("http://blabla.net/kontakten/jsonpexample.getcontacts", function(json) { '); HTP.print(' var i = 0, '); HTP.print(' max = json.length; '); HTP.print(' alert("Length of response array: " + max); '); HTP.print(' for (i; i < max; i ++) { '); HTP.print(' console.log("Result " + (i+1) + " is:"); '); HTP.print(' console.log(data[i]); '); HTP.print(' } '); HTP.print(' }); ');

Result; Nothing happens, but in fireburg I find this under response

contacts_jsonp([{"fname":"John Doe Male 42 john.doe@example.com ","lname":"","sex":"","age":null,"email":""},{"fname":"Jane Doe Female 38 jane.doe@example.com ","lname":"","sex":"","age":null,"email":""},{"fname":"Billy Bob Male 26 billy.bob@example.com ","lname":"","sex":"","age":null,"email":""},{"fname":"Mary Jane Lynn Female 30 maryjane.lynn@example.com","lname":"","sex":"","age":null,"email":""}])

Example 2

HTP.print(' $.get("http://blabla.net/kontakten/jsonpexample.getcontacts", function(json) { '); HTP.print(' var i = 0, '); HTP.print(' max = json.length; '); HTP.print(' alert("Length of response array: " + max); '); HTP.print(' for (i; i < max; i ++) { '); HTP.print(' console.log("Result " + (i+1) + " is:"); '); HTP.print(' console.log(json[i]); '); HTP.print(' } '); HTP.print(' }); ');

Result; Alert Length of response array: 405

In console: Result 156 is: c Result 157 is: o Result 158 is: m Result 159 is: Result 160 is: " Result 161 is: , Result 162 is: " Result 163 is: l Result 164 is: n Result 165 is: .....

It print each character ? I thought it would print each object.

In fireburg I find this under response

Example 3

HTP.print(&#039;         $.ajax({    &#039;);

HTP.print(' type: "GET", '); HTP.print(' url: "http://blabla.net/kontakten/jsonpexample.getcontacts", '); HTP.print(' dataType: "text" , '); HTP.print(' success: function(msg){ '); HTP.print(' alert( "Data Saved: " + msg ); '); HTP.print(' } '); HTP.print(' }); ');

  In fireburg I find this under response

Example 4


   var contacts_jsonp = function(data) {

var i = 0,
max = data.length;
for (i; i < max; i ++) {
console.log("Result " + (i+1) + " is:");
console.dir(data[i]);
}
};

$(document).ready(function(){ $("button").click(function(){ $.getJSON("http://blabla.net/kontakten/jsonpexample.getcontacts?callback=contacts_jsonp");
});
});

Result: I can se under "Params" in Firebug callback contacts_jsonp but nothing in the console. It can't find the global function.
what is missing ?

Ola said (2011-02-08 22:00:36 GMT):

Hi !

I don't understand how this should work. This is my code.Does jquery match contactsjsonp in the url with the javascript var contactsjsonp or ? I have also too declare one in parameter in the procedure but I am not using this parameter in my code.

var contacts_jsonp = function(data) { var i = 0, max = data.length; for (i; i < max; i += 1) { console.log("Result " + (i+1) + " is:"); console.dir(data[i]); } }; $(document).ready(function(){ $("button").click(function(){

$.getJSON("http://localhost:8080/myepgdad/jsonpexample.getcontacts?callback=contacts_jsonp"); }); });

Let AJAX change this text Change Content

Regards Ola

James Sumners said (2011-02-11 13:55:14 GMT):

I have updated the article. I do not know how to be any clearer. One thing I can suggest, though, is that you write an external HTML document, JavaScript included, that queries your database to do your testing. Writing the JavaScript to the document from the database is adding in a confusing step.

James Sumners said (2011-02-18 20:00:33 GMT):

There, I fixed it. This post wasn't meant to be a lesson in all things PL/SQL and jQuery. It was written with the assumption that someone reading this would be familiar with these technologies beforehand.

I have provided as much of a complete example as I am going to. Everything you need to understand how to use the JSONP support in pljson is present. It doesn't matter what version of jQuery you are using. Please read the jQuery documentation if you don't understand that library.

Ola said (2011-02-18 14:22:32 GMT):

You can,t do this. It will cause PLS-00363

IF js IS NULL THEN js := 'contacts_jsonp'; END IF;

We have been tesing this (pl/sql json and jquery ) for a few days but this example wont work. Can you provide me a complete code example ( select data ... , create json objekt and then using getJSON too retrive objekt and print each objekt . What jquey version are you using ?

//Ola

Ola said (2011-02-18 22:02:07 GMT):

Now it works

I have too add this code after begin in get_contacts

 htp.init;
owa_util.mime_header('application/json', false);
owa_util.http_header_close;

and chane jquery 1.5 ( Unexpected token in attribute selector: '!'. ) -> jquery 1.3

Result 1 is:

age 42

email "john.doe@example.com"

fname "John"

lname "Doe"

sex "Male" Result 2 is:

age 38

email "jane.doe@example.com"

fname "Jane"

lname "Doe"

sex "Female" Result 3 is:

age 26

email "billy.bob@example.com"

fname "Billy"

lname "Bob"

sex "Male" Result 4 is:

age 30

email "maryjane.lynn@example.com"

fname "Mary Jane"

lname "Lynn"

sex "Female"

James Sumners said (2011-02-18 23:01:08 GMT):

In jQuery 1.5, the "cache" option for JSONP AJAX calls defaults to "false". This means it automatically appends an "" parameter to the URL. You can't name a parameter "" in PL/SQL. A temporary workaround is to set "cache:true" on the AJAX call. However, I have a path under review that will let you specify a name for the cache parameter. This is a much better solution because disabling the cache ensures data integrity. You can follow ticket #8305 to keep up with the status of this.

Please use pastebin.com to post your code. Just include a link to the paste in your comment here.

In regard to the output you have pasted, that is because you are not correctly building the JSON array. So you only have one element in the array.