Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XML table as PL/SQL function body return sql query error

Yong HuangFeb 16 2014 — edited Feb 16 2014

Apex 4.1

Oracle 11i

Sql code execute successfully in sql command, but show errror in classic report with pl/sql function body return sql query, please see the code:

WITH PIV_TABLE AS (


SELECT COUNTRY,xmlserialize(content HOTEL_TYPE_XML) xml FROM (


SELECT COUNTRY,HOTEL_TYPE,ROOM_NIGHTS FROM EXTRACT_HOTEL)


PIVOT XML


(SUM(ROOM_NIGHTS) NIGHTS FOR HOTEL_TYPE


IN (SELECT DISTINCT HOTEL_TYPE FROM EXTRACT_HOTEL))


)


SELECT COUNTRY,EXTRACTVALUE(XMLTYPE(XML),'/PivotSet/item[1]/column[2China_NIGHTS,


EXTRACTVALUE(XMLTYPE(XML),'/PivotSet/item[2]/column[2global_nights,


EXTRACTVALUE(XMLTYPE(XML),'/PivotSet/item[3]/column[2hkg_NIGHTS,


EXTRACTVALUE(XMLTYPE(XML),'/PivotSet/item[4]/column[2twn_NIGHTS from piv_table

error as below:

Error during rendering of region "Pivot Table".ORA-06550: 第 1 行, 第 45 列: PLS-00428: an INTO clause is expected in this SELECT statement
This post has been answered by Yong Huang on Feb 16 2014
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 16 2014
Added on Feb 16 2014
#apex-discussions, #function, #pivot, #xml
2 comments
479 views