ORA-01720: grant option does not exist for ‘SYS.DUAL’, when compiling a view

If you are recompiling a view with create or replace view… and that view references DUAL, you may get ORA-01720: grant option does not exist for ‘SYS.DUAL’.

Why, you may ask?

Well from 11.2.0.4 onwards, Oracle corrected the permissions model on views after a long standing oversight.

In brief, you will get this ORA-1720 error when REPLACING a view that selects from some other user’s tables (or views / mviews) and both of the following conditions are true:

  • Non “select” grants exist : You have already granted privileges other than select on the VIEW to some other user.
  • Non matching grants exist on referenced tables / views / mviews : The view owner does not have the matching GRANT option on the tables / views / mviews being selected from. Incompatible grants cannot exist.

Here’s a simple demo of the error

create table t_table (val number);

create view v_view as select * from t_table;

grant all on v_view to public;

create or replace view v_view as select 2 as x from dual;

>> create or replace view v_view as select 2 as x from dual
Error at line 7
ORA-01720: grant option does not exist for 'SYS.DUAL'

The line

grant all on v_view to public;

Effectively grants DELETE, INSERT, SELECT, UPDATE, REFERENCES, MERGE VIEW, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK, READ on the view to public.

When we recompile v_view

create or replace view v_view as select 2 as x from dual;

Oracle checks DUAL has the same grants as the view (DELETE, INSERT, SELECT, UPDATE, REFERENCES, MERGE VIEW, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK, READ to public), whereas only SELECT on DUAL has been granted to public, breaking the conditions above.

The easiest way to fix the problem is to either drop the view first :

[snip...]
drop view v_view;

create view v_view as select 2 as x from dual;

View created.

Or revoke the non select grants and only add the select grant:

[snip...]

revoke all on v_view;

grant select on v_view;

create or replace view v_view as select 2 as x from dual;

View created.

Generate Numbers With Step Increment in SQL

A friend needed to generate numbers between a start and end range and with a step increment in SQL and couldn’t think how to do it, so highlighted below is the template I wrote for him. It’s very simple, but may be useful for others.

Here’s an example of its usage in SQL*Plus, producing the numbers counting between -2 and 1.5 in step increment of 0.2. Notice that 1.5 is not included? That’s because the step increment when added to previous value, 1.4, exceeds the end number. Note : You may be able to break it by using nonsensical values, but used properly it’ll work for most purposes.

set termout off

variable startnum number
variable endnum number
variable step number

exec :startnum := -2
exec :endnum   := 1.5
exec :step     := 0.2

set termout on
 
select :startnum + ((level - 1) * :step) val
from dual
connect by level <= ((:endnum - :startnum) / :step) + 1;

       VAL
----------
        -2
      -1.8
      -1.6
      -1.4
      -1.2
        -1
       -.8
       -.6
       -.4
       -.2
         0
        .2
        .4
        .6
        .8
         1
       1.2
       1.4

18 rows selected.

ORA-29283: invalid file operation

I’ve often seen questions regarding ORA-29283: invalid file operation when trying to read from or write to a file in an Oracle directory. Especially from people who have installed Oracle XE on Windows and are utilising UTL_FILE. If the Oracle Directory (an Oracle object referencing an OS directory) has been created, this could be because the Oracle Process doesn’t have the correct permissions to access the directory at Operating System (OS) level. The error will typically be something like this :

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478

Continue reading

DBMS_SQL – Executing SQL longer than 32KB

If you’re using DBMS_SQL, you may have come across the standard 32KB limit for dynamic SQL (or PL/SQL) you’re wishing to execute, when opening a DBMS_SQL cursor. To handle situations exceeding this, you have to chunk parse the SQL. Here’s a procedure which handles either situation transparently, it takes SQL as a clob (or you can pass as varchar2) and it’ll return the DBMS_SQL cursor – after parsing or chunk parsing accordingly. Either add to an existing library package or if you want to use as a standalone procedure, comment out the first line.

