ORACLE XML From SELECT statement

Hello Experts,

I have a table; ErrTbl with 4 columns:  Process, Num, Desc , StartTime.

What I like to do, is send out an email and have the table results in a nice format.  In sql server, I'm able to pull the results with the following query:

SELECT
td =[Process], '',
td =[Num],   '',
td =[Desc],   '',
td =[StartTime],   '',
td =[StartTime],   ''      
FROM ErrTbl            
FOR XML PATH('tr'), TYPE
                                    
I would like to get similar results using Oracle 10g.  I've played around with DBMS_XMLQuery, but I'm not getting the results that I'm looking for.

Any suggestions are greatly appreciated.

Thank you
DBL9SSGAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Don't use LONG.  Use CLOB.  They are a lot easier to deal with.

Since you really have no need to generate the entire HTML in a single statement I just thought of something.

Just brute force the output.

Check the sample below (not tested... pseudo code but should be close).  You just need to add the UTL_MAIL (or UTL_SMTP) code to send the message_body.

Once you get the UTL_SMTP code working you can change message_body to a CLOB and should be fine.

There is a lot of examples usint UTL_SMTP to send mail using Oracle.
drop table tab1 purge;
create table tab1 (col1 char(1), col2 char(1), col3 char(1));
insert into tab1 values('a','b','c');



create or replace procedure checkErrors
is
	message_body varcahr2(32767);
begin
	
	message_body := '<table>';
	message_body := message_body || '<tr><th>Col1</th><th>Col2</th><th>Col3</th></tr>';

	for i in (select col1,col2,col3 from tab1) loop
		message_body := message_body || 
			'<tr><td>' || col1 || '</td><td>' || col2 || '</td><td>' || col3 || '</td></tr>';
	end loop;
	message_body := '</table>';
end;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Some of use don't know sqlserver.  Please post sample data and expected results.
0
 
DBL9SSGAuthor Commented:
I'm attaching a screen shot of what I would like to do.
EmailRslt.JPG
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
OP_ZaharinCommented:
by using SQL Plus:

set markup HTML on
spool index.html
select * from <tablename>;
spool off
set markup HTML off
0
 
slightwv (䄆 Netminder) Commented:
With the sqlserver code above and that screenshot it looks like a simple html table not really xml.

A picture of the expected results doesn't help much since it depends on the display tool.

Can you provide the specific xml/html you want from an Oracle query for those rows in a table?
0
 
DBL9SSGAuthor Commented:
My plan is to send out an email, and here's the body for the email:  

Let me know if this is what you're asking for.

'<H1><FONT FACE="Geneva, Arial" SIZE=3>An Error Has Occurred</FONT></H2><table border="1"><th>ProcessName</th><th>ProcessStep</th><th>ProcessStepName</th><th>StartTime</th><th>ProcessStatus</th><tr><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>usp_Import_ISF_Files_Main</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>1</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>1.0 Loading E2K Data</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2011-03-08T08:00:00.977</FONT></TD></tr><tr><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>usp_Import_ISF_Files_Main</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2.0 Executing usp_FTPGet_Files</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2011-03-08T08:54:12.607</FONT></TD></tr><tr><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>usp_Import_ISF_Files_Main</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>3</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2.5 Truncate Production Tables</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2011-03-08T08:54:50.020</FONT></TD></tr><tr><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>usp_Import_ISF_Files_Main</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>4</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>3.0 Executing usp_ImportTableData @ImpDirectory = D:\ISM_Temp\ISF_Input</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2011-03-08T08:54:50.367</FONT></TD></tr><tr><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>usp_Import_ISF_Files_Main</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>5</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>4.0 Executing usp_ISF_users_IMP</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2011-03-08T08:59:29.480</FONT></TD></tr><tr><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>usp_Import_ISF_Files_Main</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>6</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>5.0 Executing usp_Export_Files D:\ISM_Temp\ISF_Output</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2011-03-08T09:01:18.537</FONT></TD></tr><tr><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>usp_Import_ISF_Files_Main</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>7</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>6.0 Executing usp_FTPPut_Files</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2011-03-08T09:02:09.810</FONT></TD></tr><tr><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>usp_Import_ISF_Files_Main</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>8</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>7.0 Executing ups_Create_WK_ISF_Tbls</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2011-03-08T09:02:44.240</FONT></TD></tr><tr><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>usp_Import_ISF_Files_Main</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>9</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>8.0 Executing ups_Create_WK_ISF_Tbls_BOL</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2011-03-08T09:04:08.680</FONT></TD></tr><tr><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>usp_Import_ISF_Files_Main</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>10</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>Completion Successfully</FONT></TD><td nowrap><FONT FACE="Geneva, Arial" SIZE=1>2011-03-08T09:05:24.573</FONT></TD></tr></table><br/>';
0
 
OP_ZaharinCommented:
apart from my simple approach above, you can do advance formatting on the html codes based on this link:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch7.htm
0
 
slightwv (䄆 Netminder) Commented:
Aside from sqlplus, Oracle has several very powerful methods of generating XML.

It can generate just about any variation you need.  If the sqlplus/cmdline approach is what you need then the link above will probably give you what you need.

If you need to issue a select statement in some other tool/platform/??? and need a single select to generate the table, please let us know the tool/platform/??? you want to use.

For example:  PL/SQL stored procedures can send an email directly from the database.

Again, not a sqlserver person are you saying the initially provided simple select statement will generate the html table you posted complete with headings?  
0
 
slightwv (䄆 Netminder) Commented:
>>you need to write Oracle procedure to send emails:

If you want them to use that approach then you need a solution that does not use sqlplus that you suggested in the previous posts.
0
 
DBL9SSGAuthor Commented:
We currently have a package that uses UTL_MAIL.SEND to send out an email.  Within that proc, we supply the message, which is the body of the email, and this is where I'd like to place XML.

I just ran the sqlplus/cmdline approach and the results are EXACTLY what I'm looking for:  the results from SELECT * FROM Table look perfect.  However, can I get that result into the message body to send via UTL_Mail?

 
0
 
OP_ZaharinCommented:
yes - i agree with you.
i'm not clear with the question. he is asking for XML yet showing sample of HTML. well - i added the solution to generate an email via Oracle pl/sql :)

cheers.
OP
0
 
OP_ZaharinCommented:
DBL9SSG,
glad that the HMTL code in sql plus helps.

this is a great document of sending email using UTL_Mail
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/u_mail.htm

first you need to grant the user on UTL_MAIL : grant execute on utl_mail to <user>;
0
 
DBL9SSGAuthor Commented:
OP Zaharin,
I just realized that I'm typing XML, but I'm thinking HTML.  My apologies, its been a long day.  

You are correct, I'm giving you HTML earlier.  
0
 
OP_ZaharinCommented:
No worries DBL9SSG :)
hope the Oracle documentation helps
0
 
slightwv (䄆 Netminder) Commented:
You realize that utl_mail.send is limited to a 32K message body.  If you need more than that you need to use UTL_SMTP.

I've never tried to simulate the sqlplus HTML reporting in pl/sql and cannot find a quick link that says how.

If no other Expert can provide the solution, I'll see what I can come up with when I get back to work tomorrow.
0
 
DBL9SSGAuthor Commented:
Thank you all for your inputs, but I have another quertion for OP Zaharin:
The link you sent me to Generate HTML Reports, can I generate the HTML from within PL/SQL and assign that HTML to a LONG variable ?


SlightWV, thanks for the heads up on 32K limit.  I'll investigate on using UTL_SMTP.  I'll certaintly wait to see if you can find anything on HTML reporting in PL/SQL.

0
 
DBL9SSGAuthor Commented:
Slight-
Got your code, basically, we'll have to generate the HTML ourselves?  Oracle doesn't have a way of doing it more simple?


I'm going to accept multiple solutions and close this out, but if you think of anything else, drop me a line at my personal email: <email removed by slightwv>.


Fellas, you guys are Awesome and thank you again for your input.
0
 
OP_ZaharinCommented:
Yes, agree with slightwv.
Use CLOB, you can store more data than Long datatype.
0
 
DBL9SSGAuthor Commented:
awesome job!
0
 
slightwv (䄆 Netminder) Commented:
Oracle and sqlserver compete head to head and typically can do anything the other one can.

Are you saying that you can issues the original select statement posted as-is to generate an html table and all you need to do is issue a simple email call in a stored procedure to get the output you posted?

If so, I'm a little impressed with sqlserver.
0
 
DBL9SSGAuthor Commented:
SLightwv-

Here's a piece that we use on sqlserver:




      DECLARE @tableHTML  NVARCHAR(MAX) ;

      SET @tableHTML =
            N'<H1><FONT FACE="Geneva, Arial" SIZE=3>An Error Has Occurred</FONT></H2>' +
            N'<table border="1">' +
            N'<th>COLUMNNAME1</th>' +
            CAST (
                        (
                              SELECT
                              td =COLUMNAME1,   ''
                              FROM SQLSERVERTABLE
                              FOR XML PATH('tr'), TYPE
                        ) AS NVARCHAR(MAX)
                  ) + N'</table>'  +
                        N'<br/>'
      
      --Disable Wrapping In Table            
      SELECT @tableHTML = REPLACE(@tableHTML,'<td','<td nowrap')
      
      --Change font style and size
      SELECT @tableHTML = REPLACE(@tableHTML,'<td nowrap>','<td nowrap><FONT FACE="Geneva, Arial" SIZE=1>')
      SELECT @tableHTML = REPLACE(@tableHTML,'</TD','</FONT></TD')



Finally, we use @TableHTML to send on message body.

We can use something similiar from what you sent me last, so I greatly appreciate pointing me the right direction.
0
 
slightwv (䄆 Netminder) Commented:
Looks like you are basically generating the html manually as well.  That actually makes me feel better.  Still not that much difference between the two.  Its pretty much the same thing I posted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.