Oracle SQL to JSON

Question

I'm trying to convert a query to JSON format, so I can use it with streaming data. I searched in the oracle documentations how to do it, but I'm getting this error on line 6, what could it be?

Error: ORA-00406: the COMPATIBLE parameter must be 12.0 or greater 00406. 00000 - "COMPATIBLE parameter needs to be %s or greater" *Cause: The COMPATIBLE initialization parameter is not high enough to allow the operation. Allowing the command would make the database incompatible with the release specified by the current COMPATIBLE parameter.

Oracle documentation: https://oracle-base.com/articles/19c/json_object-enhancements-19c

When I run the query that return the database version comes 19.0.0.0

SELECT
     JSON_OBJECT(h.loca_nm_local, j.cllo_nm_cluster_log) AS json_data
FROM
     db1.local         h
     LEFT JOIN db1.local_cluster i ON h.loca_sq_local = i.loca_sq_local
     LEFT JOIN db1.cluster_log   j ON i.cllo_sq_cluster_log = j.cllo_sq_cluster_log
WHERE
    ( j.cllo_nm_cluster_log LIKE '%NEB%'
    OR j.cllo_nm_cluster_log LIKE '%SOB%'
    OR j.cllo_nm_cluster_log LIKE '%BC%'
    OR j.cllo_nm_cluster_log LIKE '%BS%'
    OR j.cllo_nm_cluster_log LIKE '%BUZ%'
    OR j.cllo_nm_cluster_log LIKE '%ES-%'
    OR j.cllo_nm_cluster_log LIKE '%LBR-%'
    OR j.cllo_nm_cluster_log LIKE '%ES0%' )

Answer 1

As the error states, in order to use that function, you must have a minimum COMPATIBLE parameter of "12.0" You can check your current value for the COMPATIBLE parameter with the following query:

Select name,value
from v$parameter
where name like 'comp%';

You can read the details around the COMPATIBLE parameter here in the oracle documentation, including how to change it (and the ramifications of doing so):

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/what-is-oracle-database-compatibility.html