Long dynamic Oracle query and How to debug it

I have a stored procedure that, I am currently working on which uses dynamic sql query.

It has one variable assigned to (5000 - 6000 characters of dynamic SQL) and another variable assigned to (5000 - 6000 characters of dynamic SQL) and a UNION ALL between both the variables.

I got the dynamic query from the procedure and ran it on the editor. It returned me a row of data. How can I debug this query?

Can I do:

var1 = "dyamic SQL..." 
UNION ALL 
var2 = "dynamic SQL..."

Any advice to help me debug this would b开发者_Python百科e very much appreciated.

You can run a dynamic query (if that is the term), but not in this way. You can execute a query string using

EXECUTE IMMEDIATE 'sqlstring'

or using

OPEN c FOR 'sqlstring'

and there is a package too that lets you execute a query with bound parameters.

But all these require that the query is a single string, so I'd suggest you put the two queries together in a third variable ...

var3 = var1 || ' UNION ALL ' || var2;

.. and then execute var3.