Custom SQL function - PL/SQL: Statement ignored

Question

Hi made a function that modifies a little bit the INSTR function (Oracle SQL): instead of getting '0' when a space char isn't found, I get the length of the string.

It compiles but when I try to execute it, i'm getting the ORA-06550 error (which is, if i'm not wrong, a compilation error).

I tried this function as a procedure and it runs all right... so I don't understand why it doesn't want to execute.

Can you help me?

CREATE OR REPLACE FUNCTION "INSTR2" (str varchar2) RETURN NUMBER AS
pos number(4,0) := 0;
BEGIN
  select INSTR(str, ' ') into pos from dual;
  if (pos = 0) then
    select to_number(length(str)) into pos from dual;
    return pos; 
  else
    return pos;  
  end if;
END INSTR2;

Thanks a lot,

R.L.

Answer 1

Well, there is already a built-in function called INSTR2 in Oracle (look, here it is).

I've just compiled your function with another name and it worked. So the code is valid, you just have to pick the name for it.

Apparently Oracle resolves INSTR2 to the built-in function despite the fact you now have the function with such name in your own schema (I couldn't find the reason for it in docs, but this behaviour is not exactly surprising). And INSTR2 expects more parameters than your implementation. That causes a runtime error.

Answer 2

1. Change you function name for excample to INSTR_SPACE and everething there will be ok, because in oracle there is a instr2 function and you will have problem during calling
2. Change your function body to

create or replace

function INSTR_SPACE(str varchar2) return number as
       pos number(4, 0) := 0;
    begin
       pos := INSTR(str, ' ');
       if (pos = 0) then
          return to_number(length(str));
       else
          return pos;
       end if;
    end INSTR_SPACE;

you dont need sql operations, you can do it by simple pl/sql operations and it will be more faster