Month: January 2016

Every second counts!

svenwellerLeave a comment

About DAY TO SECOND INTERVAL arithmetic

This is a short collection of 30 SQL examples that deal with intervals, specifically the DAY TO SECOND interval. In some cases the result differs from what one suspects.

I’ve hidden the results, so you can read it like a quiz. First think about the expected result, then see if it matches your expectation.

There is no special explanation given. If you want more details check the documentation links at the end of the article or post a comment.

tested on a 11.2.0.4 database (using German date language settings)

Creating an interval

Example 1

select interval '90' second from dual;
Click to see result

+00 00:01:30.000000

Example 2

select interval '90' second(2) from dual;
Click to see result

+00 00:01:30.000000

Example 3

select interval '90' second(1) from dual;
Click to see result

+00 00:01:30.000000

Example 4

select interval '90' day(1) from dual;
Click to see result

ORA-01873: the leading precision of the interval is too small
*Cause: The leading precision of the interval is too small to store the
specified interval.
*Action: Increase the leading precision of the interval or specify an
interval with a smaller leading precision.

Example 5

select interval '1:30' minute to second from dual;
Click to see result

+00 00:01:30.000000

Example 6

select interval '0:90' minute to second from dual;
Click to see result

ORA-01852: seconds must be between 0 and 59

Example 7

select numtodsinterval(90, 'second') from dual;
Click to see result

+00 00:01:30.000000

Example 8

select numtodsinterval(1.5, 'minute') from dual;
Click to see result

+00 00:01:30.000000

Example 9

select to_dsinterval('90') from dual;
Click to see result

ORA-01867: the interval is invalid
*Cause: The character string you specified is not a valid interval.
*Action: Please specify a valid interval.

Example 10

select to_dsinterval('+0 00:01:30') from dual;
Click to see result

+00 00:01:30.000000

Example 11

select to_dsinterval('PT0H1M30S') from dual;
Click to see result

+00 00:01:30.000000

Example 12

select to_dsinterval('PT90S') from dual;
Click to see result

+00 00:01:30.000000

Interval-like data

Example 13

select to_timestamp('30','SS') from dual;
Click to see result

01.01.16 00:00:30,000000000

Example 14

select to_timestamp('90','SS') from dual;
Click to see result

ORA-01852: seconds must be between 0 and 59

Example 15

select to_timestamp('90','SSSSS') from dual;
Click to see result

01.01.16 00:01:30,000000000

Example 16

select to_timestamp('1:30','MI:SS') from dual;
Click to see result

01.01.16 00:01:30,000000000

Using and printing intervals

Example 17

select extract(second from interval '90' second) from dual;
Click to see result

30

Example 18

select extract(second from systimestamp) from dual;
Click to see result

57,131241

Example 19

select extract(second from sysdate) from dual;
Click to see result

ORA-30076: invalid extract field for extract source
*Cause: The extract source does not contain the specified extract field.
*Action:

Example 20

select extract(day from sysdate) from dual;
Click to see result

20

Example 21

select extract(second from numtodsinterval(90, 'second')) from dual;
Click to see result

30

Example 22

select extract(second from to_timestamp('1:30','MI:SS')) from dual;
Click to see result

30

Example 23

select to_char(interval '90' second,'SS') from dual;
Click to see result

+00 00:01:30.000000
I think this is a bug!
The formatting seems to be ignored for interval datatypes (Oracle 11.2.0.4)

Example 24

select to_char(interval '90' second,'SSSSS') from dual;
Click to see result

+00 00:01:30.000000

Example 25

select to_char(trunc(sysdate) + interval '90' second,'SS') from dual;
Click to see result

30

Example 26

select to_char(trunc(sysdate) + interval '90' second,'SSSSS') from dual;
Click to see result

00090

Example 27

select to_char(trunc(sysdate) + interval '90' second,'FMSSSSS') from dual;
Click to see result

90

Example 28

select sysdate - (sysdate - interval '90' second) from dual;
Click to see result

0,001041666666666666666666666666666666666667

Example 29

select sysdate - sysdate + interval '90' second from dual;
Click to see result

ORA-30081: invalid data type for datetime/interval arithmetic

Example 30

select numtodsinterval(sysdate - (sysdate - interval '90' second),'DAY') from dual;
Click to see result

+00 00:01:30.000000

Quirks

  • The precision for seconds seems to be ignored for interval literals (example 2).
  • TO_CHAR does not consider the formatting or intervals have no extra formatting options
  • EXTRACT uses ANSI date. ANSI date has no time component. That’s why one can extract the day from it but not the seconds (example 19/20)
  • extract on a timestamp also returns fractional seconds

Documentation links

Collapsible regions in APEX

APEX5 UT remote control for collapsible regions

svenweller10 Comments

