Operator in (parameter)

[katiacd]

Dear good afternoon!

I am developing a package, from where it will be called a certain screen.
where for example: is selected by choice in a Chekbooks, 1,2,3 or only 1 or all.
I remember that I have already developed in the past by SQL dynamic, where it used the IN (|| '' parameter '' ||). Can someone tell me, if currently
have a more dynamic way to do this without being by dimamic SQL?
Examples:

Select all

Select * 
from produtos 
where cdg_prodto IN (||'p_parametro'||);

which would be optional, selected (1,2,3). When step parameter, it returns me only the query of 1 and not of the 3 selectionDOD items.

Thanks.

Katia Cristine

[spernega]

Good afternoon Katia,

If there will be a maximum of three parameters, it may be easier to create parameters in the procedure.

more or less like this:

Select all

Procedure Sp_Produto (v_par1 varchar2, 
                      v_par2 varchar2, 
                      v_par3 varchar2) is 
 -- 
 cursor c is 
  select dsp_prodto 
  from   produtos 
  where  cdg_prodto in(v_par1, v_par2, v_par3); 
 c_r c%rowtype; 
begin 
 open c; 
  loop 
   fetch c into c_r; 
    exit when c%notfound; 
    dbms_output.put_line(c_r.dsp_prodto); 
  end loop; 
 close c; 
end; 
/

[katiacd]

Thank you spernega ...


helped me a lot ...

[geovani]

Good morning Katia,

You can treat the field and parameter with commas, as follows

Select all

Select * 
from produtos 
where '%,'||'p_parametro'||',%' like '%,'||cdg_prodto||',%'

Example:
If the parameter '1,2,3' will be: ',1,2,3,'
If the CDG_Producto field is '2' '2' shall be ',2,'

] It will be like this:

Select all

where  %',1,2,3,'%  like %',2,'%

I hope I have helped!
Embrace