SQL & PL/SQL

Hi,

I have a procedure that first creates a sequence and then alters the table column's default value to sequence.nextval. Due to the program flow, I cannot use IDENTITY columns.

When I "EXECUTE IMMEDIATE" the dynamic SQL which alters the table, I get the following error:

[ORA-02262: ORA-1031 occurs while type-checking column default value expression]

But when I run the SQL generated in SQL Developer, it runs okay. I tried the following but didn't work

1. changing the column's type to NUMBER(*,0)
2. I enclosed the EXECUTE IMMEDIATE with a BEGIN END block

I don't think there's anything wrong with the SQL generated as it runs okay separately.

Table DDL:

 CREATE TABLE "SPVIReg_CisTER"."W$_VIReg_gisTER" 
   ( "IV_PK_Reg_Col_PK0001" NUMBER, 
  "IV_PK_Reg_Col_PK0002" NUMBER, 
  "IV_PK_Reg_Col_0003" VARCHAR2(4000 BYTE), 
  "IV_PK_Reg_Col_0004" VARCHAR2(4000 BYTE), 
  "E$STATUS" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
  "E$REASON" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
  "E$COMMENT" VARCHAR2(4000 BYTE), 
  "E$LAST_MODIFIED" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
  "E$DELETED" VARCHAR2(1 BYTE), 
  "E$ROW_ID" NUMBER(22,0) NOT NULL ENABLE, 
  "E$SPSC" NUMBER(10,0) DEFAULT 0, 
  CONSTRAINT "PW$_VIReg_gisTER" PRIMARY KEY ("IV_PK_Reg_Col_PK0001", "IV_PK_Reg_Col_PK0002")
   );

The dynamically generated alter statement (which runs fine separately)

ALTER TABLE "SPVIReg_CisTER"."W$_VIReg_gisTER" MODIFY ("IV_PK_Reg_Col_PK0001" DEFAULT "SPVIReg_CisTER"."SQ_PK_Reg_Col_PK0001".NEXTVAL);