ORA-00984: column not allowed here

oracleoracle-10g

I am running Oracle 10g. I am writing a script (a delta script over a created database) that will be executed in SQL*Plus. It is needed to fix a bug.

I need to know if a row exist in a table, and if it does not, insert it. So I start like:

BEGIN
    DECLARE
        COL_EXIST NUMBER(10);
    BEGIN
        -- Reset ET_SEVERITY_LEVEL
        UPDATE ET_SEVERITY_LEVEL 
        SET ID_SEVERITY_LEVEL = 5
        WHERE CODE='FATAL';

        UPDATE ET_SEVERITY_LEVEL 
        SET ID_SEVERITY_LEVEL = 4
        WHERE CODE='ERROR';

        UPDATE ET_SEVERITY_LEVEL 
        SET ID_SEVERITY_LEVEL = 3
        WHERE CODE='WARNING';

        UPDATE ET_SEVERITY_LEVEL 
        SET ID_SEVERITY_LEVEL = 2
        WHERE CODE='INFO';

        -- Insert ET_SEVERITY_LEVEL register DEBUG
        SELECT COUNT(*) INTO COL_EXIST
        FROM ET_SEVERITY_LEVEL
        WHERE CODE like 'DEBUG';

        IF COL_EXIST = 0 THEN
            EXECUTE IMMEDIATE 'INSERT INTO ET_SEVERITY_LEVEL(ID_SEVERITY_LEVEL, CODE, DESCRIPTION) VALUES (1, "DEBUG", "Debugging")';   
        END IF;
    END;
END;
/

But I get the following error:

ORA-00984: column not allowed here
ORA-06512: in line 30
00984. 00000 -  "column not allowed here"
*Cause:    
*Action:

How could I do this? Is there a way to write a script to query tables and DML at the same time?

Best Answer

You need to use escaped single quotes in your EXECUTE IMMEDIATE statement, like so:

EXECUTE IMMEDIATE 'INSERT INTO ET_SEVERITY_LEVEL(ID_SEVERITY_LEVEL, CODE, DESCRIPTION) VALUES (1, ''DEBUG'', ''Debugging'')';