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.

select INSTR(str, ' ') into pos from dual; can be simplified to pos := INSTR(str, ' '); — user330315
– user330315, Commented Mar 21, 2017 at 11:26
If performance is a consideration, you might consider coding 'SELECT NVL(NULLIF(INSTR(str,' '),0),LENGTH(str)) FROM ...' instead of using a PL/SQL function. Or else, if you are using Oracle 12c, consider adding PRAGMA UDF; to your function. — Matthew McPeak
– Matthew McPeak, Commented Mar 21, 2017 at 12:01
Out of curiosity, why use this function? Are you using it to substring a value getting the alphanumeric values up to the first space, using this function as the length element of SUBSTR? If so, there are ways to do this with Oracle functions or regular expressions that would be better than this custom function. — unleashed
– unleashed, Commented Mar 21, 2017 at 15:23
Hi, thanks everyone! Well, I'm not fluent with regex syntax in Oracle (which is completely different than what I used in the past), so I went with this "quick fix"; Performance is not an issue as I use it in a night job that executes in 50 seconds (materialized view). — Raphaël L.
– Raphaël L., Commented Apr 3, 2017 at 15:21

Paul · Accepted Answer · 2017-03-21 11:42:35Z

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.

edited Mar 21, 2017 at 11:42

answered Mar 21, 2017 at 11:24

Paul

1,16714 silver badges24 bronze badges

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

Comments

Vecchiasignora · Accepted Answer · 2017-03-21 12:06:33Z

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

answered Mar 21, 2017 at 12:06

Vecchiasignora

1,3157 silver badges6 bronze badges

Collectives™ on Stack Overflow

Custom SQL function - PL/SQL: Statement ignored

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related