U0100080: The column type cannot be determined. For BLOB or CLOB data types, manually add UPDATE after DBE_LOB.LOB_WRITE

Description

When executing the SELECT INTO FOR UPDATE statement to call stored procedures such as APPEND, WRITEAPPEND, WRITE, or COPY in the DBMS_LOB package for BLOBs and CLOBs, Oracle automatically updates the corresponding BLOB and CLOB objects in tables. GaussDB tables cannot be automatically updated. You need to explicitly execute the UPDATE statement. If UGO cannot verify whether a column contains the BLOB or CLOB data type, the UPDATE statement cannot be automatically added. In this case, you need to manually confirm the data type. If there are BLOB or CLOB data types, manually add the UPDATE statement.

Database Type and Version

• Source database type and version: Oracle versions supported by UGO
• Target database type and version: GaussDB versions supported by UGO

Syntax Example

CREATE OR REPLACE PACKAGE BODY "PICCPROD"."PKG_TRANSFER_FORMAT_TP" is

PROCEDURE p_create_dsic_10301_0004(
    i_organ_id  IN VARCHAR,
    i_bank_code IN VARCHAR,
    i_send_id   IN NUMBER,
    o_scce_flag OUT NUMBER
    )AS
    p_clob1        CLOB;
    p_offset       INTEGER:=0;
    p_info1     VARCHAR2(200);

   BEGIN

       SELECT send_text
         INTO p_clob1
         FROM t_bank_text
        WHERE send_id = i_send_id
          FOR UPDATE;

      DBMS_LOB.WRITE(p_clob1,LENGTH(p_info1),p_offset+1,p_info1);
      p_offset := p_offset + length(p_info1);

  RETURN;
END;

end PKG_TRANSFER_FORMAT_TP;
/

Suggestion

In the preceding example, if the send_text column in the t_bank_text table contains the BLOB and CLOB data types, add the UPDATE statement to:

DBMS_LOB.WRITE(p_clob1,LENGTH(p_info1),p_offset+1,p_info1);

Execute the following statement:

DBE_LOB.LOB_WRITE(p_clob1,LENGTH(p_info1),p_offset+1,p_info1);
UPDATE t_bank_text SET send_text = p_clob1 WHERE send_id = i_send_id;