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?

Solution

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

Comments

[Tony Hopkinson]

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.

[A.B.Cade]

strange, because this works: sqlfiddle.com/#!4/c163a/1

[AnBisw]

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.

[theory]

@A.B.Cade Your solution works for me, too. Care to put it into an answer that I can then accept?

[theory]

@Annjawn Yes, it works if I use chr(1234).

[theory]

@TonyHopkinson My function is returning the result of instr(), which is not going to be anything other than a regular integer.

[theory]

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]

@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]

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