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 ?
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
Cto be returned as that's what I passed to
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>