Thursday, January 10, 2019

ALTER SESSION RESET parameter works but doesn't work

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I was wondering why it's not possible to reset a session modifiable parameter to its default value (that is the system wide current value), then just out of curiosity I tried to execute the following:

ALTER SESSION RESET SPATIAL_VECTOR_ACCELERATION;
 
The statement executed without a hitch in Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.

Unfortunately it didn't reset anything to its original value.

Note that this syntax is not supported in the syntax diagram, so it works but it doesn't do what (is not) advertised.

May be in a future release?

By the way, the only workaround I could think of in order to save the initial value of a session parameter in a session-permanent place is to store it into a named context parameter (provided you don't clear the context at any time somewhere else...).
Storing it into a packaged variable can lead to false results in case the package state is reset after altering the value of the session parameter.


If anybody has better ideas, I'll be glad to know.

Thursday, November 15, 2018

ORA-01720: grant option does not exist for SCHEMA.TABLE

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You are getting ORA-01720: grant option does not exist for SCHEMA.SOME_TABLE while trying to execute CREATE OR REPLACE VIEW V_MY_VIEW because you added the new table SOME_TABLE residing on a different schema and you lack privileges on SOME_TABLE. The you go and ask for SELECT privilege on the table SOME_TABLE with GRANT OPTION.
After obtaining the privilege you retry the operation but you still get:

ORA-01720: grant option does not exist for SCHEMA.SOME_TABLE


This happens because since version 11g you cannot simply replace a view containing a newly added table even if you have the SELECT privilege WITH GRANT OPTION.

You need to DROP VIEW V_MY_VIEW first and then re-create it.

The error message is misleading to say the least, a more specific error should be raised instead in these situations.

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

Friday, October 26, 2018

When SDO_GEOM.SDO_AREA returns a negative value

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

If function SDO_GEOM.SDO_AREA returns a negative number chances are that your geometry is invalid.

For instance I checked the geometry with function SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT and it returned:

13349 [Element <1>] [Ring <1>][Edge <2>][Edge <1>]

which means that the polygon boundary crosses itself.
The same applies when code 13356 is returned (adjacent points in a geometry are redundant).

Please note that the absolute value is still correct.
In most cases you should be able to fix the geometry by applying function SDO_GEOM.SDO_SELF_UNION.

This happens on Oracle 12.1

See message translations for ORA-13349, ORA-13356 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