Hello Sir,

I have this table function call :

select * FROM TABLE ( MOD_GENERATOR.PKG_GENERATOR_XREF.refcur_to_table_func (
MOD_GENERATOR.F_GENERATOR_XREF (trunc(sysdate)) ))

Now I have to call this 24 times every time I run this by incrementing the hour like below :

select * FROM TABLE ( MOD_GENERATOR.PKG_GENERATOR_XREF.refcur_to_table_func (
MOD_GENERATOR.F_GENERATOR_XREF (trunc(sysdate) + 1/24 ) ))

until 23/24 ...

This table function is already used in a WITH clause as part of a huge WITH caluse cluster (this is just extraneous information)

My question is :

Can I use a recursive CTE to call the above table function to pass a 'parameter' (sort of) to increment the hour 23 times ?

I did attempt it as below :

with data(r)
as
(select 1 r from dual
union all
select r+1 from data where r < 24
)
select * from (
select * FROM TABLE ( MOD_GENERATOR.PKG_GENERATOR_XREF.refcur_to_table_func (
MOD_GENERATOR.F_GENERATOR_XREF (trunc(sysdate)+ r+1/24) )
)) from data

but the value of 'r' is not recognized inside the table function.

Any advise is appreciated

Thanks

select * 
FROM TABLE( MOD_GENERATOR.PKG_GENERATOR_XREF.refcur_to_table_func (
              MOD_GENERATOR.F_GENERATOR_XREF (trunc(sysdate)+level+1/24) )))
from dual
connect by level <= 24