-- create or replace /* uncomment this line if standalone procedure */
procedure InitCursor(pSQL      in clob character set any_cs,
                     pCursorID out integer) is
  MAX_CHUNK_SIZE constant integer := 32767 - 2;  -- Max PL/SQL less 2 bytes as some Oracle versions have 32767 byte bug
  vLen pls_integer;
  vVarchar2a dbms_sql.varchar2a;
  vCnt pls_integer;
begin
  vLen := dbms_lob.getlength(pSQL);       -- Parse the query that will be used to fetch all the data to be written out...
  pCursorID := dbms_sql.open_cursor;
  if vLen <= MAX_CHUNK_SIZE then
    dbms_sql.parse(pCursorID, pSQL, dbms_sql.native);
  else
    vCnt := ceil(vLen / MAX_CHUNK_SIZE);  -- For large SQL, we need to chunk it
    for i in 1..vCnt
    loop
      vVarchar2a(i) := dbms_lob.substr(pSQL, MAX_CHUNK_SIZE, (i-1) * MAX_CHUNK_SIZE + 1);
    end loop;
    dbms_sql.parse(pCursorID, vVarchar2a, 1, vCnt, false, dbms_sql.native);
  end if;
end;
    

SQL Output to Key / Value Pairs

In this post I’ll show how you can convert SQL output into Key / Value pairs. In other words unpivoting the data such that each the Column Name becomes the KEY, and the Column Value becomes the VALUE.

[Note : This was a real world request from a poster on a forum I subscribe to.]

For example. Converting this….

OWNER     |OBJECT_NAME    |OBJECT_TYPE    |CREATED
----------|---------------|---------------|------------------------------
SYS       |I_FILE#_BLOCK# |INDEX          |04/02/2019 04:45:20
SYS       |I_OBJ3         |INDEX          |04/02/2019 04:45:20

into this…. (RECNO = row record number in dataset, COLPOS = column position in row)

KEY            |VALUE                         |     RECNO|    COLPOS
---------------|------------------------------|----------|----------
OWNER          |SYS                           |         1|         1
OBJECT_NAME    |I_FILE#_BLOCK#                |         1|         2
OBJECT_TYPE    |INDEX                         |         1|         3
CREATED        |04/02/2019 04:45:20           |         1|         4
OWNER          |SYS                           |         2|         1
OBJECT_NAME    |I_OBJ3                        |         2|         2
OBJECT_TYPE    |INDEX                         |         2|         3
CREATED        |04/02/2019 04:45:20           |         2|         4

There’s a few ways to solve this problem, one would be to use DBMS_XMLGen as I showed in a previous post. But in this example I’ll use DBMS_SQL to dynamically solve it.

Solution :

Continue reading

JSON Bugs – Mixing NULL ON NULL and ABSENT ON NULL

When generating JSON in Oracle where you might be mixing ABSENT ON NULL and NULL ON NULL in the same query block (for something like JSON_OBJECT), please be aware you might hit some issues which give you incorrect results or errors. These bugs exist in all current versions at the time of writing : 12.2c, 18c, 19c. I have logged these issues with Oracle.

SQL> 
SQL> 
SQL> -- Test 1. CORRECT. Output for val2 is null on null
SQL> select
  2         json_object (
  3           'val1' value 1
  4         , 'val2' value json_object (
  5             'childval1' value null
  6           , 'childval2' value null
  7             null on null
  8           )
  9         ) json_null_on_null
 10  from dual
 11  /

JSON_NULL_ON_NULL
-------------------------------------------------------
{"val1":1,"val2":{"childval1":null,"childval2":null}}

SQL> 
SQL> -- Test 2. CORRECT. Output for val2 is absent on null
SQL> select
  2         json_object (
  3           'val1' value 1
  4         , 'val2' value json_object (
  5             'childval1' value null
  6           , 'childval2' value null
  7             absent on null
  8           )
  9         ) json_absent_on_null
 10  from dual
 11  /

