4 Replies Latest reply: Jul 10, 2011 10:14 AM by Udo RSS

    cannot convert from char to varchar2 anymore

    Alain
      11g xe linux:
      SQL> desc cec.softtypes;
      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      ID NOT NULL NUMBER(9)
      DESCRIPTION NOT NULL CHAR(30)
      PRODUCTION NOT NULL CHAR(1)
      COMMENTAIRE NOT NULL CHAR(254)

      SQL>alter table cec.softtypes modify(description varchar2(30) ,commentaire varchar2(254) );
      alter table cec.softtypes modify(description varchar2(30) ,commentaire varchar2(254) )
      *
      ERROR at line 1:
      ORA-01439: column to be modified must be empty to change datatype
      => works on 10.2.0.3.0 enterprise, works on 10g xe also.
        • 1. Re: cannot convert from char to varchar2 anymore
          Udo
          Alain,

          do you have exactly the same table definition in 10g? I guess your NOT NULL constraint on the column to be altered is in your way here.
          You could try to workaround the problem by adding a column of desired type, copy the values using an update and then drop the old column.

          -Udo
          • 2. Re: cannot convert from char to varchar2 anymore
            Alain
            I have the same table definition in 10g, and THIS WORKS in oracle 10 (xe and enterprise edition).

            When you replace a char to a varchar2, there is a padding of the varchar2 with spaces (so length of the zone is always the same) and there are no null values.

            If you do afterwards update table ... set column=trim(column) then you may have a not null constraint violation, but never when converting column datatype.

            The message tells : "column must be null to change from char to varchar2". This is not the case in all versions <11. (I didn't test oracle 11 but it would be a regression).

            I can find a workaround (copy table / add columns / ...) but I think this has to be fixed.
            I didn't test long to clob/blob conversion, but the only way I know to convert a long to a clob is an alter table. If it doesn't work, I don't know what will be done when long will not be supported anymore.
            • 3. Re: cannot convert from char to varchar2 anymore
              Rodney Barnett
              I don't get this error...
              Connected to:
              Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Beta
              
              SQL> create table softtypes (id number (9) not null, description char (30) not null, production char(1) not null, commentaire char(254) not null);
              
              Table created.
              
              SQL> desc softtypes
               Name                                      Null?    Type
               ----------------------------------------- -------- ----------------------------
               ID                                        NOT NULL NUMBER(9)
               DESCRIPTION                               NOT NULL CHAR(30)
               PRODUCTION                                NOT NULL CHAR(1)
               COMMENTAIRE                               NOT NULL CHAR(254)
              
              SQL> insert into softtypes values (1, 'description', 'P', 'commentaire');
              
              1 row created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> alter table softtypes modify(description varchar2(30) ,commentaire varchar2(254) );
              
              Table altered.
              
              SQL> desc softtypes
               Name                                      Null?    Type
               ----------------------------------------- -------- ----------------------------
               ID                                        NOT NULL NUMBER(9)
               DESCRIPTION                               NOT NULL VARCHAR2(30)
               PRODUCTION                                NOT NULL CHAR(1)
               COMMENTAIRE                               NOT NULL VARCHAR2(254)
              
              SQL>
              What other things are going on?

              Rodney
              • 4. Re: cannot convert from char to varchar2 anymore
                Udo
                I can't reproduce it on my test instances (Win x86, Linux x86-64) either.
                When I enter any special characters so the string to be stored needs more than the maximum capacity in bytes, I receive the corresponding error (ORA-12899 "value too large for column %s (actual: %s, maximum: %s)".
                If I change the column capacity from byte to characters, I can store the string as long as its length fits to the column definition. If I try to change back to bytes I receive the appropriate error (ORA-01441 "cannot decrease column length because some value is too big").

                So, since the problem seems not to be related to the NOT NULL constraint, do you have any other constraint on this or some other table that might be affected by the alter statement? Try to check using
                select * from user_constraints where table_name = 'SOFTTYPES';
                -Udo