Resolved: How to troubleshoot PL/SQL block that contains WITH clauses?

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_LINE the arguments to check they are what was intended, I did not find a way to peek into what happens in the WITH clause of the SELECT statement. I tried SELECT INTO a local variable of the PL/SQL block, but it is not allowed to do SELECT INTO if not at the outer-most SELECT (which is never going to be the case in an element of a WITH clause).
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 WITH clause 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 WITH clause such as:

WITH sqfc1 (a, b, c, d, e) AS (
  SELECT a, b, c, d, e
  FROM   table_name
  WHERE  f = 'something'
),
sqfc2 (a, b, c, d, m) AS (
  SELECT a, b, c, d, 2 * e + d
  FROM   sqfc1
  WHERE  a > 0 OR b > 0
)
SELECT a, d, m, b + c AS n
FROM   sqfc2
WHERE  m > 3 AND d > 0;

and you want to see what is going on in the first sub-query factoring clause then just repeat the SQL statement and stop after the first clause:

WITH sqfc1 (a, b, c, d, e) AS (
  SELECT a, b, c, d, e
  FROM   table_name
  WHERE  f = 'something'
)
SELECT *
FROM   sqfc1

And you will see what is going on.

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 INTO and then loop through the cursor or collection and print the rows with DBMS_OUTPUT.

BEGIN
  FOR r IN (
    WITH sqfc1 (a, b, c, d, e) AS (
      SELECT a, b, c, d, e
      FROM   table_name
      WHERE  f = 'something'
    )
    SELECT *
    FROM   sqfc1
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(r.a || ', ' || r.b || ', ' || r.c || ', ' || r.d || ', ' || r.e);
  END LOOP;
END;
/

db<>fiddle here

If you have better answer, please add a comment about this, thank you!

Resolved: How to troubleshoot PL/SQL block that contains WITH clauses?

Resolved: Why when i change manualy the url on my react page it ignores the .css file?

Resolved: navigate and redirect not redirecting to path in react router

Resolved: Time Complexity: Find the number of people who know a secret leetcode 2327

Leave A Reply