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.


Thursday, October 10, 2019

Pretty print JSON strings in Oracle 12.2 the easy way

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

It turns out that in Oracle 12.2 you can easily get pretty printed large JSON strings using a query like this:

select json_query(large_json_value, '$' returning clob pretty) as json_clob
from some_table;

Note however that the syntax returning clob pretty is not mentioned in the documentation, where returning varchar2(n) pretty (with n <= 4000) seems to be the only valid syntax.


I really appreciated the hidden feature because now I can quickly export a bunch of JSON strings  automatically generated with views using nested JSON_ARRAYAGG and JSON_OBJECT SQL functions.


Thursday, October 03, 2019

How to rename an oracle scheduler job

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


If you ever wondered if it is possible to rename a job, be advised that you can execute:

RENAME old_name TO new_name;

Apparently this is the only way to do it as there isn't an ALTER JOB command or a DBMS_SCHEDULER API for doing that and interestingly it's not mentioned in the documentation.

If you need to rename a job on an instance where only DBAs are allowed to execute such operations, you need to provide them with a simple script like this:

ALTER SESSION SET CURRENT_SCHEMA = job_schema;
RENAME old_name TO new_name;

I am not sure if you can rename a running job, probably not which makes sense to me, at any rate it's meaningful in case of recurring calendar jobs if the original name needs to be changed for some reason.


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