Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Exclamation Unanswered: problems using 'IN' clause

    I'm writing a procedure, and it has a cursor. This cursor receive some parameters and one of them is a list of numbers, the list can't be a VARCHAR2 with ',' or ';' as separator, because it doesn't work, if I use a defined type like 'vet IS TABLE OF NUMBER INDEX BY BINARY_INTEGER' it doesn't work either. The cursor is necessary because it return many output. Here is my code:

    PROCEDURE pr_busca_equipamento (
    id_pos IN NUMBER,
    tipo_posicao IN VARCHAR2,
    empresa IN NUMBER,
    nro_serie IN VARCHAR2,
    id_item IN NUMBER,
    status IN NUMBER,
    id_equipamento OUT vetornum, -- table of number...
    desc_equip OUT vetorchar, -- table of varchar2..
    caminho_completo OUT vetorchar,
    nro_serie_out OUT vetorchar,
    status_out OUT vetornum,
    quantidade OUT NUMBER,
    erro_cod OUT VARCHAR2,
    erro_msg OUT VARCHAR2
    )
    IS
    CURSOR c_busca_equip(
    id_p NUMBER,
    tipo_p VARCHAR2,
    emp NUMBER,
    nro VARCHAR2,
    id_i NUMBER,
    stat NUMBER)
    IS
    SELECT eq.id_equipamento,
    nroserie,
    id_status,
    id_item
    FROM EQUIPAMENTOS EQ,
    EQUIPAMENTOS_POSICAO EP
    WHERE (eq.id_equipamento = ep.id_equipamento AND ep.id_posicao = id_p)
    AND (id_i IS NULL OR id_item = id_i)
    AND (stat IS NULL OR id_status = stat)
    AND (nro IS NULL OR nroserie IN (nro)) -- my problem is nro
    AND (emp IS NULL OR id_empresa_responsavel = emp);



    What do I have to do? I need to use a list...
    PS: I'm using Oracle 9
    Last edited by soaresinfo; 12-06-04 at 11:18.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The "quick & dirty" way is to use a string parameter and then use INSTR:

    AND (nro IS NULL OR INSTR(','||nro||',', ','||nronroserie||',') > 0

    (Note: any index on nro will not be usable here).

    Otherwise, you need to try something more fancy!

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    So does that mean that you have to use a table of parameter values in order to avoid dynamic SQL where you're facing a situation when you need to use an IN statement?

    insert into parm_values ('1');
    insert into parm_values ('2');
    insert into parm_values ('3');
    select * from table where code in (select parm_value from parm_values);
    delete from parm_values;


    -Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •