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
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
Some of use don't know sqlserver. Please post sample data and expected results.
ASKER
I'm attaching a screen shot of what I would like to do.
EmailRslt.JPG
EmailRslt.JPG
by using SQL Plus:
set markup HTML on
spool index.html
select * from <tablename>;
spool off
set markup HTML off
set markup HTML on
spool index.html
select * from <tablename>;
spool off
set markup HTML off
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?
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?
ASKER
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
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
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
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch7.htm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
>>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.
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.
ASKER
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?
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?
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
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
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>;
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>;
ASKER
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.
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.
No worries DBL9SSG :)
hope the Oracle documentation helps
hope the Oracle documentation helps
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Yes, agree with slightwv.
Use CLOB, you can store more data than Long datatype.
Use CLOB, you can store more data than Long datatype.
ASKER
awesome job!
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.
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.
ASKER
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','
--Change font style and size
SELECT @tableHTML = REPLACE(@tableHTML,'<td nowrap>','<td nowrap><FONT FACE="Geneva, Arial" SIZE=1>')
SELECT @tableHTML = REPLACE(@tableHTML,'</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.
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','
--Change font style and size
SELECT @tableHTML = REPLACE(@tableHTML,'<td nowrap>','<td nowrap><FONT FACE="Geneva, Arial" SIZE=1>')
SELECT @tableHTML = REPLACE(@tableHTML,'</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.
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.