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: