ORA-40478: output value too large (maximum: )

Uncategorized
| Dec 13, 2019
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. If you are hitting the following error while executing a query containing a JSON_TABLE function like this: select s.code, s.date_ins, to_char(s.date_ins,'HH24:MI') as t, r.report_name, fn_tab2str(cast(multiset(select name || ':' || value

My Presentations at #UKOUG #techfest19

Uncategorized
| Dec 3, 2019
I just finished my second presentation at TechFest19 in sunny Brighton, England. The conference this year is great from every point of view: venue, people, content, conversations… Thanks to those who came to my talks for your benevolent attention and excellent questions/remarks! Both my presentations are now on SlideShare. I beg you to download them […]

Human readable JSON, stored in BLOB

Uncategorized
| Nov 26, 2019

Currently Oracle (we're using Oracle 18c at the moment) is still recommending to store your JSON document in a BLOB column. More efficient, no character conversions and probably some more advantages.

When there is a need to look at the JSON document, having it as a BLOB is not very useful.

An easy way to make the JSON Blob readable is to use the following query:


select json_query (i.json_payload, '$' returning clob pretty)
from (more...)

Pretty print JSON strings in Oracle 12.2 the easy way

Uncategorized
| Oct 10, 2019
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. It turns out that in Oracle 12.2 you can easily get pretty printed large JSON strings using a query like this: select json_query(large_json_value, '$' returning clob pretty) as json_clob from some_table; Note however that the syntax returning clob pretty is not mentioned in the

Merge json files using Pandas

Uncategorized
| Aug 25, 2019

Quick demo for merging multiple json files using Pandas –

import pandas as pd
import glob
import json

file_list = glob.glob("*.json")
>>> file_list
['b.json', 'c.json', 'a.json']

Use enumerate to assign counter to files.


allFilesDict = {v:k for v, k in enumerate(file_list, 1)}
>>> allFilesDict
{1: 'b.json', 2: 'c.json', 3: 'a.json'}

Append the data into list –

>>> data = []

for k,v in allFilesDict.items():
    if 1  (more...)

Making Longer Lists

Uncategorized
| Aug 18, 2019
For very long lists, we need the return values to be CLOBs. Alas, LISTAGG can only return VARCHAR2 lists, but XML and JSON aggregate functions can return CLOBs!

JSON, the Euro symbol and a WE8MSWIN1252 character set database

Uncategorized
| Jul 30, 2019
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. Trying to parse a JSON_OBJECT_T string containing the € (euro) character on a WE8MSWIN1252 12.2.0.1.0 database returns the following error:   declare j json_object_t; begin j := json_object_t.parse('{"currency":"€"}'); end; /   ORA-40441: JSON syntax error ORA-06512: at "SYS.JDOM_T",

Houston, we’ve got a problem with JSON PUT method

Uncategorized
| Jul 3, 2019
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. In brief, in Oracle 12.2.0.1 there is some problem with PUT method of JSON_OBJECT_T, for some reason numbers are not recognized as such, but they are stored as scalar. The workaround is to use JSON_OBJECT_T.PARSE in combination with JSON_OBJECT SQL function for entering numbers (see the

JSON get_Date() method always discards time portion

Uncategorized
| 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 12.2.0.1.0 set serveroutput on declare l_job_description JSON_OBJECT_T; l_job_params JSON_ARRAY_T; l_job_arr_elem JSON_OBJECT_T; begin

Understanding Nested Lists Dictionaries of JSON in Python and AWS CLI

Uncategorized
| Apr 20, 2019

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...)

named tuple to JSON – Python

Uncategorized
| Apr 5, 2019

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='72.20.208.64:21132', 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

Uncategorized
| Mar 30, 2019
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

Uncategorized
| Mar 26, 2019

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

Uncategorized
| Nov 25, 2018
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

Uncategorized
| Jun 29, 2018
... 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

Uncategorized
| Feb 21, 2018
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)

Uncategorized
| Feb 10, 2018
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

Uncategorized
| Jan 9, 2018

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)

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

Getting Started with REST Enabled SQL

Uncategorized
| 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...)