Introduction

A colleque of mine recently had the wish to be able to control the state of a collapsible region using a page item.

This is how a simple report region using the “Collapsible” region template looks like.

Collapsible regions in APEX

We now want to control the state of the region by a page item. For example if the item is NO or 0 then the region should be expanded, if it is YES or 1 then the region should be collapsed.

There are three general options how to do that.

  1. Simulate the click on the region button
  2. Dynamically switch css classes
  3. Set the template option based upon some page item value

I’ll show how to implement all three possibilities.

There is also demo on https://apex.oracle.com/pls/apex/f?p=21878 to show the different options.
Login: guest/information

Option 1: click the button

Option 1

First we create a page item that should influence the state of our region. I’ve choosen a radio group, but any type of item will do.

A dynamic action is added that reacts on the change event (or on click) for the radio group. The DA then will executes the click on the button, but takes into account the current state of the region and the value of the page item. So that if you choose “expanded” then the click will only be executed if the region is currently collapsed.

On Change dynamic action

The DA has a condition PAGE_ITEM equals 0 (=expanded). This condition allows to have a TRUE (=expand) and a FALSE (=collapse) javascript action.

Condition for DA

The button can be identified using the region static alias (here REGION_TOGGLE).
The following jquery selector would find the region and the matching button in it.
$(‘#REGION_TOGGLE button.t-Button–hideShow’)

To find out about the state of the region/button we could either check the is-collapsed|is-expanded class or the aria-expanded attribute of the button.

A click() will then do the click event on the element that is selected.

So this is how the two actions can be implemented:

TRUE action:
$(‘#REGION_TOGGLE button.t-Button–hideShow[aria-expanded=”true”]’).click()

FALSE action:
$(‘#REGION_TOGGLE button.t-Button–hideShow[aria-expanded=”false”]’).click()

This works as long as there are no sub regions that could also be collapsible. Then the jquery selector needs to be more specific.

Option 2: switch css

Option 2

This is similiar to Option 1. But instead of doing a click event, the css classes are exchanged. So the difference to option 1 is only in the javascript actions of the dynamic action.

True action:
$(‘#REGION_TOGGLE2.a-Collapsible.is-collapsed’).removeClass(‘is-collapsed’).addClass(‘is-expanded’);
$(‘#REGION_TOGGLE2.a-Collapsible .a-Collapsible-content’).show();

False action:
$(‘#REGION_TOGGLE2.a-Collapsible.is-expanded’).removeClass(‘is-expanded’).addClass(‘is-collapsed’);
$(‘#REGION_TOGGLE2.a-Collapsible .a-Collapsible-content’).hide();

Three activities are done. The old class is removed from the button and the new class is added. This simply changes the icon (arrow down or left) of the region botton. And then the region content below the region header is shown or hidden.
The jquery .show()|.hide() methods result in a {display:block|none} css.

Option 3: template option

Option 3

This option works differently then the other two. It does not change the region state dynamically. Instead it renders the region depending on some item state during page load. In this case the item is not 0 or 1, but instead has a value of “is-expanded” or “is-collapsed”.

The “Collapsible” region template was copied as “Collapsible_by_Item” and got a new template option “Default State (by Item)”. The group for the new option needs to be “default state”. The value of the option is “&P1_ITEM_NAME.” (choose your specific item name).

Careful: If implemented like this, then the region template can only be used on the page where this page item exists. But it is an easy way to do it.

After creating the template the region must use it and set the default state to “Default State (by Item)”.

The Region can now be collapsed and expanded by changing the item and submitting the page. The item state will stay (apex default behaviour) and therefore the correct template class is used during page load.

Comparison

All options have some tiny differences that we should be aware of.

  • Option 1 (click) leaves the region button “highlighted” with a tiny blue ring around the icon. This is because the browser marks the button as active or current. This won’t happen for the other options.
  • For all options the state of the region can differ from the state of the page item. It happens when the region is collapsed or expanded by clicking on the region button directly.
  • Option 1 and 2 can be combined with Option 3 if the execution of the dynamic action should be avoided during page load.
  • Option 3 requires good understanding between page item state and region rendering. But it avoids that the region is changed, after the page is loaded. This is especially valuable for pages that need some time to load.
  • A simple refresh of the region does not work for option 3 because the refresh logic is based upon the content of the region and does not include the region header.

Examining JSON functions in 12.1.0.2

svenwellerLeave a comment

In preparation of a new version of my Quickreference SQL Advanced Functions i had a closer look at the new SQL JSON possibilities which are available in Oracle DB 12.1.0.2.

Overview

There are three functions and three conditions that all deal with JSON.

  • json_value – a function that returns a single (skalar) value
  • json_query – a function that returns a json string (can be an array)
  • json_table – a function that provides table logic based upon a json document.
  • is [not] json – checks if the document is json or not
  • json_exists – checks if a certain element or structure exists in the json document
  • json_textcontains – efficient search through a json document using an oracle text index

JSON_TABLE is the most versatile of all json functions. It can do everything that json_value, json_query and json_exists can do and more than that. So if you want to learn more about the json functions in 12c then concentrate on JSON_TABLE. See also: JSON_TABLE Generalizes Other Oracle SQL Functions

Call Parameters

data is [not] json
json_exists(data, $path on_error)
json_textcontains(column, $path, searchstring)
json_query(data, $path returning query_wrapper on_error)
json_table(data, $path returning on_error COLUMNS columnlist)
json_value(data, $path returning on_error)

data:  json varchar2, clob or a blob; blobs need to add “FORMAT JSON”

$path: json search path

on_error: action when return error happens;
ERROR|NULL|EMPTY|DEFAULT literal|TRUE|FALSE ON ERROR

ERROR, NULL for all functions
TRUE|FALSE   for json_exists
EMPTY             for json_query
DEFAULT          for json_table, json_value

returning :return data type and formatting; PRETTY ASCII

query_wrapper  : WITH|WITHOUT ARRAY WRAPPER

columnlist  : json column list

EXISTS PATH             like json_exists
PATH                            like json_query or json_value
NESTED PATH           single row unnesting
FOR ORDINALITY    row numbers

Understanding JSON path expressions

All functions and conditions can use a json path expression.

A JSON_PATH expression uses dot-notation to separate steps. Each step an be an object or an array step.

Syntax $path (object or array) 

$
[ { . { * | simplename | ”complexname” } }
   | 
   { [ { integer [TO integer]     
         [, integer [TO integer]
         ]…
         } 
         | *
      ]
   }
] …

Some examples for json path expressions

$.ShippingInstructions.Address.zipCode
$.a.b.c
$.a[3].b[*].c[0]

The json_table function will map columns based upon a json_path expression.
The columns can be defined using an expression resembling the JSON_VALUE, JSON_QUERY or JSON_EXISTS syntax.

 

Examples

Example 1

select *
from JSON_TABLE('{"data":{"a":[1,2,3],"b":[0,1,2],"c":"nur ein test"}}'
               ,'$.data'
       columns ("A_json" varchar2(100) format json path '$.a'
               ,"B_check" varchar2(10) EXISTS path '$.b'
               ,"C_text" varchar2(100) path '$.c'
               )) tab
;
A_JSON B_CHECK C_TEXT
[1,2,3] true nur ein test

Column A uses a json_query syntax. The “format json” directive allows to return a more complex json object.

Column B uses a json_exists syntax. If the column is declared as varchar2 then true/false is return. If it is declared as number then 1/0 is returned.

Column C uses the default json_value syntax.

Example 2

select *
from json_table('{a:[5, 10, 15]}', '$'
columns (val1 varchar2(20) format json path '$.a');
VAL1
[5,10,15]

 

select *
from json_table('{a:[5, 10, 15]}', '$'
      columns (nested path '$.a[*]' columns (val2 number path '$'))
);
VAL2
5
10
15

 

And now the combination of both columns in one query.

select *
from json_table('{a:[5, 10, 15]}', '$'
      columns (val1 varchar2(20) format json path '$.a'
              ,nested path '$.a[*]' columns (val2 number path '$'))
);

 

VAL1 VAL2
– 5
– 10
– 15
– 15

 

This result is a bug!

There is a patch available from oracle metalink, that corrects this.

patch 20080249: “JSON Patch Bundle 1”. MOS Doc 1992767.1

It is strongly recommended to install this patch when planning to work with JSON data in the database.

An error happens, but the json function does return an empty string and do not raise the error.

We can get some more info by adding the “ERROR ON ERROR” clause.

select *
from json_table('{a:[5, 10, 15]}', '$'
error on error
columns (val1 varchar2(20) format json path '$.a' ,
         nested path '$.a[*]' columns (val2 number path '$')));


ORA-40480: result cannot be returned without array wrapper

Now that we see the error message, we can try to react on it by adding “WITH ARRAY WRAPPER”. for column val1

select *
from json_table('{a:[5, 10, 15]}', '$'
error on error
columns (val1 varchar2(20) format json WITH ARRAY WRAPPER path '$.a' ,
         nested path '$.a[*]' columns (val2 number path '$')));
VAL1 VAL2
[[5] 5
,[10] 10
,[15] 15
,[]] 15

 
This result is still wrong. But at least we got some indication about what happend to our data. To solve the issue we need to install the patch or do a different kind of query.

Fazit

Using json functions in the database is easy. But just as with any other new technology we need a little time to get used to it. Also expect a few minor bugs at the beginning.

 

Other reads