Skip to Main Content
  • Questions
  • Intermittent Ora-06502: PL/SQL: numeric or value error - For Update Skip locked

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Balu.

Asked: August 27, 2016 - 3:52 am UTC

Last updated: October 21, 2021 - 1:37 am UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hi Tom,

We are getting Intermittent Ora-06502: PL/SQL: numeric or value error - For Update Skip locked. This error occurs only when application (.Net) calls this SP and it occurs at last pending row from table. But we are able to execute same SP from TOAD succefully.

Please help me to find cause.

SP code from Package:
Procedure getNextRequest(
i_repository_id IN VARCHAR2,
i_host_name IN VARCHAR2,
i_instance_name IN VARChar2,
o_platform_req_guid OUT VARCHAR2,
o_process_id OUT NUMBER,
o_request_info OUT SYS_REFCURSOR,
o_document_info OUT SYS_REFCURSOR,
o_error_code OUT varchar2,
o_error_msg OUT VARCHAR2)
IS
v_child_process_id NUMBER;
v_platform_request_guid VARCHAR2(32);
v_update_dttm TIMESTAMP;
v_pending_rows varchar2(1) := 'N';
Cursor c1 is
select q.child_process_id, q.platform_request_guid
from request_process_queue q
inner join request r
on ( p.platform_request_guid = r.platform_request_guid )
inner join request_key_val_set k
on ( p.platform_request_guid = r.platform_request_guid )
where statu = 'PND'
and k.req_key_name ='REPOSITORY_ID'
and k.req_key_value1 = i_repository_id
order by request_priority, child_process_id;

Pragma Autonomous_transaction;
BEGIN

If i_repository_id is null or i_host_name is null or i_instance_name is null then
o_error_code :='30010';
o_error_msg := 'Missing parameters';
raise e_missing_parameters;
end if;

open c1;
loop
fetch c1 into v_child_process_id, v_platform_request_guid;
exit when c1%notfound;
begin
select child_process_id, platform_request_guid
into o_child_process_id, o_platform_req_guid
from request_process_queue
where child_process_id = v_child_process_id
and platform_request_guid = v_platform_request_guid;
and status = 'PND' FOR UPDATE SKIP LOCKED;

v_update_dttm := sys_extract_utc(current_timestamp);
v_pending_rows := 'Y';

update request_process_queue set status='INP',
instance_name = i_host_name,
host_name = i_instance_name,
update_dttm = v_update_dttm
where child_process_id = o_child_process_id
and platform_request_guid= o_platform_req_guid;

update request_process set status='INP',
process_start_dttm = v_update_dttm,
update_dttm = v_update_dttm
where child_process_id = o_child_process_id
and platform_request_guid= o_platform_req_guid;

update requests set status='AIN',
update_dttm = v_update_dttm
where platform_request_guid= o_platform_req_guid
and req_status ='ARD';
commit;
exit;

exception when no_data_found then
commit; null;
end;
end loop;
If v_pending_rows = 'Y' then
open o_request_info for
select
columns
...
from requests r,
request_key_val_set k
where platform_request_guid= o_platform_req_guid(+) AND
r.platform_request_guid= o_platform_req_guid;

open o_document_info for
select
columns
...
from request_documents r,
document_key_val_set k
where d.document_id = k.document_id (+) AND
d.child_process_id = o_child_process_id AND
d.status = 'ARD' AND
d.platform_request_guid= o_platform_req_guid;

end if;
o_error_code := '0';
o_error_msg := null;
event_pkg.log_errors('IFN',o_error_code, o_error_msg,'GETNEXTREQUEST', o_platform_req_guid, o_child_process_id );
exception
when e_missing_parameters then
event_pkg.log_errors('ECN',o_error_code, o_error_msg,'GETNEXTREQUEST', o_platform_req_guid, o_child_process_id );
when others then
o_error_code := sqlcode; o_error_msg := sqlerrm;
rollback;
event_pkg.log_errors('ECN',o_error_code, o_error_msg, 'GETNEXTREQUEST', o_platform_req_guid, o_child_process_id );
end getnextrequest;

Table DDL:
1.REQUESTS
platform_request_guid varchar2(32), req_status varchar2(3), request_priority number(1),update_dttm TIMESTAMP, ...
2.request_documents
platform_request_guid varchar2(32), document_id NUMBER, child_process_id NUMBER, status varchar2(3), update_dttm TIMESTAMP..
3.request_process
platform_request_guid varchar2(32), child_process_id NUMBER, status varchar2(3),process_start_dttm TIMESTAMP, update_dttm TIMESTAMP..
4.request_process_queuue
platform_request_guid varchar2(32), child_process_id NUMBER, status varchar2(3), host_name varchar2(256), instance_name varchar2(256), update_dttm TIMESTAMP,..
5.request_ke_val_set
platform_request_guid varchar2(32), req_key_name varchar2(512), req_key_value1 varchar2(512)...
6.document_key_val_set
document_id number, ....

From the log table, i could see the message : Ora-06502: PL/SQL: numeric or value error
And the error occurs at : select * from request_process_queue FOR UPDATE SKIP LOCKED

Thank you for you help in advance.

and Connor said...

Are you confident you have your data type precisions defined correctly in .Net. If the incoming bind variable is too small, you can get this, for example:

SQL> create or replace
  2  procedure P(x out varchar2) is
  3  begin
  4    x := rpad('x',32);
  5  end;
  6  /

Procedure created.

SQL>
SQL> variable v varchar2(32);
SQL> exec p(:v);

PL/SQL procedure successfully completed.

SQL> create or replace
  2  procedure P(x out varchar2) is
  3  begin
  4    x := rpad('x',64);
  5  end;
  6  /

