How to troubleshoot PL/SQL block that contains WITH clauses?-CodePudding

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.

CodePudding user response：

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