Angel Angel - 2 months ago 11
SQL Question

ORA-00984: column not allowed here PLSQL

The next statement SQL give me a "ORA-00984: column not allowed here":

ALTER TABLE USUVCB.TVCB_RUT_SII ADD (Fecha_Inicio VARCHAR2(10 BYTE) DEFAULT TO_CHAR(SYSDATE, "YYYY-MM-DD") NOT NULL);


It's into a PL-SQL, like this:

SET SERVEROUTPUT ON

DECLARE
Fecha VARCHAR2(8) := TO_CHAR (SYSDATE, 'YYYYMMDD');
Tabla VARCHAR2(28) := 'USER.TABLE_' || Fecha;
BEGIN
SAVEPOINT START;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || Tabla || ' AS SELECT FIELD_1, FIELD_2, FIELD_3 FROM USER.TABLE';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('Error creating the table');
END;

BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE USER.TABLE ADD (FIELD_4 VARCHAR2(10 BYTE) DEFAULT TO_CHAR (SYSDATE, "YYYY-MM-DD") NOT NULL)';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('Error creating the field');
END;

BEGIN
...
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('...');
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Rollback');
ROLLBACK TO START;
END;


I would like it to catch all exceptions ocurred into PL-SQL to could rollback at check-point START in case of any error.

Answer

You need to use single quotes for the format mask:

ALTER TABLE USUVCB.TVCB_RUT_SII ADD (Fecha_Inicio VARCHAR2(10 BYTE) DEFAULT TO_CHAR(SYSDATE, 'YYYY-MM-DD') NOT NULL);

In an EXECUTE, this will be:

execute immediate 'ALTER TABLE USUVCB.TVCB_RUT_SII ADD (Fecha_Inicio VARCHAR2(10 BYTE) DEFAULT TO_CHAR(SYSDATE, ''YYYY-MM-DD'') NOT NULL)';

Notice that you are doing DDL queries, so you will not be able to rollback the modifications you made. A rollback only affects data, not the structure.

Besides, why do you store a date in a varchar column? it is a bad idea, it would be much better a date column

Comments