JSON_ABSENT_ON_NULL
-------------------------------------------------------
{"val1":1,"val2":{}}

SQL> 
SQL> -- Test 3. BUG. Both json_objects output as absent on null
SQL> select
  2         json_object (
  3           'val1' value 1
  4         , 'val2' value json_object (
  5             'childval1' value null
  6           , 'childval2' value null
  7             null on null
  8           )
  9         ) json_null_on_null
 10       , json_object (
 11           'val1' value 1
 12         , 'val2' value json_object (
 13             'childval1' value null
 14           , 'childval2' value null
 15             absent on null
 16           )
 17         ) json_absent_on_null
 18  from dual
 19  /

JSON_NULL_ON_NULL                                       JSON_ABSENT_ON_NULL
------------------------------------------------------- -------------------------------------------------------
{"val1":1,"val2":{}}                                    {"val1":1,"val2":{}}

SQL> 
SQL> -- Test 4. BUG. Both json_objects output as null on null
SQL> select
  2         json_object (
  3           'val1' value 1
  4         , 'val2' value json_object (
  5             'childval1' value null
  6           , 'childval2' value null
  7             absent on null
  8           )
  9         ) json_absent_on_null
 10       , json_object (
 11           'val1' value 1
 12         , 'val2' value json_object (
 13             'childval1' value null
 14           , 'childval2' value null
 15             null on null
 16           )
 17         ) json_null_on_null
 18  from dual
 19  /

JSON_ABSENT_ON_NULL                                     JSON_NULL_ON_NULL
------------------------------------------------------- -------------------------------------------------------
{"val1":1,"val2":{"childval1":null,"childval2":null}}   {"val1":1,"val2":{"childval1":null,"childval2":null}}

SQL> 
SQL> -- Test 5. BUG. ORA-40478: output value too large (maximum: 4000) ??
SQL> select
  2         json_object (
  3           'val1' value 1
  4         , 'val2' value json_object (
  5             'childval1' value null
  6           , 'childval2' value null
  7             null on null
  8           )
  9         ) json_null_on_null
 10       , json_object (
 11           'val3' value 1
 12         , 'val4' value json_object (
 13             'childval1' value null
 14           , 'childval2' value null
 15             absent on null
 16           )
 17         ) json_absent_on_null
 18  from dual
 19  /
from dual
     *
ERROR at line 18:
ORA-40478: output value too large (maximum: 4000)

Base64 Encoding and Decoding

Base64 Encoding is a binary-to-text encoding scheme that represents binary data in an ASCII string format. Base64 encoding is typically used to carry binary formats across channels that only reliably support text content. Base64 encoding is prevalent on systems Simple Mail Transfer Protocol “SMTP” and the World Wide Web, as it allows the ability to include binary files (e.g. images) inside textual assets such as HTML, CSS files and emails.

For example, encoding this tick image in Base64 Encoding would be

