Using CTE in Stored Procedure

User_J7CQL

HI ALl,

I am trying to ceate a stored procedure where I need to make use of CTE as well. I am providing a skeleton below.

CREATE OR REPLACE PROCEDURE "MY_TEST"

BEGIN

WITH CTE1 AS

(

.........................

),

CTE2 AS

(

........ -- Using CTE1 here

),

CTE3 AS

(

..... -- Using CTE1 and CTE2 here

)

Update Statement1 --using CTE3 and CTE1

Update Statement2 --using CTE3 here

Update Statement3 --using CTE3 here

I am running into an issue with error "AN INTO CLAUSE IS EXPECTED IN SELECT". I am not sure how should I make use of INTO clause in this scenario.

Frank Kulash

Hi, @User_J7CQL

When you use SELECT in PL/SQL, you have to explicitly say where to put the results. This article

Bulk Processing with BULK COLLECT and FORALL (oracle.com)

gives an introduction to using PL/SQL collections to store the results.

mathguy

The skeleton you provided is not sufficient to understand what you are doing.

Are your CTE directly incorporated in the UPDATE statements? If they are, then you are doing it the wrong way for SQL - the mistake has nothing to do with it being in a stored procedure. If you use CTE in an UPDATE, the UPDATE clause should come first, not the CTE. More than that, we can't tell you - because you didn't show us how the CTE are used in UPDATE.

If you are not using the CTE as part of the UPDATE statement directly, then the skeleton is even less helpful, because you aren't showing us how you are using them at all. Can't help you then.

So - please put some meat on those bones!