Re: procedure output

From: Brian Dick <bdick_at_home.com>
Date: Thu, 08 Nov 2001 01:45:31 GMT
Message-ID: <%slG7.2672$Xb7.12084@news1.wwck1.ri.home.com>

Union the three select statements together.

procedure create_xml...

( O_RESULT_1 OUT sys.types.CURSOR_TYPE)

    AS
    /* create simple xml to be merged with xsl */     BEGIN     open o_result_1 for
    select '<deptemp>' || chr(10) || '<body>' from dual

union

select

       '<emp>'|| chr(10) ||
       '<deptno>'||a.deptno||'</deptno>'|| chr(10) ||
       '<dname>'||dname||'</dname>'|| chr(10) ||
       '<empno>'||empno||'</empno>'|| chr(10) ||
       '<ename>'||ename||'</ename>'|| chr(10) ||
       '<sal>'||sal||'</sal>'|| chr(10) ||
       '</emp>'

from dept a, emp b
where a.deptno = b.deptno
order by a.deptno, empno

union

select '</deptemp>' || chr(10) || '</body>' from dual;

END; "Ken Chesak" <kchesak_at_austin.rr.com> wrote in message news:W6lG7.12050$qb.706208_at_typhoon.austin.rr.com...
> I am using the following procedure to create an XML file. The procedure
is
> being called by websphere. Right now there are 3 output parameters, I
would
> like to make this just 1 output. How can I combine the 3 selects into 1
> output.
>
> Thanks
>
> procedure create_xml...
>
> ( O_RESULT_1 OUT sys.types.CURSOR_TYPE,
> O_RESULT_2 OUT sys.types.CURSOR_TYPE,
> O_RESULT_3 OUT sys.types.CURSOR_TYPE)
> AS
> /* create simple xml to be merged with xsl */
> BEGIN
>
> open o_result_1 for
> select '<deptemp>' || chr(10) || '<body>' from dual;
>
> open o_result_2 for
> select
> '<emp>'|| chr(10) ||
> '<deptno>'||a.deptno||'</deptno>'|| chr(10) ||
> '<dname>'||dname||'</dname>'|| chr(10) ||
> '<empno>'||empno||'</empno>'|| chr(10) ||
> '<ename>'||ename||'</ename>'|| chr(10) ||
> '<sal>'||sal||'</sal>'|| chr(10) ||
> '</emp>'
> from dept a, emp b
> where a.deptno = b.deptno
> order by a.deptno, empno;
>
>
> open o_result_3 for
> select '</deptemp>' || chr(10) || '</body>' from dual;
>
> END;
>
>
>
>
Received on Wed Nov 07 2001 - 19:45:31 CST