Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement?

Something like

WITH T AS
(
  SELECT * FROM Tbl
)
BEGIN
  OPEN P_OUTCURSOR FOR
    SELECT * FROM T;

  SELECT COUNT(*) INTO P_OUTCOUNT FROM T;
END;

I want to select some data and the count of it...

You can only have one statement after the CTE. You can, however, define subsequent CTEs based on a previous one:

WITH t1 AS (
   SELECT a, b, c
   FROM table1
)
, t2 AS (
   SELECT b
   FROM t1
   WHERE a = 5
)
SELECT *
FROM t2;

Given that you are trying to count the rows and populate a ref cursor from the same result set, it may be more appropriate to do one of the following:

• create a view

• stage temporary results in a temp table

Finally, if the query is simple enough, just write it once for the count and again for the cursor. Simplicity and readability trump the DRY principle in this case.

StackExchange Database Administrators Q#6507, answer score: 17