4 Replies Latest reply on Aug 26, 2020 4:42 PM by Eslam_Elbyaly

    ORA-01442: column to be modified to NOT NULL is already NOT NULL when exporting

    Eslam_Elbyaly

      Hi, I am using Sql Developer  Version 4.1.3.20 Build MAIN-20.78.

      When I Export BP_TEST table through right click the table in the connections tab > tables> BP_TEST, I got the following,

       CREATE TABLE "BP_TEST" ("ID" NUMBER DEFAULT ON NULL "BUSINESS_PARTNER_PK"."NEXTVAL", "NAME" VARCHAR2(100), "TYPE" NUMBER) ;
      --  Constraints for Table BP
      --------------------------------------------------------
      
      
        ALTER TABLE "BP_TEST" MODIFY ("ID" NOT NULL ENABLE);
        ALTER TABLE "BP_TEST" ADD CONSTRAINT "BP_PK" PRIMARY KEY ("ID") USING INDEX  ENABLE;
        ALTER TABLE "BP_TEST" MODIFY ("TYPE" NOT NULL ENABLE);
        ALTER TABLE "BP_TEST" MODIFY ("NAME" NOT NULL ENABLE);
      

      When I try to run the above script in another schema, I got "ORA-01442: column to be modified to NOT NULL is already NOT NULL" error because of the first ALTER statement. The problem happens too when I use Tools menu> "Database Export" to export the whole schema. It happens with every table. Edited: and this is the main problem, exporting the whole schema's tables then running the script will cause many errors to appear, and I will have to go through everyone to see if they're all the same error or if there're other errors.

        • 1. Re: ORA-01442: column to be modified to NOT NULL is already NOT NULL when exporting
          EdStevens

          Eslam_Elbyaly wrote:

           

          Hi, I am using Sql Developer Version 4.1.3.20 Build MAIN-20.78.

          When I Export BP_TEST table through right click the table in the connections tab > tables> BP_TEST, I got the following,

          1. CREATETABLE"BP_TEST"("ID"NUMBERDEFAULTONNULL"BUSINESS_PARTNER_PK"."NEXTVAL","NAME"VARCHAR2(100),"TYPE"NUMBER);
          2. --ConstraintsforTableBP
          3. --------------------------------------------------------
          4. ALTERTABLE"BP_TEST"MODIFY("ID"NOTNULLENABLE);
          5. ALTERTABLE"BP_TEST"ADDCONSTRAINT"BP_PK"PRIMARYKEY("ID")USINGINDEXENABLE;
          6. ALTERTABLE"BP_TEST"MODIFY("TYPE"NOTNULLENABLE);
          7. ALTERTABLE"BP_TEST"MODIFY("NAME"NOTNULLENABLE);

          When I try to run the above script in another schema, I got "ORA-01442: column to be modified to NOT NULL is already NOT NULL" error because of the first ALTER statement. The problem happens too when I use Tools menu> "Database Export" to export the whole schema. It happens with every table.

          So what's the problem?  You are wanting the column to be NOT NULL, and it is NOT NULL.

          • 2. Re: ORA-01442: column to be modified to NOT NULL is already NOT NULL when exporting
            Eslam_Elbyaly

            The problem is when you run script to create many tables you see many errors and you'll have to go through everyone to see what it is. It could be the same error and it could be others.

            • 3. Re: ORA-01442: column to be modified to NOT NULL is already NOT NULL when exporting
              thatJeffSmith-Oracle

              Your copy of SQL Developer is very old, at least in terms of software years.

               

              How was your table originally defined in the database?

               

              As in, if I wanted to reproduce your issue, how would I go about making your BP_TEST table?

               

              In the meantime, go download version 20.2 of SQL Developer. It was released in 2020. Your version of SQL Developer was released in 2015

              • 4. Re: ORA-01442: column to be modified to NOT NULL is already NOT NULL when exporting
                Eslam_Elbyaly

                thatJeffSmith-Oracle wrote:

                How was your table originally defined in the database?

                As in, if I wanted to reproduce your issue, how would I go about making your BP_TEST table?

                Right click "Tables" node in the connections tab> New table> add id column as PK through checking PK column the set the "Default" column's value to "on null BUSINESS_PARTNER_PK.NEXTVAL" then add "name" column> Ok. Then click Tools menu> database export> choose to save to worksheet> check tables and constraints. After script has generated, issue a DROP statement to drop the table then run the script.

                I have  Version 19.1.0.094 Build 094.2042 and tried running the script with it too and I got the same error.