Forum Stats

  • 3,854,954 Users
  • 2,264,439 Discussions
  • 7,905,846 Comments

Discussions

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

Yong Huang
Yong Huang Member Posts: 109
edited Feb 16, 2014 11:56PM in APEX Discussions

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
Tagged:

Best Answer

Answers

This discussion has been closed.