Forum Stats

  • 3,815,982 Users
  • 2,259,123 Discussions
  • 7,893,352 Comments

Discussions

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

610795
610795 Member Posts: 106
edited Jan 8, 2010 8:03AM in SQL & PL/SQL
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

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    Can you just use XMLELEMENT in your REF CURSOR?

    [http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions172a.htm#1128678]
  • 730428
    730428 Member Posts: 2,087
    edited Jan 7, 2010 1:28PM
    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
    610795 Member Posts: 106
    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
    BluShadow Member, Moderator Posts: 41,878 Red Diamond
    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
    730428 Member Posts: 2,087
    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
    BluShadow Member, Moderator Posts: 41,878 Red Diamond
    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
    730428 Member Posts: 2,087
    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/]
This discussion has been closed.