OAC - Dataset WITH syntax in sql not supported

Question

Hello,

Do you have any idea why WITH Sql syntax is not accepted in OAC Sql based Dataset?

There is a plan to make evolve this and accept it in the futures OAC versions ?

Thanks,

Daniel

Daniel Vornicu · Answer

Hi,

Thanks, @GayathriAnand-Oracle for the explanation.

Best regards,

Daniel

GayathriAnand-Oracle · Answer

Hi

Using WITH clause in SQL syntax for dataset is not supported. This is because OAC wraps the user's SQL query inside another query to limit the number of rows fetched from the DB, but the Oracle DB does not support WITH clauses nested inside of other select statements( It will give error "ORA-32034: unsupported use of WITH clause" )

To use queries containing a WITH clause, one option is to first create a view based on the WITH clause query and then using the view to create the data set in OAC.

For example,

create or replace mytable_v as

with d1 as (select * from mytable) select * from d1

Then the mytable_v view can be used to create a data set in OAC.

Thanks

Gayathri