JSON – Extracting Key Names

In some circumstances you might have a JSON source document that is non standard, where a key name itself contains information that you want to extract.

For example, consider this sample stock JSON. The stock numbers 75s8n10Bmw, and AAA1234123w are actually a key name rather than a key value in the data.

{
  "stock": {
    "75s8n10Bmw": {
      "name": "Super Washing machine",
      "introduced":"2020-05-21",
    },
    "AAA1234123w": {
      "name": "Gas Cooker",
      "introduced":"2018-02-21",
    } 
  }
}

Unlike XML Path which has name() and local-name() functions for extracting a key (node) name, JSON path doesn’t seem to have an equivalent. So how do you extract the key name as a value and return it in the output? Here’s a solution I came up with :

Continue reading

JSON Array to Delimited String

Someone posed a question on OTN asking how to get the string representation of a JSON Array as a column from a JSON_Table call.

E.g. Turning...
 
{"a":400,"b":500,"c":["Jim","Fred","Jason","Al-\"E\"-Boy"]}

into...

         A|         B|C
----------|----------|------------------------------
       400|       500|Jim,Fred,Jason,Al-"E"-Boy

Note : Al-"E"-Boy was added as a special case with embedded quotes

Continue reading

Auditing Table Data Changes – A Better Approach : Flashback Data Archive

If you’ve ever wanted to audit insert / update / delete changes to data in a table, the classic approach would be to use DML (Data Manipulation Language) Triggers. This is creating an audit history table and add a trigger to the table(s) in question which populates that audit history table. Whilst this approach works, it has many pitfalls the main ones being :

  • Performance Cost – DML Triggers adds a significant performance cost to DML operations, slowing your DB down, not just the extra inserts to the audit table, but the context switches between the SQL of the DML and the PL/SQL of the trigger.
  • Maintenance Headaches – They introduce maintenance issues as they don’t automatically evolve with table DDL (Data Definition Language). e.g. If someone adds / removes / changes columns, someone also has to remember to change the triggers.
  • Retention Size – The size of the audit table will often continue to grow and grow without any consideration to retention window (how long you’re likely to keep your data for).
  • Direct Path Issues – You cannot use Insert /*+ APPEND */ ... direct path inserts as this prohibits trigger usage and SQL*Loader DIRECT PATH LOAD silently disables triggers, so you won’t capture those changes, in which case you’ll lose audit information.

So is there a better option? Fortunately, yes there is and one which doesn’t suffer from the problems listed above Flashback Data Archive.

Continue reading

Number of Interval Units in Interval – DSIntervalToNum and YMIntervalToNum

Oracle has two routines NumToDSInterval and NumToYMInterval which return an interval from an input number of interval units. (Note : For those not aware, an interval is the difference between two timestamps. DS stands for intervals in the range Days to Seconds; YM stands for intervals in the range Years to Months.)

E.g.

select NumToDSInterval(12.34, 'hour') DSInterval
     , NumToYMInterval(23.45, 'month') YMInterval
from dual
/

DSINTERVAL                    |YMINTERVAL
------------------------------|----------
+000000000 12:20:24.000000000 |+01-11

But Oracle don’t provide functions for doing the inverse of this, that is, extracting the number of interval units from an interval, which was something I needed the other day. For example you want to know the number of hours between two timestamps. Here’s my solutions :

create or replace function DSIntervalToNum(pInterval in interval day to second,
                                           pUnits    in varchar2) return number deterministic is
-- Converts a Day to Second interval into a number of the units specified
  ILLEGAL_ARGUMENT EXCEPTION;
  pragma exception_init(ILLEGAL_ARGUMENT, -1760);
  vUnitsPerDay pls_integer;    
begin
  case upper(pUnits)
    when 'DAY'    then vUnitsPerDay := 1;
    when 'HOUR'   then vUnitsPerDay := 24;
    when 'MINUTE' then vUnitsPerDay := 1440;
    when 'SECOND' then vUnitsPerDay := 86400;
    else raise ILLEGAL_ARGUMENT;
  end case;
  return  extract(day    from pInterval) *  vUnitsPerDay
        + extract(hour   from pInterval) * (vUnitsPerDay / 24)
        + extract(minute from pInterval) * (vUnitsPerDay / 1440)
        + extract(second from pInterval) * (vUnitsPerDay / 86400);
