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

Yong Huang

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

Yong Huang

Hi David,

thanks for your help.

it works but the reason is different from yours.

The report type should be not pl/sql function body return, it just is sql query.

Anyway, thanks for your responding.

David Berger

Hello Yong

Of course because your function is a PL/SQL stored subprogram. In this case your select statement will be sent to the SQL-Engine and you have to give a result "pool" where you get the result back.

So, you need an INTO-Cause and an object (Variable, Record or at a Bulk-Operation a Collection etc.) after the INTO caluse where the results will be stored for example for further processing.

Docs: http://www.dba-oracle.com/t_pl_sql_plsql_select_into_clause.htm

SELECT INTO Statement

I hope it helps!

Yong Huang

Hi David,

thanks for your help.

it works but the reason is different from yours.

The report type should be not pl/sql function body return, it just is sql query.

Anyway, thanks for your responding.