Home » SQL & PL/SQL » SQL & PL/SQL » Ora-06502 error for json_value (merged) (Oracle 12)
Ora-06502 error for json_value (merged) [message #683195] Sun, 13 December 2020 16:04 Go to next message
talhaparvaiz@yahoo.com
Messages: 14
Registered: October 2020
Junior Member
I have a big SQL query but only one part is throwing an error message where I am extracting values stored in json_value. It's running fine for a few records (may be just a coincident) but generating the error message when I extract a larger dataset

I haven't attached a sample dataset as I am unable to find records that's throwing the error.


Error:

ORA-00604: Error occurred at recursive SQL level 1

ORA-06502: PL/SQL: NUMERIC OR VALUE error: CHARACTER string buffer too small

ORA-06512: at line 47

ORA-01722: invalid NUMBER



Code (SQL):
SELECT

TIMESTAMP '1970-01-01 00:00:00 -5:00' +numtodsinterval(json_value(json_value, '$.baseline_start')/ 1000, 'second')  AS target_start_date

FROM jir.entity_property

WHERE Entity_name = 'IssueProperty'


Thanks



Please advise
Ora-06502 error for json_value [message #683196 is a reply to message #683195] Sun, 13 December 2020 16:04 Go to previous messageGo to next message
talhaparvaiz@yahoo.com
Messages: 14
Registered: October 2020
Junior Member
I have a big SQL query but only one part is throwing an error message where I am extracting values stored in json_value. It's running fine for a few records (may be just a coincident) but generating the error message when I extract a larger dataset

I haven't attached a sample dataset as I am unable to find records that's throwing the error.


Error:

ORA-00604: Error occurred at recursive SQL level 1

ORA-06502: PL/SQL: NUMERIC OR VALUE error: CHARACTER string buffer too small

ORA-06512: at line 47

ORA-01722: invalid NUMBER



Code (SQL):
SELECT

TIMESTAMP '1970-01-01 00:00:00 -5:00' +numtodsinterval(json_value(json_value, '$.baseline_start')/ 1000, 'second')  AS target_start_date

FROM jir.entity_property

WHERE Entity_name = 'IssueProperty'


Thanks



Please advise
Re: Ora-06502 error for json_value [message #683197 is a reply to message #683195] Mon, 14 December 2020 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
It's running fine for a few records (may be just a coincident) but generating the error message when I extract a larger dataset

So some data are not valid and you have to determine which ones, we can't do it for you.

[Updated on: Mon, 14 December 2020 00:15]

Report message to a moderator

Re: Ora-06502 error for json_value [message #683198 is a reply to message #683197] Mon, 14 December 2020 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A feedback in your previous topic would be welcome.

Re: Ora-06502 error for json_value [message #683199 is a reply to message #683198] Mon, 14 December 2020 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also:

Michel Cadot wrote on Wed, 04 November 2020 06:39

From your previous topic:

Michel Cadot wrote on Sun, 11 October 2020 21:56

Michel Cadot wrote on Sun, 11 October 2020 10:22

...
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
...
Re: Ora-06502 error for json_value [message #683200 is a reply to message #683197] Mon, 14 December 2020 01:02 Go to previous messageGo to next message
talhaparvaiz@yahoo.com
Messages: 14
Registered: October 2020
Junior Member
when I pull the values for baseline_start, from Json_Value field, it's either numeric or blank.

I am unable to track where the issue is.

I know that you won't do debug for me, but is there a way to avoid the errors in SQL.

Thanks
Re: Ora-06502 error for json_value [message #683201 is a reply to message #683200] Mon, 14 December 2020 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post your version.

Re: Ora-06502 error for json_value [message #683204 is a reply to message #683201] Mon, 14 December 2020 12:15 Go to previous messageGo to next message
talhaparvaiz@yahoo.com
Messages: 14
Registered: October 2020
Junior Member
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Re: Ora-06502 error for json_value [message #683205 is a reply to message #683204] Mon, 14 December 2020 15:07 Go to previous message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From the session that gets the error, post the result of:
select value from v$nls_parameters where parameter='NLS_NUMERIC_CHARACTERS';
Previous Topic: Display Numeric Value from Text Field
Next Topic: tab_to_string for longer texts ORA-06502-numeric or value error string
Goto Forum:
  


Current Time: Sat May 15 10:03:14 CDT 2021