Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: procedure output

Re: procedure output

From: Rangeshwara Reddy Kona <konareddy_at_hotmail.com>
Date: Wed, 7 Nov 2001 17:46:46 -0800
Message-ID: <3be9e3fd$1@news.cadence.com> 





Hi,


try this


select '<deptemp>' || chr(10) || '<body>' ||     '<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>' || '</deptemp>' || chr(10) || '</body>'


from dept a, emp b


where a.deptno = b.deptno


order by a.deptno, empno;



It generated following output in spool


SQL> @create_xml



'<DEPTEMP>'||CHR(10)||'<BODY>'






<deptemp>

<body><emp>

<deptno>10</deptno>

<dname>ACCOUNTING</dname>

<empno>7782</empno>

<ename>CLARK</ename>

<sal>2450</sal>

</emp></deptemp>

</body>




<deptemp>




'<DEPTEMP>'||CHR(10)||'<BODY>'






<body><emp>

<deptno>10</deptno>

<dname>ACCOUNTING</dname>

<empno>7839</empno>

<ename>KING</ename>

<sal>5000</sal>

</emp></deptemp>

</body>




<deptemp>

<body><emp>




'<DEPTEMP>'||CHR(10)||'<BODY>'






<deptno>10</deptno>

<dname>ACCOUNTING</dname>

<empno>7934</empno>

<ename>MILLER</ename>

<sal>1300</sal>

</emp></deptemp>

</body>




<deptemp>

<body><emp>

<deptno>20</deptno>




'<DEPTEMP>'||CHR(10)||'<BODY>'






<dname>RESEARCH</dname>

<empno>7369</empno>

<ename>SMITH</ename>




<sal>800</sal>

</emp></deptemp>

</body>




<deptemp>

<body><emp>

<deptno>20</deptno>

<dname>RESEARCH</dname>




'<DEPTEMP>'||CHR(10)||'<BODY>'






<empno>7566</empno>

<ename>JONES</ename>

<sal>2975</sal>

</emp></deptemp>

</body>




<deptemp>

<body><emp>

<deptno>20</deptno>

<dname>RESEARCH</dname>

<empno>7788</empno>




'<DEPTEMP>'||CHR(10)||'<BODY>'






<ename>SCOTT</ename>

<sal>3000</sal>

</emp></deptemp>

</body>




<deptemp>

<body><emp>

<deptno>20</deptno>

<dname>RESEARCH</dname>

<empno>7876</empno>

<ename>ADAMS</ename>




'<DEPTEMP>'||CHR(10)||'<BODY>'






<sal>1100</sal>

</emp></deptemp>

</body>




<deptemp>

<body><emp>

<deptno>20</deptno>

<dname>RESEARCH</dname>

<empno>7902</empno>

<ename>FORD</ename>

<sal>3000</sal>




'<DEPTEMP>'||CHR(10)||'<BODY>'






</emp></deptemp>

</body>




<deptemp>

<body><emp>

<deptno>30</deptno>

<dname>SALES</dname>

<empno>7499</empno>

<ename>ALLEN</ename>

<sal>1600</sal>

</emp></deptemp>




'<DEPTEMP>'||CHR(10)||'<BODY>'






</body>




<deptemp>

<body><emp>

<deptno>30</deptno>

<dname>SALES</dname>

<empno>7521</empno>

<ename>WARD</ename>

<sal>1250</sal>

</emp></deptemp>

</body>




'<DEPTEMP>'||CHR(10)||'<BODY>'







<deptemp>

<body><emp>

<deptno>30</deptno>

<dname>SALES</dname>

<empno>7654</empno>

<ename>MARTIN</ename>

<sal>1250</sal>

</emp></deptemp>

</body>





'<DEPTEMP>'||CHR(10)||'<BODY>'






<deptemp>

<body><emp>

<deptno>30</deptno>

<dname>SALES</dname>

<empno>7698</empno>

<ename>BLAKE</ename>

<sal>2850</sal>

</emp></deptemp>

</body>




<deptemp>




'<DEPTEMP>'||CHR(10)||'<BODY>'






<body><emp>

<deptno>30</deptno>

<dname>SALES</dname>

<empno>7844</empno>

<ename>TURNER</ename>

<sal>1500</sal>

</emp></deptemp>

</body>




<deptemp>

<body><emp>




'<DEPTEMP>'||CHR(10)||'<BODY>'






<deptno>30</deptno>

<dname>SALES</dname>

<empno>7900</empno>

<ename>JAMES</ename>

<sal>950</sal>

</emp></deptemp>

</body>





14 rows selected.



SQL> spool off



"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:46:46 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US