end;
/

create or replace function YMIntervalToNum(pInterval in interval year to month,
                                           pUnits    in varchar2) return number deterministic is
-- Converts a Year to Month interval into a number of the units specified
  ILLEGAL_ARGUMENT EXCEPTION;
  pragma exception_init(ILLEGAL_ARGUMENT, -1760);
  vResult number;  
begin
  case upper(pUnits)
    when 'YEAR'  then vResult := extract(year from pInterval) + extract(month from pInterval) / 12;
    when 'MONTH' then vResult := extract(year from pInterval) * 12 + extract(month from pInterval);
    else raise ILLEGAL_ARGUMENT;
  end case;
  return vResult;
end;
/
 

Oracle’s NumToDSInterval, NumToYMInterval raise ORA-01760 Illegal Argument exception if you pass something invalid, so I’ve done the same.

Let’s see some examples of their use :

select DSIntervalToNum('+000000008 05:21:26.647200000', 'second') total_seconds
     , DSIntervalToNum('-000000763 08:53:54.528000000', 'minute') total_minutes
     , DSIntervalToNum('+000000518 20:32:25.440000000', 'hour') total_hours
     , DSIntervalToNum(systimestamp - timestamp '1997-01-31 09:26:56.66 +02:00', 'hour') total_days
     , YMIntervalToNum('12-6', 'month') total_months
     , YMIntervalToNum('+167-06', 'year') total_years
from dual
/

TOTAL_SECONDS|TOTAL_MINUTES|TOTAL_HOURS|TOTAL_DAYS|TOTAL_MONTHS|TOTAL_YEARS
-------------|-------------|-----------|----------|------------|-----------
   710486.647|   -1099253.9| 12452.5404|212595.623|         150|      167.5
 

ORA-01720: grant option does not exist for ‘SYS.DUAL’, when compiling a view

If you are recompiling a view with create or replace view… and that view references DUAL, you may get ORA-01720: grant option does not exist for ‘SYS.DUAL’.

Why, you may ask?

Well from 11.2.0.4 onwards, Oracle corrected the permissions model on views after a long standing oversight.

In brief, you will get this ORA-1720 error when REPLACING a view that selects from some other user’s tables (or views / mviews) and both of the following conditions are true:

  • Non “select” grants exist : You have already granted privileges other than select on the VIEW to some other user.
  • Non matching grants exist on referenced tables / views / mviews : The view owner does not have the matching GRANT option on the tables / views / mviews being selected from. Incompatible grants cannot exist.

Here’s a simple demo of the error

create table t_table (val number);

create view v_view as select * from t_table;

grant all on v_view to public;

create or replace view v_view as select 2 as x from dual;

>> create or replace view v_view as select 2 as x from dual
Error at line 7
ORA-01720: grant option does not exist for 'SYS.DUAL'

The line

grant all on v_view to public;

Effectively grants DELETE, INSERT, SELECT, UPDATE, REFERENCES, MERGE VIEW, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK, READ on the view to public.

When we recompile v_view

create or replace view v_view as select 2 as x from dual;

Oracle checks DUAL has the same grants as the view (DELETE, INSERT, SELECT, UPDATE, REFERENCES, MERGE VIEW, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK, READ to public), whereas only SELECT on DUAL has been granted to public, breaking the conditions above.

The easiest way to fix the problem is to either drop the view first :

[snip...]
drop view v_view;

create view v_view as select 2 as x from dual;

View created.

Or revoke the non select grants and only add the select grant:

[snip...]

revoke all on v_view;

grant select on v_view;

create or replace view v_view as select 2 as x from dual;

View created.

Generate Numbers With Step Increment in SQL

A friend needed to generate numbers between a start and end range and with a step increment in SQL and couldn’t think how to do it, so highlighted below is the template I wrote for him. It’s very simple, but may be useful for others.

Here’s an example of its usage in SQL*Plus, producing the numbers counting between -2 and 1.5 in step increment of 0.2. Notice that 1.5 is not included? That’s because the step increment when added to previous value, 1.4, exceeds the end number. Note : You may be able to break it by using nonsensical values, but used properly it’ll work for most purposes.

set termout off

variable startnum number
variable endnum number
variable step number

exec :startnum := -2
exec :endnum   := 1.5
exec :step     := 0.2

set termout on
 
select :startnum + ((level - 1) * :step) val
from dual
connect by level <= ((:endnum - :startnum) / :step) + 1;

       VAL
----------
        -2
      -1.8
      -1.6
      -1.4
      -1.2
        -1
       -.8
       -.6
       -.4
       -.2
         0
        .2
        .4
        .6
        .8
         1
       1.2
       1.4

18 rows selected.

ORA-29283: invalid file operation

I’ve often seen questions regarding ORA-29283: invalid file operation when trying to read from or write to a file in an Oracle directory. Especially from people who have installed Oracle XE on Windows and are utilising UTL_FILE. If the Oracle Directory (an Oracle object referencing an OS directory) has been created, this could be because the Oracle Process doesn’t have the correct permissions to access the directory at Operating System (OS) level. The error will typically be something like this :

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478

Continue reading

DBMS_SQL – Executing SQL longer than 32KB

If you’re using DBMS_SQL, you may have come across the standard 32KB limit for dynamic SQL (or PL/SQL) you’re wishing to execute, when opening a DBMS_SQL cursor. To handle situations exceeding this, you have to chunk parse the SQL. Here’s a procedure which handles either situation transparently, it takes SQL as a clob (or you can pass as varchar2) and it’ll return the DBMS_SQL cursor – after parsing or chunk parsing accordingly. Either add to an existing library package or if you want to use as a standalone procedure, comment out the first line.

-- create or replace /* uncomment this line if standalone procedure */
procedure InitCursor(pSQL      in clob character set any_cs,
                     pCursorID out integer) is
  MAX_CHUNK_SIZE constant integer := 32767 - 2;  -- Max PL/SQL less 2 bytes as some Oracle versions have 32767 byte bug
  vLen pls_integer;
  vVarchar2a dbms_sql.varchar2a;
  vCnt pls_integer;
begin
  vLen := dbms_lob.getlength(pSQL);       -- Parse the query that will be used to fetch all the data to be written out...
  pCursorID := dbms_sql.open_cursor;
  if vLen <= MAX_CHUNK_SIZE then
    dbms_sql.parse(pCursorID, pSQL, dbms_sql.native);
  else
    vCnt := ceil(vLen / MAX_CHUNK_SIZE);  -- For large SQL, we need to chunk it
    for i in 1..vCnt
    loop
      vVarchar2a(i) := dbms_lob.substr(pSQL, MAX_CHUNK_SIZE, (i-1) * MAX_CHUNK_SIZE + 1);
    end loop;
    dbms_sql.parse(pCursorID, vVarchar2a, 1, vCnt, false, dbms_sql.native);
  end if;
end;
    

SQL Output to Key / Value Pairs

In this post I’ll show how you can convert SQL output into Key / Value pairs. In other words unpivoting the data such that each the Column Name becomes the KEY, and the Column Value becomes the VALUE.

[Note : This was a real world request from a poster on a forum I subscribe to.]

For example. Converting this….

OWNER     |OBJECT_NAME    |OBJECT_TYPE    |CREATED
----------|---------------|---------------|------------------------------
SYS       |I_FILE#_BLOCK# |INDEX          |04/02/2019 04:45:20
SYS       |I_OBJ3         |INDEX          |04/02/2019 04:45:20

into this…. (RECNO = row record number in dataset, COLPOS = column position in row)

KEY            |VALUE                         |     RECNO|    COLPOS
---------------|------------------------------|----------|----------
OWNER          |SYS                           |         1|         1
OBJECT_NAME    |I_FILE#_BLOCK#                |         1|         2
OBJECT_TYPE    |INDEX                         |         1|         3
CREATED        |04/02/2019 04:45:20           |         1|         4
OWNER          |SYS                           |         2|         1
OBJECT_NAME    |I_OBJ3                        |         2|         2
OBJECT_TYPE    |INDEX                         |         2|         3
CREATED        |04/02/2019 04:45:20           |         2|         4

