By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.