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 ? stackoverflow.com/questions/7816402/… — UltraCommit
– UltraCommit, Commented Jul 25, 2014 at 9:10
See also this solution: stackoverflow.com/questions/7634601/… — UltraCommit
– UltraCommit, Commented Jul 25, 2014 at 9:13
Very likely your instr-variable is not correctly quoted when you construct string for execute immediate. — user272735
– user272735, Commented Jul 25, 2014 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
– FrustratedWithFormsDesigner, Commented Jul 25, 2014 at 15:33

Sebas · Accepted Answer · 2014-07-25 19:05:04Z

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.

sampathsris · Accepted Answer · 2014-08-25 23:49:37Z

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

Collectives™ on Stack Overflow

string variable in where clause with dynamic pl/sql

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related