string variable in where clause with dynamic pl/sql

Question

I have this code:

declare
  instr varchar2(20);
  num_no number;

begin 
    select AR_ID, ID
    into instr, num_no
    from quelle_match
    where ID = 1;

      execute immediate 'CREATE or replace VIEW interm2 AS
      SELECT AR_ID, kurs,
        ((TO_CHAR(to_date(di.kursdatum,''dd.mm.yyyy''),''YYYYMMDD''))+    (TO_CHAR(to_date(di.verfall,''dd.mm.yyyy''),''YYYYMMDD''))) AS dumdate
      FROM daten_import di 
      where di.AR_ID = '||instr||'';      

  -- where di.AR_ID = ''GXIndex''';

end ;

I get error b/c instr is not valid. When I substitute the variable with the actual string GXIndex, the code works. It also works with numbers as the variable, but not with string. Why is that?

Have you tried with EXECUTE IMMEDIATE USING ? http://stackoverflow.com/questions/7816402/using-bind-variables-with-dynamic-select-into-clause-in-pl-sql — UltraCommit, Jul 25 '14 at 09:10
See also this solution: http://stackoverflow.com/questions/7634601/ora-00907-when-dynamically-creating-a-view-in-pl-sql-and-using-a-clob — UltraCommit, Jul 25 '14 at 09:13
Very likely your `instr`-variable is not correctly quoted when you construct string for `execute immediate`. — user272735, Jul 25 '14 at 11:27
Use a print statement to print out the value if `instr` before `execute immediate`. I think @user272735 is probably on the right track. — FrustratedWithFormsDesigner, Jul 25 '14 at 15:33

score 1 · Answer 1 · answered Jul 25 '14 at 19:05

this:

  execute immediate 'CREATE or replace VIEW interm2 AS
  SELECT AR_ID, kurs,
    ((TO_CHAR(to_date(di.kursdatum,''dd.mm.yyyy''),''YYYYMMDD''))+    (TO_CHAR(to_date(di.verfall,''dd.mm.yyyy''),''YYYYMMDD''))) AS dumdate
  FROM daten_import di 
  where di.AR_ID = '||instr||'';

should be:

  execute immediate 'CREATE or replace VIEW interm2 AS
  SELECT AR_ID, kurs,
    ((TO_CHAR(to_date(di.kursdatum,''dd.mm.yyyy''),''YYYYMMDD''))+    (TO_CHAR(to_date(di.verfall,''dd.mm.yyyy''),''YYYYMMDD''))) AS dumdate
  FROM daten_import di 
  where di.AR_ID = '||instr;

if instr is an integer, which I think it is.

score 0 · Answer 2 · answered Aug 25 '14 at 23:49

You have a problem in the way you concatenate instr. For clarity, let me simplify your problem:

EXECUTE IMMEDIATE
  'CREATE OR REPLACE VIEW interm2 AS
   SELECT ar_id FROM daten_import di
   WHERE di.ar_id = ' || instr;

instr is a string. You need to have quotes around it.

EXECUTE IMMEDIATE
  'CREATE OR REPLACE VIEW interm2 AS
   SELECT ar_id FROM daten_import di
   WHERE di.ar_id = ''' || instr || '''';
--                  ^^               ^^
--                  ||               ||
--                  -- escaped quotes--

string variable in where clause with dynamic pl/sql

2 Answers2