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.

invalid LOB locator specified: ORA-22275

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

Just a simple reminder (I tend to forget things over the centuries) when using procedure DBMS_LOB.CONVERTTOCLOB with persistent lob locators:

1. don't forget to include the FOR UPDATE clause in the SELECT statement fetching the lob locators. 
2. don't forget to initialize the destination lob (in this case the clob column DOC)  with EMPTY_CLOB().

otherwise ORA-22275 may occur (see also another cause of this error).

...
 select doc, bdoc, charset
   into l_clob, l_blob, l_cset
   from file_imports
  where id = p_fileid
    for update;

...
 dbms_lob.converttoclob(
  l_clob,
  l_blob,
  dbms_lob.lobmaxsize,
  l_coff,
  l_boff, 
  l_cset,
  l_context,
  l_warn);
...
 
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 

In the PL/SQL fragment above, column DOC was null, which is a different value from EMPTY_CLOB().
As this column is always null when the record is created, I modified the default value of the column as follows:

alter table file_imports modify doc default empty_clob();

This avoids the annoyance of having to update the row and set DOC to EMPTY_CLOB() before calling DBMS_LOB.CONVERTTOCLOB.


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

ORA-00942 and ORA-06512 at SYS.XMLTYPE

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

You may get the following puzzling stack of error messages when you attempt to use the XMLTYPE constructor on an empty CLOB.
This error can be easily reproduced in the following way:

select xmltype(empty_clob()) as x
from dual;

ORA-00942: table or view does not exist
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1

Most likely, in a real life scenario, you initialized a CLOB column with the special value EMPTY_CLOB(), that is standard practice when you need to insert a CLOB value into a table as the result of a conversion from a BLOB type, as shown in this PL/SQL snippet:

...
insert into file_store
      (  name, document, created_by)
values (l_name, empty_clob(), p_user)
returning id, document into l_id, l_clob;

dbms_lob.convertToClob(l_clob, l_blob, DBMS_LOB.LOBMAXSIZE, l_dest_off, l_src_off, l_csid, l_shift, l_warning);
...

Note that you can compare the lob locator with the value EMPTY_CLOB().

...
if l_clob = empty_clob() then
 -- lob locator is empty
...
elsif l_clob is null then

 -- lob locator is atomically null 
...
else

 -- lob locator contains a non empty CLOB
...
end if;
...

in case you need to distinguish between a null CLOB locator and an empty CLOB locator.

Once you are sure that the document is not empty, you can try to apply the XMLType constructor and see if you get any parsing errors.

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

ORA-06572: Function %s has out arguments

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

This error is returned when you are (typically) trying to execute a user-defined function inside a SQL statement and the function contains some parameter defined either as OUT or as IN/OUT.

ORA-06572: Function CLOB_TO_BLOB has out arguments

Most built-in functions do not use OUT parameters however it is perfectly acceptable to build packaged functions where such type of parameters are present, if not even required, to increase performance and reduce temporary memory allocation.

Although in the specific case of this CLOB_TO_BLOB function, the p_clob parameter has been declared as IN OUT but there isn't a NOCOPY declaration following it, which looks odd to me, normally the IN OUT declaration is a requirement for NOCOPY.

The trade-off is in that any attempt of using such functions in a SQL statement fails with the aforementioned error.

So, what if you need to iterate the function for each row of a given set?

There are two possibilities:

• one is to convert the implicit cursor (the SQL statement) into an explicit cursor (FOR cursor LOOP).
• the second one is to write a wrapper function;

implicit cursor, raising the error:

create table my_blobs(id number, tgt_blob blob)
/
create table my_clobs(id number, src_clob clob)
/

insert into my_blobs(id, tgt_blob)
select id, wwv_flow_utilities.clob_to_blob(src_clob, 'AL32UTF8')
FROM my_clobs;

ORA-06572: Function CLOB_TO_BLOB has out arguments

explicit cursor:

DECLARE
myblob blob;
BEGIN
FOR cur_clob in (SELECT id, src_clob FROM my_clobs) LOOP
myblob := wwv_flow_utilities.clob_to_blob(cur_clob.src_clob, 'AL32UTF8');
insert into my_blobs(id, tgt_blob) values(cur_clob.id, myblob);
END LOOP;
END;

or a wrapper function:

create or replace
function wrap_c2b (
p_clob in clob,
p_charset in varchar2)
return blob
as
tmp_clob clob := p_clob;
begin
return wwv_flow_utilities.CLOB_TO_BLOB(tmp_clob, p_charset);
end;

insert into my_blobs(id, tgt_blob)
select id, wrap_c2b(src_clob, 'AL32UTF8')
FROM my_clobs;

The wrapper function approach may cause performance degradation owing to the extra copies of the CLOBs causes by the passing of the source CLOB by value, which results in a plethora of temporary LOBs, so use it only if necessary (i.e. in your development environment, not on a production server...).

Usage of the explicit cursor seems to me a much more sensible way of using database resources, especially if the relevant parameter(s) of the function have been declared as NOCOPY.

See message translations for ORA-06572 and search additional resources