PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB

Question

I want to delete XML empty nodes when insert a table in stored procedure. I have tried some code, it throw error as

PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB.

So please any one help me to find out the clob error. int_content column variable is clob only.

INSERT
      INTO int_details
        (
          int_rid,
          int_cpy,
          int_brn,
          int_sre,
          int_ree,
          int_type,
          int_filename,
          int_content,
          int_status,
          int_remarks,
          int_blob_content
        )
        VALUES
        (
          v_int_rid,
          p_cpy_rid,
          1,
          p_inty_rid,
          null,
          null,
          TO_CHAR(sysdate, 'DDMMYYHH24MISS')||'.xml',
          deleteXML(v_clob, '//*[not(text())][not(*)]').getclobval(),
          'Active',
          'EDI Generated on'||TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'),
          v_blob
        );

What's going on with those 3 nullin the middle of the values list? It looks like you're missing at least one comma. Why concatenate a null onto a string? It's a non-op. Also I recommend you make your values list 11 lines (don't put a new line in your string concats) or comment your values so you know what they are when there are a lot of concats — Caius Jard
The first TO_CHAR has || without anything before it, that’s not proper? — Sami Kuhmonen
But now that you've added missing commas to all 3 nulls you have a string concat with nothing on the left of it and you have 12 values being inserted into 11 columns — Caius Jard
for null,i will give value after some time.. now i want to know the reason of clob issue in deleteXML(v_clob, '//*[not(text())][not(*)]').getclobval(), line — Suriya Jeva

Caius Jard Caius Jard · Accepted Answer · 2019-06-08T05:53:46

For big messy inserts lay them out with the same number of lines in the values and columns lists:

INSERT
  INTO int_details
    (
      int_rid,
      int_cpy,
      int_brn,
      int_sre,
      int_ree,
      int_type,
      int_filename,
      int_content,
      int_status,
      int_remarks,
      int_blob_content
    )
    VALUES
    (
      v_int_rid,
      p_cpy_rid,
      1,
      p_inty_rid,
      null,
      null,
      TO_CHAR(sysdate, 'DDMMYYHH24MISS')||'.xml',
      deleteXML(v_clob, '//*[not(text())][not(*)]').getclobval(),
      'Active',
      'EDI Generated on'||TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'),
      v_blob
    );

Note that I have removed one of your nulls to get down to 11 values. THIS MAY NOT BE CORRECT. I can't know what you want on this case; you have to solve it properly

Or comment the hardcides values, or separate the blocks of code into visually countable things, when things go multiline

INSERT
  INTO int_details
    (
      int_rid,
      int_cpy,
      int_brn,
      int_sre,
      int_ree,
      int_type,
      int_filename,
      int_content,
      int_status,
      int_remarks,
      int_blob_content
    )
    VALUES
    (
      v_int_rid,
      p_cpy_rid,
      1, --int_brn
      p_inty_rid,
      null, --int_ree
      null, --int_type

      --int_filename
      null
        ||TO_CHAR(sysdate, 'DDMMYYHH24MISS')||'.xml',

      --int_content
      deleteXML(v_clob, '//*[not(text())][not(*)]').getclobval(),

      --int_status
      'Active',

      --int_remarks
      'EDI Generated on'
      ||TO_CHAR(sysdate, 'DD-MON-YYY HH24:MI:SS'),

      v_blob
    );

PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB

1 Answers