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.

Scenario:

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.
Bingo.

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 http://www.oraclequirks.com 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, 
       s.date_ins, 
       to_char(s.date_ins,'HH24:MI') as t, 
       r.report_name,
       fn_tab2str(cast(multiset(select name || ':' || value 
                                  from json_table(
                                          s.params format json, 
                                          '$[*]' error on error
                                          columns(
                                            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.

Monday, October 28, 2019

GRANT INHERIT PRIVILEGES

As per Oracle 12.2 SQL Reference:

GRANT INHERIT PRIVILEGES TO user

"Execute invoker's rights procedures or functions owned by the grantee with the privileges of the invoker when the invoker is the user on whom this privilege is granted."


If there is someone who can translate in plain english the sentence above, please contact me ASAP, I read it a dozen times and still can't make a sense out of it.

UPDATED 2020/2/13: in the comments a link to a useful article explaining the issue in detail with examples.

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