Forum Stats

  • 3,782,441 Users
  • 2,254,645 Discussions
  • 7,880,078 Comments

Discussions

Categories

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:
· Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,396 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.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,223 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!

    · Share on TwitterShare on Facebook