Design a procedure to fetch data from table and output XML in .NET

610795

Oracle 9i

How can i design a procedure to fetch data from a table and the output should be in XML in the calling environment.


create table emp(empno number PRIMARY KEY, ename varchar2(40), sal number)

insert into table emp values(1, 'Dave',1000);
insert into table emp values(2, 'Den',2000);
insert into table emp values(3, 'Ben',1000);
insert into table emp values(4, 'Bel',1000);

Is this idea correct

Create Package pack
is
type reft is REF CURSOR return SYS.XMLTYPE;
end pack;

create procedure proc(eid in number, cs out pack.reft)
is
begin
open cs for select ename, sal
from emp
where empno=eid;
end;

calling environment example asp.net or c#.net then HOW TO WRITE THE BELOW

declare
refvar pack.reft;
xmldt SYS.XMLTYPE; -- unsure of this step, i am assuming that a variable of xml data type relating to the calling environment should be declared.
begin
proc(&eid,refvar);
loop
forall i in 1 .. refvar%rowcount;
exit when refvar%notfound;
fetch refvar bulkcollect into xmldt
-- display all the data example like <empid> 10 <ename> 'Dave' </ename> --<sal>1000</sal></empno><empid> 20 <ename> 'Den' </ename> <sal>2000</sal></empno>
end loop;
close refvar;
end;

Drop table emp cascade;


If this is entirely wrong please, show a proper solution where you will be extracting data from tables and supplying it to a .NET environment as a XML file with XML tags.
Thank you.

Edited by: Trooper on Jan 8, 2010 12:15 AM

Tubby

Can you just use XMLELEMENT in your REF CURSOR?

[http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions172a.htm#1128678]

730428

You can use a single query and there's plenty of XML functions in Oracle, just an example:

SQL>select sys_xmlagg(xmlelement("emp",xmlforest(empno,ename,sal)))
  2* from emp

SYS_XMLAGG(XMLELEMENT("EMP",XMLFOREST(EMPNO,ENAME,SAL)))
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<emp><EMPNO>1</EMPNO><ENAME>Dave</ENAME><SAL>1000</SAL></emp><emp><EMPNO>2</EMPNO>
<ENAME>Den</ENAME><SAL>2000</SAL></emp><emp><EMPNO>3</EMPNO><ENAME>Ben</ENAME>
<SAL>1000</SAL></emp><emp><EMPNO>4</EMPNO><ENAME>Bel</ENAME><SAL>1000</SAL></emp>
</ROWSET>

Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/01/07/creare-documenti-office-da-plsql/]

610795

Thank you for your replies,

So your saying i could use this query in a procedure(eid in number, cursor out cur_data_type) which outputs xml in the calling environment.

Is my understanding right?

Thank you.

BluShadow

If you are returning a ref cursor to your .NET environment so that the .NET environment fetches from the cursor, then using the XMLELEMENT etc. functionality of SQL (known as SQLX) returns an XMLTYPE oracle datatype. I don't know .NET so I'm not sure what support it has for the oracle XMLTYPE datatype, but I can tell you it is based on the CLOB datatype. If it can't deal with that datatype then the query would have to be done within the oracle procedure itself and you return the value from the procedure (OUT parameter) or from a function as a supported datatype (I assume .NET does support CLOB).

730428

So your saying i could use this query in a procedure

I think you don't need a procedure at all, I don't know .Net framework but I think that, like in Java for example, you can execute a query and get the query result as a string.

Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/01/07/creare-documenti-office-da-plsql/]

BluShadow

Massimo Ruocchio wrote:

So your saying i could use this query in a procedure

I think you don't need a procedure at all, I don't know .Net framework but I think that, like in Java for example, you can execute a query and get the query result as a string.

Although I think the general concept of tiered design would say that you would keep your database queries at the database level, which would typically be on the database itself and thus allow for the database to ensure the queries are valid at design time rather than at run time.

730428

Although I think the general concept of tiered design would say that you would keep your database queries at the database level, which would typically be on the database itself and thus allow for the database to ensure the queries are valid at design time rather than at run time.

Agreed, but we know many times queries are built at application level and we don't know how OP's application is designed.

I just wanted to express the concept that creating a procedure is not technically compulsory to acheive this requirement

Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/01/07/creare-documenti-office-da-plsql/]