CODEWITHSUNDEEP
oracle-databaseoracle11gclob
AAA
AAA

Reputation: 2450

concatenate multiple clobs withing Oracle Procedure

I have an Oracle procedure that is going to accept multiple values within a parameter. Part of the procedure will run a select statement putting the results of the parameter in the where clause and placing the concatenated CLOBs into a variable. I am currently using the query below in the procedure but when I run it I get the error below.

If CLOB_ID is not null then
    SELECT cast((collect(CLOB_TEXT) )as CLOB )
    into v_MessageBody 
    FROM MESSAGE_CLOB_TABLE 
    WHERE MESSAGE_ID in CLOB_ID;
End If;

Error: ORA-00932: incosistant datatypes: expected - got CLOB

I also tried writing this using a LISTAGG function but LISTAGG doesnt work with the CLOB values in the MESSAGE_CLOB_TABLE

Any help would be greatly appreciated! I am using Oracle 11g.

Upvotes: 2

Views: 10258

Answers (1)

ThinkJet
ThinkJet

Reputation: 6735

If you need to concatenate in PL/SQL simplest variant is to loop through all selected records and append all found records to result:

create or replace function get_message(p_msg_id in number) return CLOB
is
  v_MessageBody CLOB;
begin
  -- create new instance of temporary CLOB
  dbms_lob.createtemporary(v_MessageBody, true);

  -- Fill instance with lines
  for cMessages in (
    select clob_text 
    from message_clob_table 
    where message_id = p_msg_id
    order by message_row
  )
  loop
    -- add line
    dbms_lob.append(v_MessageBody, cMessages.clob_text);
  end loop;

  -- return collected lines as single CLOB
  return v_MessageBody;
end;

Example above works if type of CLOB_TEXT field is CLOB and you need to collect only one message. You can test function in this SQLFiddle.

If you need to select many messages together based on list of his ID's, function becomes a little bit more complex, but principle remains the same.

Upvotes: 3

Related Questions