Question:My workflow follows: I developed some SQL statements according to a certain logic. Then I am asked to bundle the SQL statement into PL/SQL block to help trigger/schedule the execution of said statements. At that point, I can see that PL/SQL block (despite being copy/paste of the SQL statement + passing argument) does not give the same results.
While I can
DBMS_OUTPUT.PUT_LINEthe arguments to check they are what was intended, I did not find a way to peek into what happens in the
WITHclause of the
SELECTstatement. I tried
SELECT INTOa local variable of the PL/SQL block, but it is not allowed to do
SELECT INTOif not at the outer-most SELECT (which is never going to be the case in an element of a
So the question is how to troubleshoot this type of statement?
I don’t have an MRE, I am looking for a general solution to change my workflow rather than a workaround for this case.
Note: I am fine with a high-level answer so long I could practically use it. (for instance: “never use
WITHclause in PL/SQL” would be fine). Note: I say “troubleshoot”, because I can’t debug as the DBA didn’t grant debug rights, and ETA to get debug rights granted is more than 12 months away.
Answer:If you want to see what is going on with a
WITHclause such as:
Then I am asked to bundle the SQL statement into PL/SQL block … how to troubleshoot this type of statement?
Do exactly the same thing but wrap it in a cursor or use
BULK COLLECT INTOand then loop through the cursor or collection and print the rows with
If you have better answer, please add a comment about this, thank you!