CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11galter
Angel Doza
Angel Doza

Reputation: 1134

Alter table, add column / 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.

Upvotes: 1

Views: 2688

Answers (3)

ankit
ankit

Reputation: 2845

I got my solution:

i am using

ALTER TABLE PG_PGS_MST_LABEL MODIFY (LANG_CODE DEFAULT EN );

but

my solution is that Lang_CODE column is varchar2 and i have to use single codes in EN i.e,

ALTER TABLE PG_PGS_MST_LABEL  MODIFY (LANG_CODE DEFAULT 'EN' );

thanks.

Upvotes: 1

Brian Leach
Brian Leach

Reputation: 2101

Aleksej has a good solution. One often overlooked feature of Oracle is q quoting. By using this feature, you can use single quotes. Here is the same answer with q quoting:

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

Upvotes: 1

Aleksej
Aleksej

Reputation: 22969

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

Upvotes: 3

Related Questions