CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlmybatis
simpy
simpy

Reputation: 21

Issue with the procedure having execute immediate

I need to query a table like select count(id) from user where id in ('208018042','208001105') ,i have written a function to do this which accepts semicolon separated data but i am getting invalid number as a exception ,i guess i am not sending the values in quote ,what is wrong here.I need to pass the values like '208018042'208001105' the above single query gets executed but some issue with query in the below function:

FUNCTION p_get_count_id(p_invalue IN VARCHAR2) RETURN VARCHAR2 IS
          v_splitTable      split_tbl;
          v_OptionValueList varchar2(4000);
          v_in_optid_list   varchar2(4000);
          v_count         number;
  begin
    v_in_optid_list := p_in_field_value;
    if (v_in_optid_list is not null) then
      v_in_optid_list := REPLACE(p_in_field_value, ';', ',');

 EXECUTE IMMEDIATE 'select count(id) 
      from user
     where id in
           (' || v_in_optid_list || ')' bulk collect
        into v_splitTable;
      v_OptionValueList := joinstr(v_splitTable, '; ');
    end if;
    return v_OptionValueList;
 End p_get_count_id;

the execute immediate is causing a issue.

Upvotes: 0

Views: 643

Answers (1)

Alexander Tokarev
Alexander Tokarev

Reputation: 1035

It will not work. Use either collection or pipelined function or temporary table to pass into the function the list of possible values

Upvotes: 1

Related Questions