Using oracle nested tables or collections in SAP BI Web Intelligence FHSql

[Former Member]

I am trying to use nested table (or any collection type) in SAP Webi Freehand SQL but have not been able to find a way.

Below queries are validated successfully but no result object is returned, and I am unable to run the query.

Questions:

1. Can I use collection types in FHSql (I know I can write stored procedure with cursor reference and execute in FHSql, but I don't have access to create sps)?
2. In general, can I run query other than simple select, insert, etc. statement in FHSql?

Note: I know I can use WITH statements but it is limited, and I want flexibility to use cursor, collection, etc. for advance report writing. Creating Stored procedure is not an option as stated above.

QUERY1:

DECLARE TYPE tblType IS TABLE OF Person%RowType; tbl tblType; BEGIN

SELECT *
BULK COLLECT INTO tbl FROM DBTable WHERE rownum<100;

END;

Query2:

DECLARE rows SYS_REFCURSOR;

BEGIN

OPEN rows FOR

SELECT *
FROM DBTable WHERE rownum<100;

END;