Thursday, June 03, 2021

JSON_QUERY behavior in SQL vs PL/SQL (Oracle 12.2 only?)

Frankly speaking I can't say if this is expected behavior for JSON_QUERY but I find it somewhat inconsistent:

set serveroutput on
select json_query('X', '$.test') v from dual;

So, on Oracle 12.2 in PL/SQL the string returned is not null, whilst in SQL is null.

From a test I made later on Oracle 18c the anonymous PL/SQL block above returns null, so I am inclined to consider this a bug of Oracle 12.2.

It's also worth noting that adding NULL ON ERROR clause in Oracle 12.2 doesn't change the result, which is also another possible bug (or a variation of the same problem).

Eventually I chose to use ERROR ON ERROR then catch the exception and return null, because that should avoid the risk of inconsistent results in case the db is upgraded some time in the future.

Thursday, June 25, 2020

Oracle Spatial, dynamic SQL, a view and the strange problem of a SDO_GEOMETRY bind variable

The title sound like an old italian joke and indeed I wasted 4 hours on this joke before having the epiphany.


A set of functions accepting a few parameters, in some cases one of the parameters can be a geometry object, while in other cases it's a CLOB containing a geometry in WKT format.
These functions must execute dynamic SQL queries built on some of these parameters.
The data suitable for testing lies in a different schema and the table's columns would need to be renamed, so I decided instead to create a view in that schema with the expected column names, then I granted SELECT to the schema containing the functions.

I ran the first test with functions working on CLOBs and they worked fine.
Then I ran a test on a function taking the SDO_GEOMETRY parameter whose result should be identical to the result of the previous test.
It wasn't, it returned nothing.

Among the dozens of experiments I made to sort out what was wrong, I altered the function such that it converts the SDO_GEOMETRY object back and forth within the OPEN-FOR-USING statement and to my surprise the function worked correctly.

But even if it worked well, I didn't like the fact I had to do this unnecessary step, so I continued investigating.

At a certain point, I decided to try bypassing the view, so I copied some data into a test table and repeated the test.

Now it worked

Then came the epiphany.

I granted SELECT on the base table of the view and repeated the test using the view.
It worked.

I revoked the SELECT  from the base table and, again, it stopped working.

Is it some sort of bug or is it expected behavior?
I am still undecided.
Certainly an error message would have helped to point me in the right direction much earlier.

At any rate the problem affects only the dynamic SQL containing a view in a different schema when passing a SDO_GEOMETRY value to the bind variable which is later used as argument to SDO_FILTER or any other spatial operator in the WHERE clause. If that bind variable is used as argument elsewhere, it seems to work regardless of the privilege on the base table.

I think I'll take a day off tomorrow, I have had enough for this week...

Friday, December 13, 2019

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

Always check out the original article at 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, 
       to_char(s.date_ins,'HH24:MI') as t, 
       fn_tab2str(cast(multiset(select name || ':' || value 
                                  from json_table(
                                          s.params format json, 
                                          '$[*]' error on error
                                            name   varchar2(2)  path '$.NAME',
                                            value  varchar2(5)  path '$.VALUE'
                               ) as ora_mining_varchar2_nt
                 ) as params
  from batch_statistics s,
       batch_reports r
 where s.code = r.report_code;

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

it's very likely that you might have specified an insufficient size for a column (in red).
In my case the path $.NAME returns strings of up to 255 bytes, so any value larger than 2 bytes would break the query.

See message translations for ORA-40478 and search additional resources.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles