create table A (keys number, text CLOB);
create table B (keys number, text varchar2(100));
insert into B values (1, 'How do I '); insert into B values (2, ' merge multiple rows '); insert into B values (3, ' into one row of type CLOB? ');
After inserting from table B into table A, I would like to see the following after selecting from table A:
keys text ----- ------- 1 How do I merge multiple rows into one row of type CLOB?I wrote the following, but am unsure of how to utilize dbms_lob.writeAppend() with it.
DECLARE TYPE A_table_type IS TABLE OF A%ROWTYPE INDEX BY PLS_INTEGER; t_A A_table_type; CURSOR c1 SELECT text FROM B WHERE keys BETWEEN 1 AND 3; BEGIN OPEN c1; LOOP --Fetch a configured set of rows at once FETCH c1 BULK COLLECT INTO t_A LIMIT l_ROW_LIMIT; EXIT WHEN t_A.COUNT = 0; --For each set of rows fetched... FOR x IN 1 .. t_A.COUNT LOOP dbms_lob.writeAppend(?????)
I'd suggest you break the problem down into simpler units. One issue is how to concatenate the text values into a single CLOB. I wrote a stand-alone function to do just that:
create or replace function assemble_clob(p_from in number, p_to in number) return clob is v_clob clob; -- inner one begin for rec in ( select keys, text from b where keys between p_from and p_to) loop if (rec.keys = p_from) then -- first item v_clob := rec.text; else -- subsequent item v_clob := v_clob || CHR(10) || rec.text; -- prepend newline end if; end loop; return (v_clob); end assemble_clob; /Now I can use this in SQL:
insert into a (keys, text) values (1, assemble_clob(1, 3));
Notice how this simplifies the insert statement.
When I query table A, I get:
SQL> column text format a30 SQL> select * from a; KEYS TEXT ---------- ------------------------------ 1 How do I merge multiple rows into one row of type CLOB?
Dig Deeper on Oracle development languages
Related Q&A from Dan Clamage
Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically. Continue Reading
One reader asks how to set up a report in Oracle SQL so that Monday is the first day of the week. Continue Reading
Are you seeing an invalid identifier? Learn when to use double quotes in Oracle column names to avoid this in this tip from PL/SQL expert Dan Clamage. Continue Reading