Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

610795Jan 7 2010 — edited Jan 8 2010
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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 5 2010
Added on Jan 7 2010
7 comments
907 views