Procedure created.

SQL>
SQL> variable v varchar2(32);
SQL> exec p(:v);
BEGIN p(:v); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "MCDONAC.P", line 3
ORA-06512: at line 1



Rating

  (7 ratings)

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

Comments

Data type mismatch

Balu, September 02, 2016 - 2:35 am UTC

Thank You Tom.

You are correct.

.Net team has confirmed that the data type used in their code was String, but it was Number data type in Oracle SP. Hence it was giving "Ora-06502: PL/SQL: numeric or value error ".

After seeing your post, i have asked .Net team to change the data type to Decimal in their code and the error is not happening now.

But still i'm not clear why the error is happening inside SP rather in .Net code ie,. the error was captured by When other exception block. Could you please explain in this.

I appreciate your help.
Connor McDonald
September 04, 2016 - 1:16 pm UTC

Because it is a *binding* issue.

For example, if the calling environment says "give me back a number", and the PLSQL routine fetches a varchar2 from the database, it will try to return a number. When that conversion fails, we are still within the PLSQL.

ORA-06502 selecting v$sysstat.value into local variable

Bakunian, November 12, 2019 - 7:41 pm UTC

Any idea why these simple code would produce this error?

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 6

DECLARE
l_before v$sysstat.value%type;
l_after v$sysstat.value%type;
BEGIN
SELECT value into l_before FROM v$sysstat where name = 'user calls';
dbms_output.put_line('Before value: ' + l_before);
dbms_lock.sleep( 5 );
select value into l_after from v$sysstat where name = 'user calls';
dbms_output.put_line('After value: ' + l_after);
dbms_output.put_line('User calls during this interval: ' + l_after - l_before);
END;
Connor McDonald
November 12, 2019 - 11:17 pm UTC

You're confusing PLSQL with Java :-)

SQL> exec dbms_output.put_line('String ' + 123 );
BEGIN dbms_output.put_line('String ' + 123 ); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1


SQL> exec dbms_output.put_line('String '|| 123 );
String 123

PL/SQL procedure successfully completed.


on data length mismatch

Rajeshwaran Jeyabal, November 13, 2019 - 2:58 pm UTC

Team,

was running up your above demo at my local database, and the output length is getting truncated no error due to data length mismatch - kindly advice.

demo@PDB1> create or replace procedure p ( p_out out varchar2 )
  2  as
  3  begin
  4     p_out := rpad('x',64,'x');
  5  end;
  6  /

Procedure created.

demo@PDB1> variable x varchar2(30)
demo@PDB1> exec p(:x);

PL/SQL procedure successfully completed.

demo@PDB1> select length(:x) from dual;

LENGTH(:X)
----------
        30

demo@PDB1>

Much probably an enhancement in recent versions...

J. Laurindo Chiappa, November 13, 2019 - 9:59 pm UTC

Hello, Raj : this surely can be some version-based difference : for example, see sqlplus version 10.2.0.5 connecting in 10.2.0.5 database, the error arises :

scott@DESENV10gR2:SQL>create or replace procedure p ( p_out out varchar2 )
2 as
3 begin
4 p_out := rpad('x',64,'*');
5 end;
6 /

Procedimento criado.

scott@DESENV10gR2:SQL> variable x varchar2(30);
scott@DESENV10gR2:SQL>exec p(:x);
BEGIN p(:x); END;

*
ERRO na linha 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.P", line 4
ORA-06512: at line 1


scott@DESENV10gR2:SQL>

==> but in 12cR2 sqlplus connecting in 12.2.0.2 database :

Conectado a:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SYSTEM:@desenv12cr2:SQL>create or replace procedure p ( p_out out varchar2 )
2 as
3 begin
4 p_out := rpad('x',64,'*');
5 end;
6 /

Procedimento criado.

SYSTEM:@desenv12cr2:SQL> variable x varchar2(30);
SYSTEM:@desenv12cr2:SQL>exec p(:x);

Procedimento PL/SQL concluĂ­do com sucesso.

SYSTEM:@desenv12cr2:SQL>print x

X
-------------------------------------------------------------------------------------------------------------
x*****************************

SYSTEM:@desenv12cr2:SQL>select length(:x) from dual;

LENGTH(:X)
----------
30

SYSTEM:@desenv12cr2:SQL>

==> See , in sqlplus 12cR2 the data was silently 'truncated' to X's length....

Question regarding the message

Monica Tasso, September 07, 2021 - 5:02 pm UTC

I have been receiving this message:
ociexecute(): ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at line 1

trying to access a specific website which I could access before. It is a trusted website and no one I know has shown this message.

Could you please tell me how to correct it?
Thank you
Monica


Connor McDonald
September 08, 2021 - 6:16 am UTC

Not sure how this relates to this topic...

But you're getting some characters which are not valid hex...which means they can't be converted to raw

PL/SQL error ORA-6502 after upgrading to 19c

George Varghese, September 26, 2021 - 2:33 am UTC

Getting this error from a stored procedure after upgrading the database from 12c to 19c.

The same code base is working in 12c.

ORA-6502 PL/SQL : numeric or value error: character string buffer too small

Is there any value conversions, happening differently in 19c ?
Connor McDonald
September 27, 2021 - 11:08 am UTC

Is it possible you have changed characterset?

12c to 19c Migration

Leonard, October 15, 2021 - 5:23 pm UTC

We have seen issues in 19c with Group By clause. If the names of the fields in the group by do not match the name of the fields in the select clause (except for the aggregate function), you will get ORA-06502. 12c seemed to tolerate this but not 19c.
Connor McDonald
October 21, 2021 - 1:37 am UTC

Not sure I understand what you're saying - can you add a test case?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library