Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
62 lines (34 sloc) 2.42 KB

SODA for Java in the Database (OJVM)

SODA for Java is Oracle's fluent Java API for accessing JSON collections and documents without any knowledge of SQL. See Getting started with SODA for Java for more details on running testSODA.java with a client JVM (JDK or JRE). The goal of this write up is to furnish the steps for running testSODA.java in OJVM and manipulate the JSON collection directly in the database session without moving data around.

Requirements to run SODA with Java in the database

(i) Download the latest orajsoda.jar currently orajsoda-1.0.4.jar

(ii) Load the latest javax.json-1.0.4.jar

        loadjava -r -v -u hr/hr javax.json-1.0.4.jar

(iii) Upload orasoda.jar in your database schema

        loadjava -r -v -u hr/hr orajsoda-1.0.5.jar

Prep testSODA.java for OJVM

(i) Get (copy/paste) testSODA.java from Getting started with SODA for Java

(ii) Replace the URL in the connect string with the OJVM server-side connect URL

Replace "jdbc:oracle:thin:@//hostName:port/serviceName"
With "jdbc:default:connection"

The furnished testSODA.java on this page already has the change

(iii) load the updated testSODA.java in your schema using

        loadjava -r -v -user hr/hr testSODA.java

(iv) Create a SQL wrapper for invoking the main method

        create or replace procedure testSODA as
        language java name 'testSODA.main(java.lang.String[])';        

(v) Invoke the wrapper of the main method and display the output

        set serveroutput on
        call dbms_java.set_output(2000);
        call testSODA();

The furnished testSODA.sql performs the steps (iv) and (v).

From a database SQLPLUS session, issue the following call

sqlplus hr/hr @testSODA.sql

You should see the following output

 * Retrieving the first document by its key *

{ "name" : "Alex", "friends" : "50" }

* Retrieving documents representing users with at least 300 friends *

{ "name" : "Mia", "friends" : "300" }
{ "name" : "Gloria", "friends" : "399" }