|
|
JSON (JavaScript Object Notation) is a lightweight data format that is very well suited for transmitting data over the Internet. Despite the reference to JavaScript in its name, JSON is a language-independent syntax and native support for it has been includedin many modern programming languages. In fact, JSON is so popular nowadays that entire database management systems have built their record structure around it, for exampleApacheCouchDB.
As a Web developer who does a lot of work with PL/SQL, I have recently found myself creating PL/SQL procedures that output JSON data, and making these procedures available as RESTful HTTP services that are then consumed by Ajax functions in the front-end ofmy applications. This can be a bit of a pain, however, as I need to output the JSON data manually, making it very prone to errors and very difficult to debug in many cases.
Thankfully, an open source library for PL/SQL called PL/JSON is available that resolves some of the issues associated with working with JSON data in an Oracle application. In this post, you will learn how to install and use PL/JSON to work with JSON data inyour own PL/SQL applications.
Download and install the library
The latest version of PL/JSON, at the time of writing, is version 0.8.6. The compressed archive is less than 200KB in size. You can download PL/JSON from SourceForge athttp://sourceforge.net/projects/pljson/files/.Unzip the library to your hard drive and install it in a SQL*Plus session using the following command:
You should see the following output:
-----------------------------------
-- Compiling objects for PL/JSON --
-----------------------------------
PL/SQL procedure successfully complete
Type created.
No errors.
Type body created.
No errors.
Type created.
No errors.
Type body created.
No errors.
Type created.
No errors.
Type created.
No errors.
Type created.
No errors.
Type created.
No errors.
Type created.
No errors.
Type created.
No errors.
Package created.
Package body created.
Package created.
Package body created.
Type body created.
No errors.
Type body created.
No errors.
Package created.
Package body created.
|
Assuming you see no errors from running the install script, PL/JSON is now installed in your Oracle database.
Using PL/JSON
With PL/JSON installed, you can now start working with the traditional “Hello, world” example, using the following PL/SQL code:
1
2
3
4
5
6
7
8
9
10
| set serveroutput on
declare
jsonObj json;
begin
--Create a new JSON object, passing the JSON code as the constructor
jsonObj := json('{"greeting":"Hello World"}');
--Output the value for the JSON data with the key "greeting"
dbms_output.put_line(json_ext.get_varchar2(jsonObj, 'greeting'));
end;
/
|
This produces the following output:
As you can see from the above output, PL/JSON parses JSON data. But what if you want to create a JSON object without actually providing the data in JSON format. Take the following example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| set serveroutput on
declare
jsonObj json;
begin
--Use an empty constructor to create a blank JSON object
jsonObj := json();
-- Use the put procedure to add a string, number, boolean and null property to the JSON object
jsonObj.put('name', 'Joe Lennon');
jsonObj.put('age', 24);
jsonObj.put('awesome', json_bool(true));
jsonObj.put('children', json_null());
--Print the string representation of the JSON object (pretty-print)
jsonObj.print;
dbms_output.put_line('Am I awesome?');
--Use getters to retrieve the value of the boolean property "awesome" and print the result
dbms_output.put_line(json_ext.get_json_bool(jsonObj, 'awesome').to_char);
end;
/
|
The output for the above looks like:
{
"name" : "Joe Lennon",
"age" : 24,
"awesome" : true,
"children" : null
}
Am I awesome?
true
|
JSON Arrays
JSON objects can contain data in several types – number, string, boolean, null or array. At this point, you’ve seen how to work with the first four of these types, but not arrays. PL/JSON refers to these as a json_list. Working with json_list objects is verysimilar to working with a regular JSON object, as shown in the following example:
1
2
3
4
5
6
7
8
9
10
11
12
| set serveroutput on
declare
jsonArray json_list;
jsonObj json;
begin
jsonArray := json_list('[{"name":"Joe","age":24},{"name":"Jill","age":26}]');
jsonObj := json.to_json(jsonArray.get_elem(1));
dbms_output.put_line(json_ext.get_varchar2(jsonObj, 'name'));
jsonObj := json.to_json(jsonArray.get_elem(2));
dbms_output.put_line(to_char(json_ext.get_number(jsonObj, 'age')));
end;
/
|
The above code create a JSON array (or json_list) containing two objects with two properties, name and age. The first object has the name “Joe” and age 24, the second has name “Jill” and age 26. The get_elem function is used to return an item in the JSON array,and the to_json function casts this is a JSON object. In the above example, you first assign jsonObj to the first object in the JSON array, printing out the value of the name property for this object (in this case, the string value “Joe”). Next, you assignjsonObj to the second object in the array, and print out the value of the age property (in this case, age is 26). The output for this example is as follows:
To wrap up this post, let’s have a look at a more complex scenario for using JSON in PL/SQL. Let’s say that you want to create a JSON representation of the result of a SQL statement. The following example demonstrates just that:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| set serveroutput on
declare
jsonArray json_list;
jsonObj json;
cursor get_data is
select initcap(lower(object_type)) name, count(*) count
from all_objects
where upper(object_type) like 'INDEX%'
group by object_type;
begin
jsonArray := json_list();
for objType in get_data loop
jsonObj := json();
jsonObj.put('object_type', objType.name);
jsonObj.put('count', objType.count);
jsonArray.add_elem(jsonObj.to_anydata);
end loop;
jsonArray.print;
dbms_output.new_line;
for i in 1..jsonArray.count loop
dbms_output.put_line(
json_ext.get_varchar2(
json.to_json(jsonArray.get_elem(i)),
'object_type'
)||'->'||
to_char(json_ext.get_number(
json.to_json(jsonArray.get_elem(i)),
'count'
))
);
end loop;
end;
/
|
In the example above, you first initialize the JSON array to an empty json_list. Next, you loop through the get_data cursor, which selects back the counts for all object types in the database that begin with “INDEX”. For each iteration of this loop, a JSONobject is created, the object type name and count are added as properties to the object, and the object is added to the JSON array. Then the string representation of the array is printed as output. Next, you loop through the JSON array itself, and print outthe object_type and count properties for each item in the list. The overall output should look as follows:
[{
"object_type" : "Index Partition",
"count" : 99
}, {
"object_type": "Index",
"count" : 1411
}, {
"object_type" : "Indextype",
"count" : 8
}]
Index Partition->99
Index->1411
Indextype->8
|
It’s important to note that the “print” function which outputs a string representation of JSON objects or arrays merely uses the DBMS_OUTPUT.PUT_LINE Oracle function to dump data to the SQL prompt.There are buffer limitations with this function, particularly on clients before 10g Release 2 (10.2). As a result, on large sets of data you may get an error:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
This is not an issue with PL/JSON, but rather an issue with the Oracle DBMS_OUTPUT.PUT_LINE function. Your JSON objects can be used in other means perfectly fine, but the print member function forthe json and json_list objects suffer from this limitation.
PL/JSON provides a ton of excellent features for working with JSON in PL/SQL. The best way to grips with it is to get your hands dirty and try it out for yourself. This post should be a good base to start from, and you can find out more by reading the doc.pdffile that comes with PL/JSON. Also, be sure to check out the examples folder, as it contains some good sample usages that are ready for you to test out. Finally, check the source code itself, particularly the type specification files:
As these will give you a good idea of the member functions available for json and json_list objects. If you have any specific questions about PL/JSON, leave a comment and I will do my best to answer them.
转载自：http://embosa.wordpress.com/2010/02/26/oracle-and-json-using-pljson/
下载PL/JSON
- http://sourceforge.net/projects/pljson/
- http://reseau.erasme.org/pl-sql-library-for-JSON?lang=en
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2016-7-28 07:19:17
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