更改表，添加列/ORA-00984:此处不允许使用列PLSQL [英] Alter table, add column / ORA-00984: column not allowed here PLSQL

问题描述

下一条语句SQL给我一个"ORA-00984:此处不允许使用列":

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);

它进入了PL-SQL，就像这样:

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;

我希望它能够捕获PL-SQL中发生的所有异常，以便在出现任何错误时可以在检查点START进行回滚.

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

推荐答案

您需要对格式掩码使用单引号:

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);

在执行中，这将是:

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

请注意，您正在执行DDL查询，因此您将无法回滚所做的修改.回滚仅影响数据，不影响结构.

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.

此外，为什么还要将日期存储在varchar列中?这是一个坏主意，date列

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

这篇关于更改表，添加列/ORA-00984:此处不允许使用列PLSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！