Posts tagged dbms_epg
Creating JSON document straight from SQL query – using LISTAGG and With Clause
0This very brief article demonstrates how a fairly complex, nested JSON document can be created using a single SQL query. The main features used in the SQL statement are the WITH clause to define the inline views per object (Department, Manager, Employee), Scalar Subquery to retrieve the result from an inline view as string into the overall JSON string and LISTAGG to collect multiple elements into a JSON list.
The article shows a very simple application of this JSON creation through a simple HTML page – served by a PL/SQL package. This page does not contain any data. Right after loading, the onLoad event triggers a listener that invokes – through AJAX – the PL/SQL package’s other procedure. This procedure returns the JSON object – courtesy of that fabulous SQL query – and uses it to write the company details on the fly into the page.
Producing simple Pie Chart straight out of the Oracle Database – leveraging dbms_epg, CANVAS, LISTAGG and some JavaScript
0Once upon a time, I had something of a reputation for creating Charts in SQL. Bar Charts, Pie Charts, Gantt Charts and several other unlikely chart types, all created with SQL. And all presented on the one stage SQL has: SQL*Plus. See for example such riveting articles like http://technology.amis.nl/blog/1890/creating-a-gantt-chart-in-sql, http://technology.amis.nl/blog/1937/gauge-charts-in-sql-or-how-to-produce-the-worlds-ugliest-speedometers and http://technology.amis.nl/blog/398/pie-charts-in-sql-how-pathetic-can-you-get. Now I am back, with another look at charts produced straight out of the Oracle Database – this time using a different stage to present the chart on. With the Embedded PL/SQL Gateway – introduced in release 10g – the Oracle Database has a different stage to present information on or a different user interface to interact through: the web browser.
This article demonstrates how we can present charts – in the web browser using the CANVAS tag – based directly on a PL/SQL package in the database. No middle tier is required in between, no additional libraries are frameworks need to be configured. It’s only between the package and your browser.
Granted, you have seen More >
No JDBC based data retrieval in Java applications – RESTstyle, JSON formatted HTTP based interaction from Java to Database
1This article demonstrates how Java application can query data from relational databases (well, in this case more specifically the Oracle RDBMS) without the use of plain old JDBC but instead through a simple HTTP interaction with a RESTful service that returns the data in JSON format.
In this way, issues with JDBC drivers, database connections, data sources and firewall and other network complications are avoided and the Java application does not contain a single bit of SQL.
This article will demonstrate how easy it is to set up the RESTful API from the database – using Oracle’s dbms_epg package – and how this API can be accessed from Java using JAX-RS (Jersey) and the json-lib utility library.
This topic is one of many to be discussed in Friday’s (4th February) Masterclass on Leveraging the Oracle Database in Java Application (some seats are available – go to http://www.amis.nl/trainingen/oracle-database-in-java-applications for registration and details).
Recent Comments