Hi Mark,
thanks a lot, nice idea regarding the regular views.
I've tried to create corresponding persisted views, but it doesn't help
really - as soon as the view is referenced in a WITH clause the error
pops up again.
So it looks like the only viable workaround is rewriting the SQLs using
conventional inline views instead of WITH clauses.
Regards,
Randolf
--
oracle-performance.de
Oracle Database Performance Troubleshooting Expert
http://www.oracle-performance.de
Phone: +49 170 758 1171
Datenschutzerklärung: http://www.oracle-performance.de/datenschutzerklaerung
Umsatzsteuer / VAT ID: DE213366879
Am 23.02.2021 um 20:26 schrieb Mark W. Farnham:
Are the "with" clauses amenable to conversion to views, and if so, does that
eliminate the error?
If the answer is "yes" and especially if there is a high fan-in of with
clauses to views, that might be an economic work-around for minimizing the
effort of changing the existing sql.
This is a not yet implemented bug, right?
By that I mean there is no inherent reason why the parser could not expand a
sql macro in a with clause, (is there?)
Is the PRAGMA UDF clause allowed with the SQL_MACRO clause, and if so, does
that fix it.
Just a few things I'd try.
mwf
-----Original Message-----
From: oaktable-bounce@xxxxxxxxxxxxx [mailto:oaktable-bounce@xxxxxxxxxxxxx]
On Behalf Of Lothar Flatz
Sent: Monday, February 22, 2021 11:12 PM
To: oaktable@xxxxxxxxxxxxx; Randolf Eberle-Geist
Subject: [oaktable] Re: ORA-64630 "unsupported use of SQL macro: use of SQL
macro inside WITH clause is not supported"
Hi Randolf,
I tried the with_plsql hint, but it does not work either.
Regards
Lothar
Am 22.02.2021 um 18:59 schrieb Randolf Eberle-Geist:
Dear Oakies,
I have a customer on 19c that wants to make use of "SQL Macros". In
principle it's working fine for their purpose and they are happy, but
they hit above limitation, which means they would need rewrite a lot
of queries making extensive use of WITH clauses if they want to move
to "SQL Macros".
I've did a quick test setting "_with_subquery" to "inline" but that
gives the same error message when using them inside a WITH clause.
Does anyone already have experience in that regard what could be a
useful workaround?
Thanks!
Randolf
