Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 389 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
Using CTE in Stored Procedure

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