Forum Stats

  • 3,768,302 Users
  • 2,252,772 Discussions
  • 7,874,521 Comments

Discussions

Using CTE in Stored Procedure

User_J7CQL
User_J7CQL Member Posts: 2
edited Jan 28, 2021 11:03AM in SQL & PL/SQL

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.

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    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
    mathguy Member Posts: 10,154 Blue Diamond

    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!