There’s a few ways to solve this problem, one would be to use DBMS_XMLGen as I showed in a previous post. But in this example I’ll use DBMS_SQL to dynamically solve it.

Solution :

Continue reading

JSON Bugs – Mixing NULL ON NULL and ABSENT ON NULL

When generating JSON in Oracle where you might be mixing ABSENT ON NULL and NULL ON NULL in the same query block (for something like JSON_OBJECT), please be aware you might hit some issues which give you incorrect results or errors. These bugs exist in all current versions at the time of writing : 12.2c, 18c, 19c. I have logged these issues with Oracle.

SQL> 
SQL> 
SQL> -- Test 1. CORRECT. Output for val2 is null on null
SQL> select
  2         json_object (
  3           'val1' value 1
  4         , 'val2' value json_object (
  5             'childval1' value null
  6           , 'childval2' value null
  7             null on null
  8           )
  9         ) json_null_on_null
 10  from dual
 11  /

JSON_NULL_ON_NULL
-------------------------------------------------------
{"val1":1,"val2":{"childval1":null,"childval2":null}}

SQL> 
SQL> -- Test 2. CORRECT. Output for val2 is absent on null
SQL> select
  2         json_object (
  3           'val1' value 1
  4         , 'val2' value json_object (
  5             'childval1' value null
  6           , 'childval2' value null
  7             absent on null
  8           )
  9         ) json_absent_on_null
 10  from dual
 11  /

JSON_ABSENT_ON_NULL
-------------------------------------------------------
{"val1":1,"val2":{}}

SQL> 
SQL> -- Test 3. BUG. Both json_objects output as absent on null
SQL> select
  2         json_object (
  3           'val1' value 1
  4         , 'val2' value json_object (
  5             'childval1' value null
  6           , 'childval2' value null
  7             null on null
  8           )
  9         ) json_null_on_null
 10       , json_object (
 11           'val1' value 1
 12         , 'val2' value json_object (
 13             'childval1' value null
 14           , 'childval2' value null
 15             absent on null
 16           )
 17         ) json_absent_on_null
 18  from dual
 19  /

JSON_NULL_ON_NULL                                       JSON_ABSENT_ON_NULL
------------------------------------------------------- -------------------------------------------------------
{"val1":1,"val2":{}}                                    {"val1":1,"val2":{}}

SQL> 
SQL> -- Test 4. BUG. Both json_objects output as null on null
SQL> select
  2         json_object (
  3           'val1' value 1
  4         , 'val2' value json_object (
  5             'childval1' value null
  6           , 'childval2' value null
  7             absent on null
  8           )
  9         ) json_absent_on_null
 10       , json_object (
 11           'val1' value 1
 12         , 'val2' value json_object (
 13             'childval1' value null
 14           , 'childval2' value null
 15             null on null
 16           )
 17         ) json_null_on_null
 18  from dual
 19  /

JSON_ABSENT_ON_NULL                                     JSON_NULL_ON_NULL
------------------------------------------------------- -------------------------------------------------------
{"val1":1,"val2":{"childval1":null,"childval2":null}}   {"val1":1,"val2":{"childval1":null,"childval2":null}}

SQL> 
SQL> -- Test 5. BUG. ORA-40478: output value too large (maximum: 4000) ??
SQL> select
  2         json_object (
  3           'val1' value 1
  4         , 'val2' value json_object (
  5             'childval1' value null
  6           , 'childval2' value null
  7             null on null
  8           )
  9         ) json_null_on_null
 10       , json_object (
 11           'val3' value 1
 12         , 'val4' value json_object (
 13             'childval1' value null
 14           , 'childval2' value null
 15             absent on null
 16           )
 17         ) json_absent_on_null
 18  from dual
 19  /
from dual
     *
ERROR at line 18:
ORA-40478: output value too large (maximum: 4000)