Skip to Content
0

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

Dec 28, 2016 at 03:00 PM

24

avatar image
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;

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers