OraFAQ Forum: SQL & PL/SQL » Ora-06502 error for json

Home » SQL & PL/SQL » SQL & PL/SQL » Ora-06502 error for json_value (merged) (Oracle 12)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Ora-06502 error for json_value (merged) [message #683195]

Sun, 13 December 2020 16:04

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

Report message to a moderator

Ora-06502 error for json_value [message #683196 is a reply to message #683195]

Sun, 13 December 2020 16:04

talhaparvaiz@yahoo.com
Messages: 14
Registered: October 2020

Junior Member

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

Report message to a moderator

Re: Ora-06502 error for json_value [message #683197 is a reply to message #683195]

Mon, 14 December 2020 00:12

Michel Cadot
Messages: 68421
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

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

A feedback in your previous topic would be welcome.

Report message to a moderator

Re: Ora-06502 error for json_value [message #683199 is a reply to message #683198]

Mon, 14 December 2020 00:16

Michel Cadot
Messages: 68421
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.
...

Report message to a moderator

Re: Ora-06502 error for json_value [message #683200 is a reply to message #683197]

Mon, 14 December 2020 01:02

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

Report message to a moderator

Re: Ora-06502 error for json_value [message #683201 is a reply to message #683200]

Mon, 14 December 2020 01:29

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post your version.

Report message to a moderator

Re: Ora-06502 error for json_value [message #683204 is a reply to message #683201]

Mon, 14 December 2020 12:15

talhaparvaiz@yahoo.com
Messages: 14
Registered: October 2020

Junior Member

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Report message to a moderator

Re: Ora-06502 error for json_value [message #683205 is a reply to message #683204]

Mon, 14 December 2020 15:07

Michel Cadot
Messages: 68421
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';

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 29 22:01:39 CDT 2023