DBA Hub

📋Steps in this guide1/2

JSON_BEHAVIOR Parameter in Oracle Database 23ai/26ai

By default many JSON functions return null if they encounter a runtime error. Oracle database 23ai introduced the JSON_BEHAVIOR parameter to allow us to alter this default behaviour for our session.

oracle 23configurationintermediate
by OracleDba
12 views
1

Prior Releases

By default many JSON functions return null if they encounter a runtime error. The following example shows the default behaviour of the function. We can control the handling of errors at the statement level by including an optional error clause. The error clause has a variety of settings, some of which are function-specific or condition-specific. - ERROR ON ERROR - NULL ON ERROR - Not for JSON_EXISTS. - FALSE ON ERROR – JSON_EXISTS and JSON_EQUAL only. Default. - TRUE ON ERROR – JSON_EXISTS and JSON_EQUAL only. - EMPTY OBJECT ON ERROR – JSON_QUERY only. - EMPTY ARRAY ON ERROR – JSON_QUERY only. - EMPTY ON ERROR – JSON_QUERY only. - DEFAULT 'literal_return_value' ON ERROR

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT json_value('banana', '$.*') as data;

DATA
--------------------------------------------------------------------------------


SQL>

SELECT json_value('banana', '$.*' error on error) as data;
*
ERROR at line 1:
ORA-40441: JSON syntax error
JZN-00078: Invalid JSON keyword 'banana' (line 1, position 1)
Help: https://docs.oracle.com/error-help/db/ora-40441/


SQL>
2

JSON_BEHAVIOR

The parameter allows us to alter the behaviour of JSON functions which default to . We might be happy with the default of clause, but at development time want to check our code to make sure we're getting the results we expect, not just hiding runtime errors. When we connect to a new session we see this parameter is not set. Without touching the parameter we call a JSON function that results in an error and we see a null value is returned. We set the parameter to and we see the error is reported. We set it to , and we revert to the default behaviour. We can also reset it to give the default behaviour. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
connect testuser1/testuser1@//localhost:1521/freepdb1

show parameter json_behavior

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
json_behavior                        string

SQL>

select json_value('banana', '$.*') as data;

DATA
--------------------------------------------------------------------------------


SQL>

alter session set json_behavior='on_error:error';

select json_value('banana', '$.*') as data;
*
ERROR at line 1:
ORA-40441: JSON syntax error
JZN-00078: Invalid JSON keyword 'banana' (line 1, position 1)
Help: https://docs.oracle.com/error-help/db/ora-40441/


SQL>

alter session set json_behavior='on_error:null';

select json_value('banana', '$.*') as data;

DATA
--------------------------------------------------------------------------------


SQL>

alter session reset json_behavior;

select json_value('banana', '$.*') as data;

DATA
--------------------------------------------------------------------------------


SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!