Re: procedure output

From: Brian Dick <bdick_at_home.com>
Date: Thu, 08 Nov 2001 20:00:59 GMT
Message-ID: <%vBG7.3020$Xb7.16426@news1.wwck1.ri.home.com>

I threw this out without testing it. Then I notice Ken was using the scott schema, so I tested it and changed the "union" to "union all". Also, the "group by" in the second query required me to wrap it in a dynamic table. I sent Ken the corrected solution. For the benefit of others, here it is.

Question. I don't have a sys.types package on my database, so I created one myself. Is there a script for it somewhere?

create or replace 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 all

select *

    from (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 all

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

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:2vAG7.23223$D5.8931337_at_typhoon.san.rr.com...
> Carefull, the 'union' sorts that data.
>
>
> Brian Dick wrote:
> >
> > 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 Thu Nov 08 2001 - 14:00:59 CST