CODEWITHSUNDEEP
oracle-databaseplsqloracle11gbi-publisher
Sagitarius
Sagitarius

Reputation: 363

BI Publisher - Can't implement cursor logic inside data model

I want to use cursor logic inside the data model's script in order to select some data from tables based on different conditions, but I can't do it from the data model's query script also I can't invoke already stored procedure from db, is there any better option to accomplish this

here are options that I have already tried but got some errors:

  1. I have tried to create procedure and then invoke it from data model as well but it is not possible to call procedure from inside data model's script and i need to reflect procedure's data into data model structure, so i belive this scenario is not good for me.
  2. I have used following solution Anonymous blocks (Procedural Call) data model in Oracle BI Publisher (Oracle Fusion):http://oracleappssolutiions.blogspot.com/2020/12/anonymous-blocks-procedural-call-data.html it is perfect when we need simple 'select' but can't run more complicated logic like this:
DECLARE
type refcursor is REF CURSOR;
xdo_cursor refcursor;
BEGIN
OPEN :xdo_cursor FOR 
   FOR c IN (select t.* from test t where t.VERSION=2) LOOP
       FOR d in (select c.* from record d where CONNECT_BY_ISLEAF = 0 start with d.id. = c.id connect  by prior d.id = c.id) loop
     select * test
       where id=d.id
     end loop;
   END LOOP; 
END;

when i trying to run this i am getting similar errors: enter image description here

Please share your thoughts/Ideas

Thanks

Upvotes: 0

Views: 939

Answers (1)

EdHayes3
EdHayes3

Reputation: 1954

  1. Try a Pipelined Table Function if you want to use a procedure to generate data.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm

  1. If you can get away with just doing select statements with unions and where clause filters, that may be the cleanest way to go.

  2. I'm not sure what the end goal is, but the grouping functionality for templates may help you. There's quite a bit of documentation in the user guides. look for for-each-group in the guide. It's used to take a single flat series of rows/elements, and then create a hierarchy of them, on the fly.

Upvotes: 1

Related Questions