/9j/4AAQSkZJRgABAQEAYABgAAD/4QBoRXhpZgAATU0AKgAAAAgABAEaAAUAAAAB
AAAAPgEbAAUAAAABAAAARgEoAAMAAAABAAIAAAExAAIAAAARAAAATgAAAAAAAABg
AAAAAQAAAGAAAAABcGFpbnQubmV0IDQuMi4xNAAA/9sAQwAbEhQXFBEbFxYXHhwb
IChCKyglJShROj0wQmBVZWRfVV1baniZgWpxkHNbXYW1hpCeo6utq2eAvMm6pseZ
qKuk/9sAQwEcHh4oIyhOKytOpG5dbqSkpKSkpKSkpKSkpKSkpKSkpKSkpKSkpKSk
pKSkpKSkpKSkpKSkpKSkpKSkpKSkpKSk/8AAEQgAKAAyAwEhAAIRAQMRAf/EAB8A
AAEFAQEBAQEBAAAAAAAAAAABAgMEBQYHCAkKC//EALUQAAIBAwMCBAMFBQQEAAAB
fQECAwAEEQUSITFBBhNRYQcicRQygZGhCCNCscEVUtHwJDNicoIJChYXGBkaJSYn
KCkqNDU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6g4SFhoeI
iYqSk5SVlpeYmZqio6Slpqeoqaqys7S1tre4ubrCw8TFxsfIycrS09TV1tfY2drh
4uPk5ebn6Onq8fLz9PX29/j5+v/EAB8BAAMBAQEBAQEBAQEAAAAAAAABAgMEBQYH
CAkKC//EALURAAIBAgQEAwQHBQQEAAECdwABAgMRBAUhMQYSQVEHYXETIjKBCBRC
kaGxwQkjM1LwFWJy0QoWJDThJfEXGBkaJicoKSo1Njc4OTpDREVGR0hJSlNUVVZX
WFlaY2RlZmdoaWpzdHV2d3h5eoKDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKz
tLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uLj5OXm5+jp6vLz9PX29/j5+v/aAAwD
AQACEQMRAD8A5migAooA0dM0/wC0fvp/lgX143f/AFqfe6WNnn2Z8yI87RyR9K5X
XtUt0MHVtO3Qy6K6jcKKACr+maeblvNl+WBep9azqz5ItkTlyxuTXl097KLOzGIh
xxxn/wCtU8s0ekWvkRtvnbk+3vXM4aKl1erMeXRQ+8xGZmYsSSSck0V22OkvWGmS
XX7yT93CP4j3+lXJbCzu4yLJ1EkfHXg/X/GuSpWal7uy3OedRqWmyKtlpUsk5+0K
Y40PzZ7/AEqW9umunWysh+76cd//AK1NyVSd+i1G2pyv0RMzQ6Pb7Ew9y45P+e1Y
sjtI5dyWZjkk1VFN3qPqOkr3m+o2iug2NGw1RoR5M48yE8e4H+FTz2BXF3pr5HXa
p6fT/CuSS9lO/wBlnO1yS8mQzajc30aWyJh24bb/ABf4VaPk6NbcYe5cf5/ClKCi
lSj1/ITja1NdTFlkeWQySMWZuSTTa60rKyOlKwUUwCrFney2b5jOVPVT0NTOKmrM
mUVJWZqtqVmkTXMUY+0Pxtxzn3rFlleaRpJGLM3U1hQpuN3LczpQau5DKK6TYKKA
P//Z

That text could then be used to send the image via say a webservice.

Oracle’s provides some Base64 routines in UTL_ENCODE (Base64_Decode and Base64_Encode), but these will only process RAWs (raw byte data) and sizes up to a maximum of 32KB. This isn’t particularly useful for many real world scenarios, which may be needed for data types other than RAW and for larger sizes. So I decided to write a Base64 package which offers a much richer set of routines to help Oracle developers.

You can download my P_Base64 package from here : /paulzip-dev/Base64

JSON_Object Absent or Null On Empty

I was recently calling a third party JSON based REST web-service, where it didn’t like an empty JSON object value. Consider these simple examples…

-- Example 1. This would be OK
{"key1":1,"key2":{"childkey1":1,"childkey2":2}}  

-- Example 2. This would be OK
{"key1":1,"key2":{"childkey1":1}}  

-- Example 3. This caused errors as key2's value is empty
{"key1":1,"key2":{}}

Oracle’s JSON_Object function does have a clause to determine the action to take when one of the JSON object’s key’s values evaluates to null : absent on null (omits the property key-value pair) or null on null (return the JSON null value), but it doesn’t provide handling for when it is empty, which was what I needed. So here’s how I achieved that :

with data(val1, childval1, childval2) as (
  select 'val1', 'childval1', 'childval2' from dual union all
  select 'val1', 'childval1', null from dual union all
  select 'val1', null, null from dual
)
select val1, childval1, childval2
     , json_object (
         'key1' value val1
       , 'key2' value case
                        when json_exists(val2, '$.*') then val2
                        else null
                      end
         absent on null
       ) json_fix
