Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

4 Answers 4

up vote 6 down vote accepted

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.

share|improve this answer
    
I need to select all values and put in P_OUTCUR (ref cursor) and get the count and set P_OUTCOUNT (number) –  BrunoLM Oct 4 '11 at 17:53
    
@BrunoLM - You won't be able to both store the count and populate the ref cursor using a single statement (CTE or not). If the query is simple enough I would just write it twice. Your other options are the view and temp table. –  Nick Chammas Oct 4 '11 at 17:59
    
@Bruno can you update the question with a few more details of your pl/sql code? –  Jack Douglas Oct 4 '11 at 18:01

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

share|improve this answer

Yes, put the results of the WITH statement in a table variable or temp table.

EXAMPLE:

;WITH T AS
(
  SELECT * FROM Tbl
)

SELECT * INTO #Tmp FROM T
share|improve this answer
    
Can you give me an example? –  BrunoLM Oct 4 '11 at 17:38
    
@BrunoLM, edited for example –  Wil Oct 4 '11 at 17:40
    
I get 00911. 00000 - "invalid character" on #Tmp. I am running on Oracle SQL Developer 3.0.04... –  BrunoLM Oct 4 '11 at 17:52
    
you'll a CTAS do what you are trying to do on Oracle - eg create table tmp as with t as (... –  Jack Douglas Oct 4 '11 at 17:53
    
Arg, I'm dumb...didn't see the Oracle tag. I'm sure that @JackDouglas answer is more on the spot. –  Wil Oct 4 '11 at 17:53

WITH is essentially a view

define a view

share|improve this answer
    
unless the intent is to avoid materializing the 'view' twice for performance reasons –  Jack Douglas Oct 4 '11 at 17:58
    
then run it to a temp table. Save yourself all the headache. –  jcolebrand Oct 4 '11 at 18:13
2  
@jcole that's a SQL Server answer to an Oracle problem :) I use temp tables extremely rarely and then almost always GTTs –  Jack Douglas Oct 4 '11 at 18:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.