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