Multiline bind parameter

I'm trying the following query from DBeaver (backend is oracle):

SELECT  * FROM mytable where mycolumn in (REPLACE( :req_id_list, CHR(13), ','))

when it prompts for the value of req_id_list I want to paste the values from Excel, which will be one value per line. the query is failing with ORA-00907: missing right parenthesis. is there a way to convert the multiline value to a CSV one ?

1 answer

  • answered 2022-05-04 10:50 Littlefoot

    I don't use DBeaver so this is a SQL*Plus example. Basically, you'll have to "split" that multi-line input value into separate rows (that's what subquery in lines #8 - 10 does):

    Declaring a bind variable:

    SQL> var req_id_list varchar2(20)
    

    Storing A + carriage return + C into it:

    SQL> exec :req_id_list := 'A' || chr(13) || 'C';
    
    PL/SQL procedure successfully completed.
    

    Query itself (lines #1 - 5 represent sample data; I expect A and C to be returned as that's what I passed to req_id_list):

    SQL> with mytable (mycolumn) as
      2    (select 'A' from dual union all
      3     select 'B' from dual union all
      4     select 'C' from dual
      5    )
      6  select *
      7  from mytable
      8  where mycolumn in (select regexp_substr(replace(:req_id_list, chr(13), '#'), '[^#]+', 1, level)
      9                     from dual
     10                     connect by level <= regexp_count(:req_id_list, chr(13)) + 1
     11                    );
    
    M
    -
    A
    C
    
    SQL>
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum