CODEWITHSUNDEEP
oracle-databaseplsql
Victoria
Victoria

Reputation: 187

Invalid datatype error when creating primary key on the existing table

The block below returns the error ORA-00902: invalid datatype. Do you have any idea where could be the problem? Thanks in advance :)

DECLARE
      lv_txt VARCHAR2(32000);
BEGIN
      lv_txt := 'ALTER TABLE INF_AUSBAUPLAENE ADD CONSTRAINT PRIMARY KEY(AUSB_ID)';
      EXECUTE IMMEDIATE lv_txt;
      DBMS_OUTPUT.PUT_LINE('PK Constraint successfuly created.');
EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -02275 THEN
        -- ORA-02275: such a referential constraint already exists in the table
          DBMS_OUTPUT.PUT_LINE('PK AUSB_ID is already used on table "inf_ausbauplaene"');
        ELSE
        migra_log_mechanism.saveline(code_in         => SQLCODE,
                                   text_in         => SQLERRM,
                                   error_line_text => 'Unexpected error occured while executing 
    script');

          RAISE;
        END IF;
END;
/

Upvotes: 1

Views: 670

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

You're missing the primary key constraint name:

SQL> create table inf_ausbauplaene (ausb_id number);

Table created.

SQL>
SQL> DECLARE
  2        lv_txt VARCHAR2(32000);
  3  BEGIN
  4        lv_txt := 'ALTER TABLE INF_AUSBAUPLAENE ADD CONSTRAINT pk_iabp PRIMARY KEY(AUSB_ID)';
  5        EXECUTE IMMEDIATE lv_txt;                           -- ^^^^^^^
  6  END;                                                      -- this!
  7  /

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Related Questions