CODEWITHSUNDEEP
oracle-databaseplsql
Marco Aurelio Fernandez Reyes
Marco Aurelio Fernandez Reyes

Reputation: 2421

ORA-00922: missing or invalid option when trying to create column in table

I'm using the following code for create a column in an existing table, but, I'm getting this error:

ORA-00922: missing or invalid option

I've tried get the desired result (create column in table "only if this column does not exists") without the EXECUTE IMMEDIATE instruction, but, PL/SQL doesn't allow use the ALTER TABLE [...] in an IF [] THEN structure.

Is there something I'm missing?

This is the db<>fiddle sample:

CREATE TABLE "TMP_TABLE_SAMPLE" 
   (  "ID_TABLE" NUMBER(9,0)
   ) ;

✓

SET SERVEROUTPUT ON;
CLEAR SCREEN;
DECLARE 
  V_COLUMN_EXISTS NUMBER := 0;
BEGIN
  SELECT COUNT(1) CONTEO 
  INTO V_COLUMN_EXISTS 
  FROM USER_TAB_COLS 
  WHERE UPPER(COLUMN_NAME) = 'PNT_NCODE' 
  AND UPPER(TABLE_NAME) = 'TMP_TABLE_SAMPLE';
  
  IF V_COLUMN_EXISTS = 0 THEN 
    EXECUTE IMMEDIATE 'ALTER TABLE TMP_TABLE_SAMPLE ADD PNT_NCODE NUMBER (9,0) ' || 
    ' COMMENT ON COLUMN TMP_TABLE_SAMPLE.PNT_NCODE IS ''Stores ID from TMP_TABLE_SAMPLE_2.''';
  ELSE 
    DBMS_OUTPUT.PUT_LINE('Column already exists');
  END IF;
  
END;

ORA-00922: missing or invalid option

Upvotes: 0

Views: 4343

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

The error you're getting is because you have set serveroutput on and clear screen in your script. db<>fiddle knows how to interpret SQL and PL/SQL. It doesn't support SQL*Plus commands.

If you remove those, the next error you'll get is that you have a single execute immediate statement that is trying to execute two separate statements. Creating the column and adding a comment on the column are separate operations so you need separate statements.

If I change your fiddle to this, it works the way you want

DECLARE 
  V_COLUMN_EXISTS NUMBER := 0;
BEGIN
  SELECT COUNT(1) CONTEO 
  INTO V_COLUMN_EXISTS 
  FROM USER_TAB_COLS 
  WHERE UPPER(COLUMN_NAME) = 'PNT_NCODE' 
  AND UPPER(TABLE_NAME) = 'TMP_TABLE_SAMPLE';
  
  IF V_COLUMN_EXISTS = 0 THEN 
    EXECUTE IMMEDIATE 'ALTER TABLE TMP_TABLE_SAMPLE ADD PNT_NCODE NUMBER (9,0) '; 
    EXECUTE IMMEDIATE 'COMMENT ON COLUMN TMP_TABLE_SAMPLE.PNT_NCODE IS ''Stores ID from TMP_TABLE_SAMPLE_2.''';
  ELSE 
    DBMS_OUTPUT.PUT_LINE('Column already exists');
  END IF;
END;
/

Upvotes: 1

Related Questions