How to convert sql query with in clauses into string

Question

Hi I want to convert the sql statement into string for dynamic use. But I’m having trouble while concatenation

select col1, col2 from Table1 Where Col3 in ( ‘cat’ , ‘dog’ );

I’m unable to figure out how to put quotes and || for the cat and dog

‘select col1, col2 from Table1 Where Col3 in ( ‘||’‘cat’’ ||’, ‘’dog’ ‘||’)’ || Strcond;

Littlefoot · Accepted Answer · 2022-05-24 10:02:17Z

0

One option is to use the q-quoting mechanism (see line #6), as it lets you use single quotes normally (also, your single quotes look fancy; if you planned to copy/paste from e.g. MS Word into your SQL client, that won't work).

Here's an example:

SQL> declare
  2    l_str varchar2(500);
  3    strcond varchar2(100) := '  and 1 = 1';
  4  begin
  5    l_str := 'select col1, col2 from table1 where col3 in ' ||
  6             q'[('cat', 'dog')]' || strcond;
  7    dbms_output.put_line(l_str);
  8  end;
  9  /
select col1, col2 from table1 where col3 in ('cat', 'dog')  and 1 = 1

PL/SQL procedure successfully completed.

SQL>

answered May 24, 2022 at 10:02

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2022-05-24 10:37:52Z

One option it to use bind variables and put in as many bind variables as the maximum size of the IN list:

select col1, col2 from Table1 Where Col3 in ( :a001, :a002, :a003, :a004 )

Then:

you can use the same statement repeatedly and the SQL engine will not have to reparse it (after the first time).
you do not need to escape quotes in your list values in the dynamic SQL string.
your code is less vulnerable to SQL injection.

If you want to pass fewer values than the maximum then you can repeat values:

DECLARE
  sql VARCHAR2(2000) := 'select col1, col2 from Table1 Where Col3 in ( :a001, :a002, :a003, :a004 )';
BEGIN
  EXECUTE IMMEDIATE sql USING 'cat', 'dog', 'cat', 'cat';
END;
/

However, if you are going to do this then you can consider if you can eliminate dynamic SQL entirely:

DECLARE
  a001 Table1.Col3%TYPE := 'cat';
  a002 Table1.Col3%TYPE := 'dog';
  a003 Table1.Col3%TYPE := a001;
  a004 Table1.Col3%TYPE := a001;

  col1_values SYS.ODCIVARCHAR2LIST;
  col2_values SYS.ODCIVARCHAR2LIST;
BEGIN
  select col1, col2
  BULK COLLECT INTO col1_values, col2_values
  from   Table1
  Where Col3 in ( a001, a002, a003, a004 );
END;
/

Collectives™ on Stack Overflow

How to convert sql query with in clauses into string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related