We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
2 days, 18 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
ORACLE XML From SELECT statement
Posted on 2011-03-08
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
8-
8
7
24 Comments
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
Active today
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?
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
you need to write Oracle procedure to send emails:
1- http://arunrathod.blogspot.com/2008/05/sending-email-using-plsql.html
2- http://www.techrepublic.com/blog/programming-and-development/sending-e-mail-from-an-oracle-database-with-utlsmtp/515
1- http://arunrathod.blogspot.com/2008/05/sending-email-using-plsql.html
2- http://www.techrepublic.com/blog/programming-and-development/sending-e-mail-from-an-oracle-database-with-utlsmtp/515
Active today
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?
Active today
>>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.
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>;
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.
Active today
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.
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.
Active today
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.
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;
/
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.
Active today
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.
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.
Featured Post
For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| oracle spooling query into csv eliminating new line character | 9 | 55 | |
| error in oracle form | 11 | 53 | |
| Need help installing oracle client,it is not installing. | 3 | 56 | |
| ORA-01843: not a valid month - use NVL | 14 | 21 |
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…
This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself).
I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month2 days, 18 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.