How to convert sql query with in clauses into string-CodePudding

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;

CodePudding user response：

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>

CodePudding user response：

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;
/