Exception in regexp_instr using clob uncatchable?
The zero is illegal, should be a one, but the real problem is that the exception is not catchable. It bubbles to top and "caught it" is never printed. The d()'s just write to dbms output. The exception is catchable if i change the clob to a varchar2. The fact that the exception is uncatchable is a bug right? The exception is ora-01428 argument '0' is out of range.
declare
v int;
p_in clob := 'i think i can init like this';
begin
d('started');
v := regexp_instr(p_in, 'some_regexp', 0);
d('it worked');
exception when others then
d('caught it');
end;
ya know, just know reporting a bug to a company should feel like more of a gift to the company not a torturous experience to the user. 30 minutes i goofed around trying to create an account and enter an oracle bug, entering same stuff multiple 开发者_开发技巧times. forget it. what happened to kiss. how about anonymous bugs. cmon oracle.
In Oracle XE (10.2.0.1), the following fails with an unhandled exception at line 1
declare
v int;
p_in clob := 'i think i can init like this';
begin
v := regexp_instr(p_in, 'some_regexp', 0);
exception
when others then
dbms_output.put_line('caught');
end;
/
Because it is at line 1, it can't even enter the declare section, so it isn't getting caught be the exception handler..
declare
v int;
p_in clob := 'i think i can init like this'; begin
select regexp_instr(p_in, 'some_regexp', 0) into v from dual;
end;
/
errors at line 5 and
declare
v int;
p_in clob := 'i think i can init like this';
begin
select regexp_instr(p_in, 'some_regexp', 0) into v from dual;
exception
when others then
dbms_output.put_line('caught');
end;
/
catches the error.
========================================================================= PS. It looks like the issue is down to STANDARD.REGEXP_INSTR
select package_name, object_name, overload, argument_name,
position, sequence, data_type, in_out, pls_type
from all_arguments
where object_name = 'REGEXP_INSTR' order by overload, position
indicates two procedures, one for VARCHAR2 and one for CLOB. The CLOB one has a NUMBER/INTEGER as the POSITION parameter while the VARCHAR2 has a BINARY_INTEGER/PLS_INTEGER. I guess the PLS_INTEGER isn't big enough to hold the a value for a very large CLOB of gigabytes etcs.
精彩评论