"Big Mac and Size"..Handling large SQL Macros

Happy 2021 everyone! And what better way to start than a cheesy pun to make you groan and already to start looking forward to a better 2022

This post is just a quick one to kick off 2021 because officially I’m still on Christmas holidays. I had a question come in regarding the cool SQL Macro features in 19c and beyond. A SQL Macro generates SQL or a SQL fragment as an output from a special PL/SQL function, and most of the demo code you will see in the documentation or on the interwebs returns a SQL macro as a varchar2.

But what if your SQL statement is really large? You might see this

SQL> create or replace
  2  function big_mac  return varchar2 sql_macro is
  3    c varchar2(32767) := 'select dummy from dual where 1=0 and 2=0 and 3=0 and 4=0';
  4  begin
  5    for i in 1 .. 1000 loop
  6      c := c || ' union all select dummy from dual where 1=0 and 2=0 and 3=0 and 4=0';
  7    end loop;
  8    return c;
  9  end;
 10  /

Function created.

SQL>
SQL> select * from big_mac();
select * from big_mac()
              *
ERROR at line 1:
ORA-62565: The SQL Macro method failed with error(s).
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "MCDONAC.BIG_MAC", line 5
ORA-06512: at line 5

At this point, there are two steps you need to perform in order to resolve this error.

The first one is to stop, sit down, grab a coffee and have a good long think about that SQL statement -because if you are regularly writing SQL statements that are longer than 32k, then this suggests perhaps a deeper issue that will ultimately cause you problems later anyway. Massive SQL statements might be OK, but they might be due to a poor design, or misusing SQL. For example, I often see huge SQL statements when people build in-list iterators with multiple batches of 1000 elements. That’s a disaster waiting to happen.

Now we’re ready to talk about the second thing. But…stop. Go back to the first one again. Take another look

OK, now we’re ready for the second one.

The second thing you can do to resolve this issue, is return a CLOB. A function that returns a CLOB can still be used as SQL Macro PL/SQL function. Here’s the same function as above, with a little extra addenda to show that we can successfully construct, execute and fetch rows from a huge SQL statement that is the output from a SQL Macro function that returns a CLOB.

SQL> create or replace
  2  function big_mac  return clob sql_macro is
  3    c clob := 'select dummy from dual where 1=0 and 2=0 and 3=0 and 4=0';
  4  begin
  5    for i in 1 .. 1000 loop
  6      c := c || ' union all select dummy from dual where 1=0 and 2=0 and 3=0 and 4=0';
  7    end loop;
  8    c := c || ' union all select dummy from dual';
  9    return c;
 10  end;
 11  /

Function created.

SQL> select * from big_mac();

D
-
X

But seriously…go back to step 1.