oracle function syntax error

Question

I have the following oracle function , where it has num_list as input of comma separated list , and then check if the numbers in this list exists in certain table :

create or replace FUNCTION get_num_exist (num_list varchar2,separator varchar2) 
    RETURN nbs PIPELINED 
as
    row_type nb;
begin

    with numbers as 
    (select regexp_substr(num_list,'[^'||separator||']+', 1, level)num_  
     from dual
     connect by regexp_substr(num_list, '[^'||separator||']+', 1, level) is not null)


    for r_row in (select * from numbers
                  where num_  in (select phone_number 
                                   from phone_numbers) )
    loop
        PIPE ROW(nb(r_row.num_));
    end loop;
    return;

end;

but it's giving a syntax error at the level of for loop.

Any help would be appreciated.

You have split your CTE and the selection from the CTE with the for loop, that is not going to work. The with clause would have to be with the select - although the real purpose of this function is not clear. It might help if you can give more details of what you are trying to achieve, — Andrew
– Andrew, Commented Mar 13, 2018 at 14:44
Your with clause belongs to the cursor, presumably? If so, it should be part of the cursor for loop, i.e. for r_row in (with numbers as (...) select * from numbers ... — Boneist
– Boneist, Commented Mar 13, 2018 at 14:46
@Andrew I want to return the numbers from the list that exist in phone_numbers table. — user123
– user123, Commented Mar 13, 2018 at 14:46

Gerard H. Pille · Accepted Answer · 2018-03-13 15:02:16Z

6

You seem to be mixing SQL and PL/SQL.

for r_row in (
    with numbers as 
      (select regexp_substr(num_list,'[^'||separator||']+', 1, level) num_
         from dual
       connect by regexp_substr(num_list, '[^'||separator||']+', 1, level)
         is not null )
    select * from numbers where num_ in (
       select phone_number from phone_numbers) )
loop
    PIPE ROW(nb(r_row.num_));
end loop;

But I used to do it in another way (I thought faster, but that will not be so if there is an index on phone_numbers.phone_number).

for r_row in (
  select phone_number num_ from phone_numbers
    where instr(separator || num_list || separator,
                separator || phone_number || separator) > 0
) loop
    PIPE ROW(nb(r_row.num_));
end loop;

A word of explanation perhaps. I'm putting the separator before and after the search list, and before and after the database column (to avoid false positives). With the instr > 0, I simulate Oracle's (or any DB's) " where phone_number IN ( ..., ..., ... )".

edited Mar 13, 2018 at 15:02

answered Mar 13, 2018 at 14:46

Gerard H. Pille

2,5981 gold badge16 silver badges17 bronze badges

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

2 Comments

user123 Over a year ago

thank you for your suggestion. Do you mean that if there is index on phone_number field , your approach will be slow , and then the classical "where num in (select ,,,) " will be faster ?

Gerard H. Pille Over a year ago

Yes, my construct does not allow an index to be used. Use it when you want to avoid using an index (because it's not selective) or when there isn't one that can't be used. In your case, phone_number is probably the PK of phone_numbers, and your construction will be a thousand times faster. Neat trick splitting the list using a hierarchical query. Did you find it on Tom Kyte?

Collectives™ on Stack Overflow

oracle function syntax error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related