from (
  select d.*,
         json_object (
           'childkey1' value childval1
         , 'childkey2' value childval2
           absent on null
         ) val2
  from data d
) d
/

VAL1 |CHILDVAL1|CHILDVAL2|JSON_FIX
-----|---------|---------|---------------------------------------------------------------------------
val1 |childval1|childval2|{"key1":"val1","key2":{"childkey1":"childval1","childkey2":"childval2"}}
val1 |childval1|         |{"key1":"val1","key2":{"childkey1":"childval1"}}
val1 |         |         |{"key1":"val1"}

I simply created the JSON object content in the inline view for the key that couldn’t have an empty value, and then used a case statement along with JSON_Exists which checked for the existence of any objects or elements within val2. ‘$.*’ = Any object or elements

SPLIT_PART function for Oracle

Someone asked if Oracle had an equivalent to the useful SPLIT_PART function that is found in several other relational database systems like PostgreSQL Snowflake, Amazon Redshift, Vertica, Hadoop etc. There wasn’t so I decided to write one.

Definition :

SPLIT_PART(<string> varchar2, <delimiter> varchar2, <partnumber> number) return varchar2

Splits a given string at a specified delimiter and returns the requested part.

Parameters :
string : Text to be split into parts.

delimiter : Text representing the delimiter to split by, this can be multiple characters.

partnumber : Requested part of the split (index is 1 based).

Notes :

  1. If the partnumber is 0, the returned value is null. (Snowflake maps 0 to 1)
  2. If partnumber is negative, the parts are counted backward from the end of the string. (Some DBs don’t support negative partnumber
  3. If the partnumber is out of range, the returned value is null.
  4. If the string starts or is terminated with the delimiter, the system considers empty space before or after the delimiter, respectively, as a valid part of the split result. See examples below.
  5. If the delimiter is null, then an effective delimiter is considered to exist at the boundary of the direction of search on the string. See examples below.
Examples :
-- partNumber = 0
split_part('one,two,three', ',',  0) = {null}

-- Positive partNumber
split_part('one,two,three', ',',  1) = one
split_part('one,two,three', ',',  2) = two
split_part('one,two,three', ',',  3) = three
split_part('one,two,three', ',',  4) = {null}

-- Negative partNumber, parts counted backward
split_part('one,two,three', ',', -1) = three
split_part('one,two,three', ',', -2) = two
split_part('one,two,three', ',', -3) = one
split_part('one,two,three', ',', -4) = {null}

-- Started / ended with delimiter
split_part(',two,three,', ',',  1) = {null}
split_part(',two,three,', ',',  2) = two
split_part(',two,three,', ',',  3) = three
split_part(',two,three,', ',',  4) = {null}
split_part(',two,three,', ',', -1) = {null}
split_part(',two,three,', ',', -2) = three
split_part(',two,three,', ',', -3) = two
split_part(',two,three,', ',', -4) = {null}

-- Null delimiter
split_part('one,two,three', null,  1) = one,two,three
split_part('one,two,three', null,  2) = {null}
split_part('one,two,three', null, -1) = one,two,three
split_part('one,two,three', null, -2) = {null}
Solution :
Continue reading

Oracle JSON and Empty Strings “”

Oracle’s support of JSON from v12 onwards is pretty comprehensive, however it does lack support for generating empty string “” values. Oracle is very clear about this limitation in their JSON documentation :

Because Oracle SQL treats an empty string as NULL there is no way to construct an empty JSON string ("").

Oracle Documents : Generation of JSON Data With SQL/JSON Functions

However, empty string and null are two distinctly separate concepts in data and particularly JSON, so this is a problem for developers, especially if their use case needs it. Something I experienced recently, when a RESTful API I was interfacing with wouldn’t handle nulls for key values but would handle empty strings. In this post I explore some approaches to work around this empty string “” limitation.

Continue reading