Multiple operations using WITH

Question

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...

Answer 1

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.

Answer 2

No, a CTE or with clause is defined within the scope of a single statement

Sometimes you can do more than you might expect with a single statement though, eg:

with w as (select v from t3)
insert all into t1(v) values(v)
           into t2(v) values(v)
select v from w;

The 'normal' Oracle way to store temporary result sets (if you have to) is to use a GTT:
GLOBAL TEMPORARY table.