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

1

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.

answered Jul 25, 2014 at 19:05

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

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

Comments

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

0

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

answered Aug 25, 2014 at 23:49

sampathsris

22.4k12 gold badges74 silver badges102 bronze badges

Collectives™ on Stack Overflow

string variable in where clause with dynamic pl/sql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related