#SQLmacros

Still new for many users and rarely used, but no longer on the new features list of #Oracle23c. Talking again about #SQLmacros at #ittage2023 today to remind of this valuable Feature

Already available since @OracleDatabase 21c, but still new for many people in the audience: SQL Macros, explained by @Andrej_SQL at #DOAG2023

SQL Macros used in Oracle Apex { by @oraclexpert } from @hashnode #sql #oracle #sqlmacros #apex #oracleapex

Thanks @DOAGeV and organizing team for this awesome 10th #apexconn24 conference. And thanks to all attendies for your attention and feedback. I hope @josifabr and I could convince a few more people to check out #sqlmacros

I wanted to test the backport of #SQLmacros to #OracleDatabase 19.6 and had to build the patched #Oracle #Docker image. Here are some impressions:

Want to see the final SQL after applying all your #SQLmacros? Also for scalar macros? New blog post:

Day 2 session 2 of #ausougConnect21 with @ChrisRSaxon and #sqlMacros We are LIVE now but registrations are still opened #oracle #sql #plsql

One of my New Year's Resolutions is to start writing not only about #SQLmacros. Failed today.😀But 363 attempts are still left 😅 New blog post: What else can you see in the #CBO trace about #SQLmacro expansion?

The second part is all about using COLUMNS pseudo-operator with #SQLmacros. Did you know that you can also pass data types along with the column names? Or use it with scalar #SQLmacros? #OracleDatabase @oracleace

Where can scalar #SQLmacros appear? Apparently more often than you might think. Can the #SQLmacros be nested? - Actually no, but somehow yes... @oracleace New blog post:

The last blog post about #SQLmacros, well this year 🙃 Using UNPIVOT and GROUP BY to find out what columns are different in almost identical rows. Of course, as a SQL macro 😀

New blog post: When using views or subqueries as input for #PTF or table #SQLmacros, you might hit ORA-62558. What is the reason and how to work around it?

As the documentation says, the scalar SQL macros should also work in the GROUP BY clause. But I can't get it to work. Here is the simplest example. Am I doing something wrong or is it a bug? Thanks #Oracle23c #sqlmacros @ChrisRSaxon @connor_mc_d @StewAshton

A second post today - finding frequency-based ranges for various tables (e.g., user_scheduler_job_run_details) with #PatterMatching and #SQLMacros @OracleDatabase #orcldb #21c #SQL @oracleace #ACED

Join me for my virtual session at #UKOUGConf20 on Wednesday 2 December 14:00-14:45. I’ll be presenting on #SQLmacros: @UKOUG

Thanks @DOAGeV for accepting my talk for the virtual conference #DOAG2020. I will talk about #SQLMacros on Tuesday, November 17 at 16:00.

Thank you @DOAGeV for accepting my talk about #SQLmacros for the #apexconn21! Looking forward to "see you there"

My experience asking #ChatGPT about #SQLmacros in #OracleDatabase.😀For now I would still stick to using google, oracle docs, asktom and Co., at least for the topics that are new to me. How fast will it get better, what do you think?

I was very disappointed during the weekend because none of my abstracts for #DOAG2020 was accepted 😥 It seems that @DOAGeV did not receive my submissions. Fortunately, it is cleared now. Thanks #DOAGOffice for the flexibility 😊 I will talks about #SQL extensions in #Oracle20c

@dani_schnider @DOAGeV Same story with my submission but with a bit less luck: now I'm on a waiting list with my talk about #SQLMacros Using a table SQL macro function, you can just add a record to whatever table you're querying. Exactly what I need now😀

Returning an aggregate or analytical function from scalar SQL macro doesn't seem to work: ORA-00918. Is this a limitation or bug? Haven't found anything in the documentation. @connor_mc_d @ChrisRSaxon

@Andrej_SQL @connor_mc_d @ChrisRSaxon Maybe it depends on what one thinks a "scalar" is? A scalar subquery works fine: create or replace function f return varchar2 sql_macro(scalar) is begin return '(select count(*) from emp)'; end f; / select f() from dual;

@StewAshton @connor_mc_d @ChrisRSaxon I see no reason to restrict to scalar queries. Moreover, the use of the scalar #SQLmacros in HAVING clause is explicitly documented and it is especially there that aggregates will make sense...

@ChrisRSaxon and @sfonplsql had a great time yesterday on #Feuertips 24, as Chris demoed Oracle's new #SQLmacros feature. See the recording and check out the reference links too! #PLSQL