Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Scott.

Asked: December 09, 2016 - 3:51 am UTC

Last updated: February 27, 2018 - 11:36 am UTC

Version: 12.1

Viewed 10K+ times! This question is

You Asked

We have a problem with a JSON query retruning ORA-01460. I thin it's because it's referring to an actual parameter formally defined as varchar2.
This string is > 4000 (about 4400 chars).

I see this error can be replicated by referring to a large PL/SQL variable within a SQL statement
http://www.orafaq.com/forum/t/191946/ 

declare
VV varchar2(32767);
NN NUMBER;
begin
VV:=RPAD('*',30000,1);
select length(VV) into NN from DUAL;
end;
/

ORA-01460: unimplemented or unreasonable conversion requested


So I though I could extrapolate this to JSON queries, since it's mentioned here, but in regard to pulling data from a table.column with varchar2(32767)
https://blogs.oracle.com/jsondb/entry/storing_json_in_the_oracle

I'm not sure how to sanitise my example as a simple test case with JSON, I'm not familiar enough with the tool yet.

But I see on liveslq.oracle.com that this returns EXTENDED
show parameter max_string_size;
And the simple RPAD above returns successfully.

Our database still has max_string_size:standard. Do you think this issue would be resolved by modifying that parameter?
What (real world) implications should we consider? I see some suggestions in the documentation ( https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321 ), but I don' thitnk we'll hit any of those.
We updated our character set earlier in the year, but left this alone.

and Chris said...

As you say, extended data types solve the ORA-01460 in your example:

SQL> select value from v$parameter
  2  where  name = 'max_string_size';

VALUE
----------------------------------------------

EXTENDED

SQL>
SQL> declare
  2  VV varchar2(32767);
  3  NN NUMBER;
  4  begin
  5  VV:=RPAD('*',30000,1);
  6  select length(VV) into NN from DUAL;
  7  end;
  8  /

PL/SQL procedure successfully completed.


I don't know enough about your JSON issue to say this will solve it. But it's worth investigating if you're hitting ORA-01460s.

On extended data types, Oracle stores more like mini-clobs than large varchar2s:

http://www.ludovicocaldara.net/dba/extended-data-types-storage/

But they don't have the round-trip fetching cost of clobs:

http://blog.dbi-services.com/12c-extended-datatypes-better-than-clob/

So as always, test and check in your environment.

The big driver for using these comes in 12.2. This introduces case-insensitive searching with column level collation. But if you want to use it you must set max_string_size = extended:

http://docs.oracle.com/database/122/LNPLS/DEFAULT-COLLATION-clause.htm#BEGIN

So worth looking into in the long term.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reduced sql calls

Rajeshwaran, Jeyabal, December 10, 2016 - 6:35 am UTC

If, you could reduce the SQL Calls, this error ORA-01460 could be eliminated, not sure if this could solve your JSON issue.

demo@ORA12C> show parameter max_string_size

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- ---------------------
max_string_size                               string      STANDARD
demo@ORA12C> declare
  2  VV varchar2(32767);
  3  NN NUMBER;
  4  begin
  5  VV:=RPAD('*',30000,1);
  6  select length(VV) into NN from DUAL;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01460: unimplemented or unreasonable conversion requested
ORA-06512: at line 6


demo@ORA12C> declare
  2  VV varchar2(32767);
  3  NN NUMBER;
  4  begin
  5  VV:=RPAD('*',30000,1);
  6  nn := length(vv);
  7  dbms_output.put_line( 'nn ='||nn);
  8  end;
  9  /
nn =30000

PL/SQL procedure successfully completed.

demo@ORA12C>

JSON_VALUE operator on TRUNCATE option

Rajeshwaran, Jeyabal, January 18, 2017 - 6:55 am UTC

Team,

I was reading through this blog post from JSON team
https://blogs.oracle.com/jsondb/entry/s
Where they have shown that it is possible to TRUNCATE the output of the JSON_VALUE function, but that is not working with 12c(12.1.0.2) and don't even find that option is available in product documentation also.

The script to create and populate the tables are available, in the above blog post.
demo@ORA12C> SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10) TRUNCATE)
  2  FROM colorTab;
SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10) TRUNCATE)
                                                         *
ERROR at line 1:
ORA-00907: missing right parenthesis


demo@ORA12C>

JSON_VALUE returning clause, doesn't show anything about the TRUNCATE option.
http://docs.oracle.com/database/121/SQLRF/functions093.htm#CJABADIE
Please advise.
Chris Saxon
January 18, 2017 - 3:19 pm UTC

If you think there's a mistake in a blog post, it's better to comment on the post! That way the author can address your comments directly...

Anyway, this option is available in 12.2:

select * from v$version;

BANNER                                                                        CON_ID  
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production  0       
PL/SQL Release 12.2.0.1.0 - Production                                        0       
CORE 12.2.0.1.0 Production                                                    0       
TNS for Linux: Version 12.2.0.1.0 - Production                                0       
NLSRTL Version 12.2.0.1.0 - Production                                        0 

CREATE TABLE colorTab (
  color VARCHAR2(4000), CONSTRAINT is_j CHECK (color IS JSON STRICT)
);

INSERT INTO colorTab VALUES('
{
"name": "black",
"rgb": [0,0,0],
"hex": "#000000"
}');

SELECT JSON_VALUE(color, '$.name' RETURNING VARCHAR2(10) truncate)
FROM colorTab;

JSON_VALUE(COLOR,'$.NAME'RETURNINGVARCHAR2(10)TRUNCATE)  
black


Though it's still undocumented...

http://docs.oracle.com/database/122/SQLRF/JSON_VALUE.htm#SQLRF56668

JSON_EXISTS

Rajeshwaran, Jeyabal, January 31, 2017 - 9:10 am UTC

Team,

Practicing this at my local instance running in 12.1.0.2.
https://blogs.oracle.com/jsondb/entry/the_new_sql_json_query1 
drop table t purge;
create table t( doc_id  int, 
 doc_details varchar2(1000),
 constraint valid_json_check check(doc_details is json) );

insert into t values (1 , '{"id":1, "name" : "Jeff"}' );
insert into t values (2 , '{"id":2, "name" : "Jane", "status":"Gold"}' );
insert into t values (3 , '{"id":3, "name" : "Jill", "status":["Important","Gold"]}' );
insert into t values (4 , '{"name" : "John", "status":"Silver"}' );
commit;

when running this query using @ notation, doesn't work in 12.1
demo@ORA12C> select * from t
  2  where json_exists(doc_details,'$.status?(@ == "Gold")') ;
where json_exists(doc_details,'$.status?(@ == "Gold")')
                                                       *
ERROR at line 2:
ORA-40442: JSON path expression syntax error


if this is not supported in 12.1, then what would be the workaround in this case?
Chris Saxon
January 31, 2017 - 2:09 pm UTC

This is working in 12.2:

select * from v$version;

BANNER                                                                        CON_ID  
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production  0       
PL/SQL Release 12.2.0.1.0 - Production                                        0       
CORE 12.2.0.1.0 Production                                                    0       
TNS for Linux: Version 12.2.0.1.0 - Production                                0       
NLSRTL Version 12.2.0.1.0 - Production                                        0

create table t( doc_id  int, 
 doc_details varchar2(1000),
 constraint valid_json_check check(doc_details is json) );

insert into t values (1 , '{"id":1, "name" : "Jeff"}' );
insert into t values (2 , '{"id":2, "name" : "Jane", "status":"Gold"}' );
insert into t values (3 , '{"id":3, "name" : "Jill", "status":["Important","Gold"]}' );
insert into t values (4 , '{"name" : "John", "status":"Silver"}' );
commit; 

select * from t
where json_exists(doc_details,'$.status?(@ == "Gold")') ;

DOC_ID  DOC_DETAILS                                               
2       {"id":2, "name" : "Jane", "status":"Gold"}                
3       {"id":3, "name" : "Jill", "status":["Important","Gold"]}


In the meantime you can workaround this by:

- Creating a JSON text index
- Using JSON_textcontains:

create index i on t(doc_details)
  indextype is ctxsys.context
  parameters ('section group ctxsys.json_section_group sync (on commit)');

select * from t
where json_textcontains(doc_details,'$.status', 'Gold') ;

DOC_ID  DOC_DETAILS                                               
2       {"id":2, "name" : "Jane", "status":"Gold"}                
3       {"id":3, "name" : "Jill", "status":["Important","Gold"]}

Simple access on JSON objects

Rajeshwaran, Jeyabal, February 13, 2017 - 10:06 am UTC

Team - did i miss something here ?

demo@ORA12C>
demo@ORA12C> CREATE TABLE colorTab (
  2  id NUMBER,
  3  color VARCHAR2(1000),
  4  CONSTRAINT ensure_json2 CHECK (color IS JSON STRICT));

Table created.

demo@ORA12C>
demo@ORA12C> INSERT INTO colorTab VALUES(1, '
  2  {
  3  "name": "black",
  4  "rgb": [0,0,0],
  5  "hex": "#000000"
  6  }');

1 row created.

demo@ORA12C>
demo@ORA12C> INSERT INTO colorTab VALUES(2, '
  2  {
  3  "name": "orange red",
  4  "rgb": [255,69,0],
  5  "hex": "#FF4500"
  6  }');

1 row created.

demo@ORA12C> SELECT
  2  c.color.rgb[0] "RED",
  3  c.color.rgb[1] "GREEN",
  4  c.color.rgb[2] "BLUE" FROM colorTab c;
c.color.rgb[0] "RED",
           *
ERROR at line 2:
ORA-00923: FROM keyword not found where expected


demo@ORA12C>

Connor McDonald
February 13, 2017 - 11:02 pm UTC

12.2 not 12.1

SQL> CREATE TABLE colorTab (
  2      id NUMBER,
  3      color VARCHAR2(1000),
  4      CONSTRAINT ensure_json2 CHECK (color IS JSON STRICT));

Table created.

SQL>
SQL> INSERT INTO colorTab VALUES(1, '
  2  {
  3  "name": "black",
  4  "rgb": [0,0,0],
  5  "hex": "#000000"
  6  }');

1 row created.

SQL>
SQL> INSERT INTO colorTab VALUES(2, '
  2  {
  3  "name": "orange red",
  4  "rgb": [255,69,0],
  5  "hex": "#FF4500"
  6  }');

1 row created.

SQL>
SQL> SELECT
  2      c.color.rgb[0] "RED",
  3      c.color.rgb[1] "GREEN",
  4      c.color.rgb[2] "BLUE" FROM colorTab c;

RED
---------------------------------------------------------------------
GREEN
---------------------------------------------------------------------
BLUE
---------------------------------------------------------------------
0
0
0

255
69
0


Simple access on JSON objects

Rajeshwaran, Jeyabal, February 14, 2017 - 6:34 am UTC

Well the document doesn't say about this restriction in 12.1
http://docs.oracle.com/database/121/ADXDB/json.htm#GUID-7249417B-A337-4854-8040-192D5CEFD576
<quote>
po.po_document.LineItems[1] – The second element of array LineItems (array positions are zero-based).
</quote>

also the JSON blog doesn't say this restriction in 12.1
https://blogs.oracle.com/jsondb/entry/simple_queries

JSON_ITEM_METHOD in 12.2

Rajeshwaran, March 30, 2017 - 10:54 am UTC

Team,

was reading about SQL/JSON path expression syntax here.
http://docs.oracle.com/database/122/ADJSN/json-path-expressions.htm#ADXDB6372
where we have the flexibility to define JSON_ITEM_METHOD in the path description.
drop table t purge;
create table t(custdata varchar2(100) ,
 constraint ensure_json check(
  custdata IS JSON ) );
insert into t values ('{"id":1, "name" : "Jeff"}');
insert into t values ('{"id":2, "name" : "Jane", "status":"Gold"}');
insert into t values ('{"id":3, "name" : "Jill", "status":["Important","Gold"]}');
insert into t values ('{"name" : "John", "status":"Silver"}');
commit;  

demo@ORA12C> column cust_names format a20
demo@ORA12C> select json_value(custdata,'$.name') as cust_names
  2  from t  ;

CUST_NAMES
--------------------
Jeff
Jane
Jill
John

demo@ORA12C> select json_value(custdata,'$.name.length()') as cust_names
  2  from t  ;

CUST_NAMES
--------------------
Jeff
Jane
Jill
John

demo@ORA12C>

dont see the JSON_ITEM_METHOD returning the length of the targeted string. did i missing something here? kindly advise.
Connor McDonald
April 03, 2017 - 12:42 am UTC

This is a documentation bug. Those operators are meant to only be listed as valid for JSON_EXSITS, eg

SQL> drop table t purge;

Table dropped.

SQL> create table t(custdata varchar2(100) ,
  2   constraint ensure_json check(
  3    custdata IS JSON ) );

Table created.

SQL> insert into t values ('{"id":1, "name" : "Jeff"}');

1 row created.

SQL> insert into t values ('{"id":2, "name" : "Janet", "status":"Gold"}');

1 row created.

SQL> insert into t values ('{"id":3, "name" : "Simon", "status":["Important","Gold"]}');

1 row created.

SQL> insert into t values ('{"name" : "John", "status":"Silver"}');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> column cust_names format a20
SQL> select json_value(custdata,'$.name') as cust_names
  2  from t  ;

CUST_NAMES
--------------------
Jeff
Janet
Simon
John

4 rows selected.

SQL>
SQL> select json_value(custdata,'$.name') as cust_names
  2  from t
  3  where json_exists(custdata,'$.name?(@.length() == 4)');

CUST_NAMES
--------------------
Jeff
John


boolean values wrapped

Rajeshwaran, Jeyabal, February 16, 2018 - 12:33 pm UTC 

demo@ORA12C> column b1 format a10
demo@ORA12C> column b2 format a10 trunc
demo@ORA12C> column b3 format a10 trunc
demo@ORA12C> select t2.*
  2  from j_purchaseorder , json_table( po_document ,'$'
  3              columns( b1 varchar2(10) exists path '$.ShippingInstructions.Phone.number' error on error ,
  4                  b2 varchar2(100) format json with conditional wrapper path '$.ShippingInstructions.Phone.number',
  5                  b3 varchar2(130) format json with conditional wrapper path '$.ShippingInstructions.Address') ) t2
  6  /

B1         B2         B3
---------- ---------- ----------
[true]     ["909-555- {"street":
false                 {"street":

demo@ORA12C>


Team,

Could you tell me why the first row first column in the sql output get boolean value wrapped? but the same is not wrapped in the second row first column in the output.
we are on 12.2.0.1, please find below the create table and insert statements for the above sql.

CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   po_document VARCHAR2 (4000)
   CONSTRAINT ensure_json CHECK (po_document IS JSON));
   
INSERT INTO j_purchaseorder
  VALUES (
    SYS_GUID(),
    to_date('30-DEC-2014'),
    '{"PONumber"             : 1600,
      "Reference"            : "ABULL-20140421",
      "Requestor"            : "Alexis Bull",
      "User"                 : "ABULL",
      "CostCenter"           : "A50",
      "ShippingInstructions" : {"name"    : "Alexis Bull",
                                "Address" : {"street"  : "200 Sporting Green",
                                             "city"    : "South San Francisco",
                                             "state"   : "CA",
                                             "zipCode" : 99236,
                                             "country" : "United States of America"},
                                "Phone"   : [{"type" : "Office", "number" : "909-555-7307"},
                                             {"type" : "Mobile", "number" : "415-555-1234"}]},
      "Special Instructions" : null,
      "AllowPartialShipment" : true,
      "LineItems"            : [{"ItemNumber" : 1,
                                 "Part"       : {"Description" : "One Magic Christmas",
                                                 "UnitPrice"   : 19.95,
                                                 "UPCCode"     : 13131092899},
                                 "Quantity"   : 9.0},
                                {"ItemNumber" : 2,
                                 "Part"       : {"Description" : "Lethal Weapon",
                                                 "UnitPrice"   : 19.95,
                                                 "UPCCode"     : 85391628927},
                                 "Quantity"   : 5.0}]}');
         
         
INSERT INTO j_purchaseorder
  VALUES (
    SYS_GUID(),
    to_date('30-DEC-2014'),
    '{"PONumber"             : 672,
      "Reference"            : "SBELL-20141017",
      "Requestor"            : "Sarah Bell",
      "User"                 : "SBELL",
      "CostCenter"           : "A50",
      "ShippingInstructions" : {"name"    : "Sarah Bell",
                                "Address" : {"street"  : "200 Sporting Green",
                                             "city"    : "South San Francisco",
                                             "state"   : "CA",
                                             "zipCode" : 99236,
                                             "country" : "United States of America"},
                                "Phone"   : "983-555-6509"},
      "Special Instructions" : "Courier",
      "LineItems"            : [{"ItemNumber" : 1,
                                 "Part"       : {"Description" : "Making the Grade",
                                                 "UnitPrice"   : 20,
                                                 "UPCCode"     : 27616867759},
                                 "Quantity"   : 8.0},
                                {"ItemNumber" : 2,
                                 "Part"       : {"Description" : "Nixon",
                                                 "UnitPrice"   : 19.95,
                                                 "UPCCode"     : 717951002396},
                                 "Quantity"   : 5},
                                {"ItemNumber" : 3,
                                 "Part"       : {"Description" : "Eric Clapton: Best Of 1981-1999",
                                                 "UnitPrice"   : 19.95,
                                                 "UPCCode"     : 75993851120},
                                 "Quantity"   : 5.0}
                                ]}');
commmit;

Chris Saxon
February 27, 2018 - 11:36 am UTC

It's a bug. We've filed it for you. If you need a patch for this please let us know.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here