JSON get_Date() method always discards time portion

| May 16, 2019
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. In case you wonder why you don't see the time portion when you call the method get_Date() on a JSON object: -- tested on Oracle set serveroutput on declare l_job_description JSON_OBJECT_T; l_job_params JSON_ARRAY_T; l_job_arr_elem JSON_OBJECT_T; begin

Convert JSON with PHP

Sometimes I get poorly thought or just naive questions. That is naive questions because they didn’t read the documentation or don’t understand the semantics of a given programming language. The question this time said they tried to implement what they found on a web page but their sample json_decode function example failed after they followed directions.

Surprise, it didn’t fail because they followed directions. They overlooked part of the example because they didn’t understand how (more...)

Jupyter Notebook for retrieving JSON data from REST APIs

If data is available from REST APIs, Jupyter Notebooks are a fine vehicle for retrieving that data and storing it in a meaningful, processable format. This article introduces an example of a such a dataset:


Oracle OpenWorld 2018 was a conference that took place in October 2018 in San Francisco. Over 30,000 attendees participated and visited some 2000 sessions. Raw data from the session catalog is available from an API – a REST service that (more...)

The Full Oracle OpenWorld and CodeOne 2018 Conference Session Catalog as JSON data set (for data science purposes)

Oracle OpenWorld and CodeOne 2018 are two co-located conferences that took place in October 2018. Some 2000 sessions presented by over 2500 presenters form the core of these conferences. Many details are known about each of the sessions and the speakers – from title, abstract, room (size), date and time, session slides, session type and key topics to first name, last name, Twitter handle, picture, company, job title and bio. image

A data set is available (more...)

Understanding Nested Lists Dictionaries of JSON in Python and AWS CLI

After lots of hair pulling, bouts of frustration, I was able to grasp this nested list and dictionary thingie in JSON output of AWS cli commands such as describe-db-instances and others. If you run the describe-db-instances for rds or describe-instances for ec2, you get a huge pile of JSON mumbo-jumpo with all those curly and square brackets studded with colons and commas. The output is heavily nested.

For example, if you do :

aws rds (more...)

My #OUGIreland19 Presentation on JSON

Thanks to those who came to my presentation today at OUG Ireland 2019. As promised, here is the link to the slides. JSON in 18c and 19c from stewashton I had a great time at the conference, meeting new folks and spending time (but never enough) with the less new. The talks I attended were […]

named tuple to JSON – Python

In pgdb – PostgreSQL DB API, the cursor which is used to manage the context of a fetch operation returns list of named tuples. These named tuples contain field names same as the column names of the database query.

An example of a row from the list of named tuples –

Row(log_time=datetime.datetime(2019, 3, 20, 5, 41, 29, 888000, tzinfo=), user_name='admin', connection_from='', command_tag='INSERT', message='AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,user.demodml,"insert into user.demodml (id) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(11);",',  (more...)

limit IOs by instances

This is a follow up to my previous post.

I had to answer if I can limit the (IO) resources a DB instance can utilize. Unfortunately, in a simple instance I can not do so. It can be done in PDBs, but right now PDBs are out of scope.
So a simpler approach was developed: limiting IOs by creating cgroups.

There are 2 steps:

  1. disks
  1. create a proper cgroup
  2. get all related devices for (more...)

Documented JSON Classes

Looking through the PeopleTools 8.57 Feature Overview document, you may have noticed that 8.57 now includes support for several JSON classes as well as PeopleBooks documentation. As Chris Malek showed us a couple of years ago, the classes listed in the Feature Overview document are not new. What is new is the keyword Support and PeopleBooks documentation. Using the documentation, I was able to generate a sample on PeopleTools 8.57:

Local  (more...)

Schema-on-what? How to model JSON

How do you make sense out of schema-on-read? This post shows you how to turn a JSON document into a relational data model that any modeler or relational database person could understand.

101 Ways to Process JSON with PeopleCode

... well... maybe not 101 ways, but there are several!

There is a lot of justified buzz around JSON. Many of us want to (or must) generate and parse JSON with PeopleSoft. Does PeopleSoft support JSON? Yes, actually. The Documents module can generate and parse JSON. Unfortunately, many of us find the Documents module's structure too restrictive. The following is a list of several alternatives available to PeopleSoft developers:

Using the Slack webhook API from PL/SQL

Slack is a web-based chat room popular with many companies. Slack also has an API that can be used to post messages to a given "channel" or chat room. The simplest API offered is the "webhook" integration, which is "a simple way to post messages from external sources into Slack. They make use of normal HTTP requests with a JSON payload that includes the message text and some options."

To create a new Slack (more...)

Pivot Table plugin for APEX (Updated)

Some years ago I created an APEX region plugin for displaying the results of a query as a Pivot Table. You can read the original blog post about that here.

The plugin proved quite popular, but people were having problems using it with APEX 5 due to a jQuery versioning conflict.

I've now updated the plugin so it works with APEX, both 5.0 and 5.1.

To use it, simply add a region to (more...)

JSON_TABLE and the Top 2000

Oracle database 12c provides native JSON parsing. It is relatively easy to go from data in a JSON format to a relational representation.
In this example I will use data from the Top 2000. Every year in The Netherlands a playlist is produced by popular vote and fully broadcast between Christmas and the last day of the year, with the number one being played just before the New Year. You can see the complete list (more...)

Oracle and the Autonomous Database (a personal perspective from afar)

Yeah, if you hadn’t seen that one coming, hmmm, what can I say… Lot’s of…

Getting Started with REST Enabled SQL

| Sep 7, 2017
POST a query or DML or DDL or even a short script over HTTPS to ORDS and have ORDS run that on your Oracle Database and have the results returned in JSON. That's the plan.

What You Need To Know

REST Enabled SQL uses Schema Authentication as well First Party Authentication.  

This means you can run SQL against an Oracle Database if the following is true.
  • REST Enabled SQL is enabled in ORDS
  • A (more...)

New! REST Enabled SQL for ORDS

| Sep 7, 2017

The Oracle REST Data Service provides REST access to your Oracle Database. Tables, predefined queries and PL/SQL blocks can be exposed as RESTful services. This is great when you can foresee what table, query or action you wish to REST enable.

Starting in ORDS 17.3 you can now POST the query, pl/sql or sql*plus statement to ORDS at run time. This new feature is call REST Enabled SQL and it is built into ORDS (more...)

Oracle Developer Cloud for Deployment Automation of PHP Applications

This blog will give an understanding of deploying a simple PHP based REST service project on Oracle Application Container Cloud using Oracle Developer Cloud. This post will show the essentials for the PHP project for maintaining, building Nodejs code on Developer Cloud Service and deploying from Developer Cloud to Application Container Cloud.


Tech Stack Usage

Eclipse: IDE for PHP development.

Grunt: Tool for building the PHP code archive for deployment.

Oracle Developer Cloud: For (more...)

eProseed Oracle Open World 2016 debrief – Oracle Database & Oracle Management Cloud

For people interested, here the slide desks used internally to debrief my colleagues regarding Oracle…

Oracle Database – JSON and the In-Memory Database

Here the presentation slide deck (100+) used during the oGH Dutch Usergroup session and (partially…