Home » Developer & Programmer » JDeveloper, Java & XML » PL/SQL Cursor output as XML
PL/SQL Cursor output as XML [message #294407] Thu, 17 January 2008 08:31 Go to next message
rkaula
Messages: 22
Registered: December 2005
Junior Member
Is it possible to transform the output of PL/SQL cursor with dbms_output statements as XML. Any utility??? For example,

create table xyz
(a1 integer,
a2 varchar2(5));

insert into xyz values(1, 'xxx');
insert into xyz values(2, 'yxx');
insert into xyz values(3, 'xxx');
insert into xyz values(4, 'xxk');


declare
begin
for c_row in (select * from xyz)
loop
if c_row.a2 = 'xxx' then
dbms_output.put_line(c_row.a1 || c_row.a2 || 'Correct');
else
dbms_output.put_line(c_row.a1 || c_row.a2 || 'Not Correct');
end if;
end loop;
end;

Suppose I want the output of dbms_output statement appear as XML?

Thanks.

Rajeev.
Re: PL/SQL Cursor output as XML [message #294428 is a reply to message #294407] Thu, 17 January 2008 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;
DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>12/17/1980 0:0:0</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
   </ROW>
   <ROW num="2">
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/20/1981 0:0:0</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
   <ROW num="3">
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/22/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
</ROWSET>

Forgot: please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel

[Updated on: Thu, 17 January 2008 10:09]

Report message to a moderator

Re: PL/SQL Cursor output as XML [message #294433 is a reply to message #294428] Thu, 17 January 2008 11:02 Go to previous messageGo to next message
rkaula
Messages: 22
Registered: December 2005
Junior Member
The output of DBMS_XMLQUERY is coming from a SQL query. My question was on generating XML based on processing result. Is there a utility that combines the processing result in one XML output. As in the script, the 4 output rows with static text displayed as XML.

Thanks.
Re: PL/SQL Cursor output as XML [message #294438 is a reply to message #294433] Thu, 17 January 2008 11:54 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Since you are outputting text, how should this be translated to XML? You just write two lines of text.
Re: PL/SQL Cursor output as XML [message #294447 is a reply to message #294438] Thu, 17 January 2008 12:22 Go to previous messageGo to next message
rkaula
Messages: 22
Registered: December 2005
Junior Member
Generally it seems XML output from tables involves SQL query. However, is it possible to combine PL/SQL processing results in one XML output. Maybe separate IF statements in the logic deliver different outputs, which need to be combined in one XML output.
Re: PL/SQL Cursor output as XML [message #294452 is a reply to message #294447] Thu, 17 January 2008 12:33 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
What output do you exactly expect?
Re: PL/SQL Cursor output as XML [message #294461 is a reply to message #294433] Thu, 17 January 2008 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your problem displaying the result the same way dbms_xmlquery does?
It is not hard, see the format, it is really simple.

Regards
Michel
Re: PL/SQL Cursor output as XML [message #294500 is a reply to message #294461] Thu, 17 January 2008 16:27 Go to previous messageGo to next message
rkaula
Messages: 22
Registered: December 2005
Junior Member
dbms_xmlquery is great. It gets the table data out as XML. But, how will dbms_xmlquery work if some of the output values also include variable values or static text within a PL/SQL program unit? Maybe I am not able to explain what the question clearly. Let me try again.

declare
t1 varchar2(5) := 'abc';
begin
for c_row in (select * from xyz)
loop
if condition1 then
dbms_output.put_line(c_row.a1 || c_row.a2 ||t1|| 'Correct');
end if;
. . .
if condition2 then
dbms_output.put_line(c_row.a1 || c_row.a2 ||t1|| 'Not Correct');
end if;
end loop;
end;

Is it possible to display the dbms_output lines above which have a mix of table data, variable value, and static text as XML. I guess one can manually add tags, but it would be better if there is a utility that enables one to collect the values and then spit out the XML.
Re: PL/SQL Cursor output as XML [message #294540 is a reply to message #294407] Thu, 17 January 2008 23:42 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Maybe the example you've given is a simplified one, but at least here your conditions can be included in the select itself with CASE/DECODE to print out the static text/variable values within XML.

SQL> select dbms_xmlgen.getxml('select a1
  2                                  , a2
  3                                  , DECODE(a2,'||''''||'xxx'||''''||','||''''
  4                                  ||'Correct'||''''||','||''''
  5                                  ||'Not Correct'||''''||') a3 
  6                             from xyz') xmldata 
  7  from dual;

XMLDATA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <A1>1</A1>
  <A2>xxx</A2>
  <A3>Correct</A3>
 </ROW>
 <ROW>
  <A1>2</A1>
  <A2>yxx</A2>
  <A3>Not Correct</A3>
 </ROW>
 <ROW>
  <A1>3</A1>
  <A2>xxx</A2>
  <A3>Correct</A3>
 </ROW>
 <ROW>
  <A1>4</A1>
  <A2>xxk</A2>
  <A3>Not Correct</A3>
 </ROW>
</ROWSET>
(Note that Oracle recommends using dbms_xmlgen in place of dbms_xmlquery wherever possible.)

Then there is XMLELEMENT which adds tags to individual elements, but if you have to use dbms_output.put_line to print out each element that won't make things better - the cleaner way would be to manually add tags.

If your requirements are more complex that that you might want to look at XMLDOM package for generating XML - that gives a lot of flexibility to the way XML can be structured.
Previous Topic: XML namespace extraction
Next Topic: Java Tester
Goto Forum:
  


Current Time: Thu Mar 28 05:09:21 CDT 2024