Solved

ORACLE XML From SELECT statement

Posted on 2011-03-08
24
943 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:DBL9SSG
  • 8
  • 8
  • 7
24 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35076489
Some of use don't know sqlserver.  Please post sample data and expected results.
0
 

Author Comment

by:DBL9SSG
ID: 35076538
I'm attaching a screen shot of what I would like to do.
EmailRslt.JPG
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35076627
by using SQL Plus:

set markup HTML on
spool index.html
select * from <tablename>;
spool off
set markup HTML off
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35076647
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
 

Author Comment

by:DBL9SSG
ID: 35076731
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35076750
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
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 75 total points
ID: 35076791
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35076850
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35076867
>>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
 

Author Comment

by:DBL9SSG
ID: 35076954
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35076970
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35077028
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
 

Author Comment

by:DBL9SSG
ID: 35077031
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35077107
No worries DBL9SSG :)
hope the Oracle documentation helps
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35077128
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
 

Author Comment

by:DBL9SSG
ID: 35077270
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 175 total points
ID: 35077315
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
 

Author Comment

by:DBL9SSG
ID: 35077411
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35077429
Yes, agree with slightwv.
Use CLOB, you can store more data than Long datatype.
0
 

Author Closing Comment

by:DBL9SSG
ID: 35077455
awesome job!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35077514
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
 

Author Comment

by:DBL9SSG
ID: 35077568
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35077648
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now