4 Replies Latest reply: May 11, 2011 6:34 PM by 438346 RSS

    DB2 to Oracle: DEFAULT value for BLOB attribute

    user15004
      The DEFAULT constraint in DB2 is converted by Oracle Migration Workbench (OMWB) as follows:

      ALTER TABLE ADMIN.REQ MODIFY (BAAGE DEFAULT "SYSIBM"."BLOB"(''));

      This fails with the following message:
      Failed to create default for Table :ADMIN.REQ; ORA-02262: ORA-4044 occurs while type-checking column default value expression

      Using the following works, but fails when DEFAULT is inserted into the table:
      ALTER TABLE ADMIN.REQ MODIFY (BAAGE DEFAULT TO_BLOB(''));

      ORA2: REPUSER4> INSERT INTO ADMIN.REQ VALUES(1, 1, 1, 1, 1, SYSDATE, 1, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
      INSERT INTO ADMIN.REQ VALUES(1, 1, 1, 1, 1, SYSDATE, 1, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
      *
      ERROR at line 1:
      ORA-01400: cannot insert NULL into ("ESPADMIN"."REQ"."BAAGE")


      ORA2: REPUSER4>

      I want to make the default for the column as 'blank space'. So, I need to have the hex number for the same set as default for the column definition. I tried '0x255', however, it fails with invalid hex number. How can I resolve this?

      Thanks in advance.
        • 1. Re: DB2 to Oracle: DEFAULT value for BLOB attribute
          395098
          For better or worse, '' and NULL are the same thing in Oracle DB (effectively, there is no empty string value; only the "non-value" NULL). If you have placed a NOT NULL constraint on that column making the default value NULL sets up an inherent conflict.

          Did you try TO_BLOB(' '). since "TO_BLOB" converts its argument to a blob, should be able to convert the that string ( it isn't NULL anymore) into a real/instantiated BLOB value.

          In ASCII, the space char is 0x20. However, "TO_BLOB" should help mitagate any character set transition issues if they pop up later.
          • 2. Re: DB2 to Oracle: DEFAULT value for BLOB attribute
            user15004
            Thanks for the input.

            I did try the TO_BLOB(' '); but it fails as shown below:
            ORA2: REPUSER4> create table d(name blob not null enable);

            Table created.

            ORA2: REPUSER4> alter table d modify (name default to_blob(' '));
            alter table d modify (name default to_blob(' '))
            *
            ERROR at line 1:
            ORA-01465: invalid hex number


            Even the 0x20 errors out when used as show below:
            ORA2: REPUSER4> alter table d modify (name default to_blob('0x20'));
            alter table d modify (name default to_blob('0x20'))
            *
            ERROR at line 1:
            ORA-01465: invalid hex number


            ORA2: REPUSER4>


            However, using RAWTOHEX works, is this ok?

            ORA2: REPUSER4> alter table d modify (name default rawtohex(' '));

            Table altered.

            ORA2: REPUSER4>


            Thanks in advance.
            • 3. Re: DB2 to Oracle: DEFAULT value for BLOB attribute
              630705
              The correct query is
              ALTER TABLE ADMIN.REQ MODIFY (BAAGE DEFAULT EMPTY_BLOB());
              I hope this might be helpful for others who face this situation

              Try the latest released version of Oracle SqlDeveloper and use its Migration Workbench to migrate Db2 database
              to Oracle.
              • 4. Re: DB2 to Oracle: DEFAULT value for BLOB attribute
                438346
                You can use cast_to_raw when you insert,update and define default value to blob column
                utl_raw.cast_to_raw('string value you need to convert to raw')