CODEWITHSUNDEEP
sqloracle-database
Nayrb
Nayrb

Reputation: 1043

Oracle Execute Immediate not escaping quote?

DECLARE
  v_column_exists number := 0;  
BEGIN
  Select count(*) into v_column_exists
    from user_tab_cols
    where upper(column_name) = 'MyCoolColumn'
      and upper(table_name) = 'MyCoolTable';

  if (v_column_exists = 0) then
      execute immediate 'alter table MyCoolTable add (MyCoolColumn varchar2(255))';
      execute immediate 'update MyCoolTable set MyCoolColumn = 'NULL'';
  end if;
end;
/

Unfortunately I get the oracle error

ORA-06550
PLS-00103 Encounter Symbol "NULL" when expecting .....

on the line:

execute immediate 'update MyCoolTable set MyCoolColumn = 'NULL'';

I know I am inserting the word "Null" and not a null value. I am adding this column to an existing table and need to make it Not Null and a primary key with two other primary key columns. If I remove the problem line the statement executes fine.

Am I not properly escaping the 'Null'?

Upvotes: 0

Views: 358

Answers (2)

Atif
Atif

Reputation: 2210

Alternative way to handle this:

execute immediate q'[update MyCoolTable set MyCoolColumn = 'NULL']';

Upvotes: 2

OldProgrammer
OldProgrammer

Reputation: 12159

execute immediate 'update MyCoolTable set MyCoolColumn = ''NULL''';

you need two quotes to escape the 1st ' , then triple quotes for the end, one for the escape, and the second for the string temination.

Upvotes: 1

Related Questions