CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
p27
p27

Reputation: 2222

creating clob inside for loop of PLSQL

I have write a plsql procedure to create a html page and send it as email attachment.

In my procedure I am writing for loop which select around 500 + records, exp code is below:

declare

table clob;
footer VARCHAR2(1000);

begin

table:='<html><head></head><body><table>';
DBMS_LOB.APPEND(table, '<tr><th>ID</th><th>NAME</th></td>');    

footer:='</table></body></html>'

for rec in (

select o.id from <some tables> group by o.id

)
loop

select 
o.id,
oa.name
into id_object,name
from
objects o,
objectsattributes oa
where
o.id=oa.object_id and
o.id=rec.id;

DBMS_LOB.APPEND(table, '<tr><td>'||id_object||'</td><td>'||name||'</td></td>');

END LOOP;

DBMS_LOB.APPEND(table,footer):


-- send email with table as html attachment

END;

for less amount of data it is working file and i will get html doc as attachment with proper records and layout but when data is large (in for loop) i will get repeated data in html doc and layout is also destroyed.

i have debug it and i found that in for query I am not getting any duplicate id.

but in html doc i am getting duplicate and some time more column.

it seems that it has cashing issue or synchronizing issue or issue related to for loop or issue with clob writing.

can any one tell me how to loop large abound of data and select other data base in it in for loop.

so data will not be lost and be in sequence.

Note : above code is for describing issue actual select statement is more complex and more data.

Upvotes: 1

Views: 1338

Answers (1)

WoMo
WoMo

Reputation: 7246

You are not closing your table rows </tr> correctly in your header row or in the body rows.

-- Opening header
table:='<html><head></head><body><table>';
-- Change the closing </td> to a </tr> below to end the header row
DBMS_LOB.APPEND(table, '<tr><th>ID</th><th>NAME</th></tr>');    


-- Body rows
-- Change the closing </td> to  </tr>
DBMS_LOB.APPEND(table, '<tr><td>'||id_object||'</td><td>'||name||'</td></tr>');

Upvotes: 1

Related Questions