WITH clause behavior in BEGIN END block PL/SQL

Asked Jun 13 '18 at 07:27

Active Jun 13 '18 at 09:32

Viewed 134 times

When I run the following it executes just fine

WITH temp AS ( SELECT owner, table_name FROM all_tables )
SELECT * FROM temp;

When I then add a BEGIN-END block it does not work anymore

BEGIN
    WITH temp AS ( SELECT owner, table_name FROM all_tables )
    SELECT * FROM temp;
END;

The error message is:

an INTO clause is expected in this SELECT statement

Can anyone explain what is going on here and why the WITH statement needs to be amended?

edited Jun 13 '18 at 09:32

asked Jun 13 '18 at 07:27

Simon

3

It's not the `WITH` clause. You would get this self-explanatory error for any `select` without an `into` clause. – William Robertson Jun 13 '18 at 07:30
4

When you run `SQL` statement under the scope of `PLSQL` engine, you need variables to hold the result of select statement and you capture those using `INTO` clause. – XING Jun 13 '18 at 07:33

0 Answers0