Geek asks and answers

Return record set as HTML for Oracle

I have an Oracle database and you need to send emails based on events that occur in my Dot Net web application. HTML email notifications. I have different templates that are used depending on the email notifications that should be generated. Each of them is associated with Sproc. Therefore, when the event is fired, I pass the Id to the handler, and it calls sproc. Then I get an email template that is in HTML format and has tags that should be replaced with fields from sproc. Then I search and replace any tags in the template with the corresponding field from the recordset. The recipeint email is placed and the email is sent as HTML. It all works great.

However, I now have a requirement to show several rows in a table in an email notification. So, for example, the details of books from the library. I could get all the rows and build the rows of the HTML table on the fly in a point network, iterating over the recordset and creating a new row for each recordset, but it will break my general tag replacement approach. So I would like to know if I can build these few rows in PL SQL and return it as one field, where I can replace the tag in my html email template with the contents of the field from the recordset. This field will contain the HTML version of all required entries ... ready to be used as a table. So I would have a tag in my template called OVerDueBooks and replace that tag with the Over_Due_Books field from my recordset.In this field, all recorded records in html format will be recorded, so I need to replace the tag with content.

thanks Mika

+3
source share
2 answers

Thanks Gary

I got this little gem from Sliqhtwv on EE, and her work is a pleasure. Thanks for your help .. many apprieciated.

From Sliqhtwv:

drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1), col3 char(1));

insert into tab1 values('a','1','A');
insert into tab1 values('b','2','B');
insert into tab1 values('c','2','C');
commit;

select xmlagg(xmlelement("TR",
    xmlforest(col1 as "TD",col2 as "TD",col3 as "TD"))).getclobval()
from tab1
0
source

How much data? As long as the collected length does not exceed 4000 bytes, this should not be difficult.

Search for STRAGG for the various mechanisms to combine the VARCHAR2 collection on one line.

The rest should consist of SELECT '<tr>'||....||'</tr>' ....

Take a look at UTL_URL.ESCAPE to remove any characters that may interfere with HTML.

+1
source

More articles:


All Articles