CREATE PROCEDURE XMLQuotesForStocksInPortfolio( id NUMBER ) IS -- Select all stocks for the user with id passed in CURSOR c_QuotesForUserid( cp_Userid NUMBER ) IS SELECT q.symbol, q.price, q.change FROM quotes q, portfolio_stocks ps WHERE q.symbol = ps.symbol AND ps.owner = cp_Userid; BEGIN OWA_UTIL.MIME_HEADER('text/xml'); HTP.P('<?xml version="1.0"?>'); HTP.P('<Quotes>'); FOR curQuote IN c_QuotesForUserid( id ) LOOP HTP.P('<Quote>'); HTP.P('<Symbol>' ||curQuote.symbol||'</Symbol>'); HTP.P('<Price>' ||curQuote.price ||'</Price>'); HTP.P('<Change>' ||curQuote.change||'</Change>'); HTP.P('</Quote>'); END LOOP; HTP.P('</Quotes>'); END XMLQuotesForStocksInPortfolio; |