varchar in execute immediate

Question

I'm using the following script pl/sql:

EXECUTE IMMEDIATE 'insert into TAB1(ID, CODE, TYPE, ORDRE) 
    select ''KEY'', ''KEY_LIB'', ''TYP_KEY'', 3 FROM dual
    where not exists(
                    select ID,CODE,TYPE,ORDRE
                    FROM TAB1
                    where TYPE=''TYP_KEY''
                    AND CODE =''KEY_LIB''
                    )';

And I'm getting the following error :

00000 - "FROM keyword not found where expected

The error seems to be in the second line but I can't figure out what's wrong. Can anyone help please ?

The generated code is fine and works for me (tip: display the dynamic string using dbms_output and test it on the command line). It appears the error is somewhere else in your code. — William Robertson, Dec 03 '18 at 11:37
This doesn't do what you think it does: `not exists (select id,code,type,ordre from ...`) - the exists operator doesn't care what you select. Also, you could omit the `select from dual` by selecting directly from `TAB1` (with an additional `rownum=1` predicate). — Jeffrey Kemp, Dec 04 '18 at 02:14

Mohammad Yasir Arafat · Answer 1 · 2018-12-03T11:53:02.290

0

Type is a keyword in Oracle. So, try with tablename.Type when type is a column name. Here is your revised query:

EXECUTE IMMEDIATE 'insert into TAB1(ID, CODE, TYPE, ORDRE) 
select ''KEY'', ''KEY_LIB'', ''TYP_KEY'', 3 FROM dual
where not exists(
                select ID,CODE,TYPE,ORDRE
                FROM TAB1
                where TAB1.TYPE=''TYP_KEY''
                AND CODE =''KEY_LIB''
                )';

edited Dec 03 '18 at 11:53

answered Dec 03 '18 at 11:45

Mohammad Yasir Arafat

655
4
13

Wernfried Domscheit · Answer 2 · 2018-12-03T13:18:58.587

0

You should better try it like this:

EXECUTE IMMEDIATE 'insert into TAB1(ID, CODE, "TYPE", ORDRE) 
select :KEY, :KEY_LIB, :TYP_KEY, 3 FROM dual
where not exists(
                select ID,CODE,"TYPE",ORDRE
                FROM TAB1
                where "TYPE" = :TYP_KEY
                AND CODE = :KEY_LIB
                )'
USING 'KEY', 'KEY_LIB', 'TYP_KEY', 'TYP_KEY', 'KEY_LIB';

edited Dec 03 '18 at 13:18

answered Dec 03 '18 at 13:14

Wernfried Domscheit

54,457
9
76
110

1

If you quote the `TYPE` in the column list you will need to quote it in the `WHERE` clause as well I guess – Dec 03 '18 at 13:16

score 0 · Answer 3 · answered Dec 03 '18 at 15:42

You can use Alternative Quoting Mechanism (''Q'') for String Literals, then you don't need double quotes. With this technique it is much easier to test queries manually (if it is needed). Btw, your query works fine.

BEGIN

  EXECUTE IMMEDIATE 
     q'[INSERT INTO tab1(id, code, type, ordre) 
        SELECT 'KEY', 'KEY_LIB', 'TYP_KEY', 3 
          FROM dual
         WHERE NOT EXISTS(
                  SELECT 1
                    FROM tab1
                   where tab1.type = 'TYP_KEY'
                     AND code      = 'KEY_LIB'
               )]';
END;
/

varchar in execute immediate

3 Answers3