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 Using Oracle PL-SQL
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
Expert Dan Clamage explains how to use SQL SELECT and SQL UNION ALL statements to sort and visualize a set of sales figures. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.