What Causes the Numeric Overflow in this PL/SQL Function?

Question

I can run this command on Oracle 10.2 without problem:

SQL> select instr(unistr('foo'), chr(4050596145)) as hazit from dual;

     HAZIT
----------
     0

So I tried to encapsulate it into a function:

CREATE OR REPLACE FUNCTION hazit(string IN VARCHAR2) RETURN INTEGER
AS
BEGIN
    RETURN instr(unistr(string), chr(4050596145));
END;
/

Function created.

But I get a numeric overflow error when I try to use it:

SQL> select hazit('foo') FROM DUAL;
select hazit('foo') FROM DUAL
       *
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "DWHEELER.HAZIT", line 4

What gives?

A guess , chr(4050596145) won't fit in a signed integer in whatever the charset of your database is. Widen the return type of the function. — Tony Hopkinson
– Tony Hopkinson, Commented Oct 10, 2012 at 22:27
I somehow feel that this is happening due to chr(4050596145) because if you replace it with a random string 'abcdefghijkl' it works just fine. — Anjan Biswas
– Anjan Biswas, Commented Oct 10, 2012 at 23:24
@A.B.Cade Your solution works for me, too. Care to put it into an answer that I can then accept? — theory
– theory, Commented Oct 11, 2012 at 0:05

A.B.Cade · Accepted Answer · 2012-10-11 11:18:03Z

2

I don't have an explanation but this seems to work:

CREATE OR REPLACE FUNCTION hazit(string IN VARCHAR2) RETURN NUMBER IS
i number;
BEGIN
    select instr(unistr(string), chr(4050596145))
      into i from dual;
    return i;
END;
/

Here is a fiddle

answered Oct 11, 2012 at 11:18

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

theory Over a year ago

Thanks @A.B.Cade. I actually pasted in the literal character instead of using chr() to get around the problem. It's a bizarre one, though, I gotta say.

A.B.Cade Over a year ago

@theory, I found something about a different function (rawtohex) which behaves differently in sql and plsql (see here techonthenet.com/oracle/functions/rawtohex.php) perhaps the same happens to chr() function as it also makes implict conversions (stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/…)

theory Over a year ago

Dude, that's just whack! Oracle continues to annoy…

Collectives™ on Stack Overflow

What Causes the Numeric Overflow in this PL/SQL Function?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related