I need to write PL SQL code for a query.

I receive a parameter (code) as a string which is a list of codes separated by semicolons (for example 'AAAA.4545;AAAA.9089;AAAA.6756'). I need to change it so I can use it in my query as "in (p_code)" which would be "IN ('AAAA.4545','AAAA.9089','AAAA.6756')"

I tried the following

p_code varchar2(50) := '''' || REPLACE(code,';',',''') || '''' ;

but it doesn't work and I am not sure if it is the code or a PL SQL instruction as I am pretty new to PL SQL (I come from a Microsoft world).

How can I make it work?

Thanks for your help

CodePudding user response：

Could this work for you?:

DECLARE

     v_old_code_list VARCHAR2(100); 
     v_new_code_list VARCHAR2(100); 

BEGIN

     v_old_code_list := 'AAAA.4545;AAAA.9089;AAAA.6756';
     v_new_code_list := '''' || REPLACE(v_old_code_list, ';', ''',''') || '''';

     dbms_output.put_line(v_old_code_list);
     dbms_output.put_line(v_new_code_list);
END;

1. concatenate a single code
2. replace semicolons with single quote/comma/single quote
3. concatenate a single code

Output as per the below

CodePudding user response：

If you split that string into rows (lines #3 - 5), you can use it in IN clause.

Sample table:

SQL> select * from test;

ID        NAME
--------- ------
AAAA.4545 Little
AAAA.9089 Foot

Query:

SQL> set ver off
SQL> select *
  2  from test
  3  where id in (select regexp_substr('&&par_code', '[^;] ', 1, level)
  4               from dual
  5               connect by level <= regexp_count('&&par_code', ';')   1
  6              );
Enter value for par_code: AAAA.4545;AAAA.9089;AAAA.6756

ID        NAME
--------- ------
AAAA.4545 Little
AAAA.9089 Foot

SQL>

That's a SQL*Plus example which uses a substitution variable. Depending on a tool you use, you might need to switch to a bind variable:

select *
from test
where id in (select regexp_substr(:par_code, '[^;] ', 1, level)
             from dual
             connect by level <= regexp_count(:par_code, ';')   1
            );

Or, if you use such a code in a procedure, remove the colon character.