VARCHAR2 SQL > 4000 bytes

My requirement was to produce a result set from a custom SQL (data is not from a table) which could have hundreds of rows @A@

For that, I created an SQL builder that concatenates UNION-ed SELECT statements which in the end exceeded 4000 bytes. Unfortunately VARCHAR2 only allows up until that point. The solution was to change my variable's data type to CLOB? not quite. REF CURSORs doesn't OPEN to CLOBs and TO_CHAR doesn't do the trick either TwT uhuhu

---

update:

so the final solution was to use a GLOBAL TEMPORARY TABLE

The ON COMMIT DELETE ROWS part purges the table when you issue the command COMMIT

instead of creating a mega-SQL, you'll just need to insert into a GLOBAL TEMPORARY TABLE just like above; then OPEN the REF CURSOR using a SELECT * statement FROM that Global Temporary Table ^^ pretty neat <3

All I need to do is convert this procedure into a function then return that REF CURSOR ^^/

(TBC clarify)