Why "an INTO clause is expected in this SELECT statement"

Dee102 · Jan 20, 2021 4:52PM

Hello,

I have been a SQL Server developer for couple decades and recently started working on Oracle PL SQL. I have a simple query with a simple question. Maybe you can explain to me why I got that error, so I will understand the concept.Thanks.

declare p_start_dt date := '01-Jun-20';

p_end_dt date := '30-Jun-20';

BEGIN

SELECT *

FROM Table

WHERE date1 <= p_start_dt

and date2 >= p_end_dt;

END;

mathguy · Jan 20, 2021 5:15PM

SQL Developer is not your database, it's the interface you use to interact with the database. To find your Oracle db version, run

select * from v$version;

To your question: If you want to run a SELECT statement to retrieve information from your tables, just run the SELECT statement directly - don't enclose it within a PL/SQL program unit (an "anonymous block" in your case - the structure with a DECLARE section and BEGIN and END markers).

It seems you want to run a query where you give the start date and the end date as parameters. The way to do that is to include BIND VARIABLES in your query (variable names are preceded by colon : ) You can declare the variables and give them values in a PL/SQL block before running the SELECT query.

Something like this:

SQL> variable start_date varchar2(10)
SQL> begin :start_date := '2021-01-15'; end;
 2 /

PL/SQL procedure successfully completed.


SQL> select to_date(:start_date, 'yyyy-mm-dd') as start_dt from dual;

START_DT
----------
2021-01-15

1 row selected.

Keep in mind the "division of labor" here: VARIABLE is a SQL*Plus command (a command understood by your interface program). The anonymous block where you assign a value to the bind variable is a PL/SQL block (not SQL*Plus interface, but also NOT SQL). Finally, the SELECT statement is a SQL statement. It helps to keep your thoughts organized, to understand which task is performed by which component of your complex setup.

Note that when you declare the variable (with the VARIABLE command) you are not using the colon before the variable name. You do use the colon everywhere else.

Can you adapt this to your example? Give it a try! Note: This is how it works in SQL*Plus; in SQL Developer you can just enter the SELECT statement, with the bind variables; when you execute it, SQL Developer will ask you for values for the bind variable(s). Also, while some systems support the DATE data type for bind variables, others don't; SQL*Plus doesn't, so I used VARCHAR2 and converted to a date in the query itself.

EDIT: I didn't explain the error message. In PL/SQL you can't run standard (SQL) SELECT statements. You can run SELECT statements that return values to PL/SQL variables (either scalar variables or collections, such as arrays of records, etc.) If you have a SELECT statement in a PL/SQL block, you must specify the variable(s) INTO which the values are to be returned. By the look of your code, I assumed you weren't trying to write PL/SQL code that does something, but instead you just wanted to run a SELECT statement that returns results depending on two runtime variables used in the WHERE clause. If I guessed wrong, say so.

Dear DBA Frank · Jan 20, 2021 4:57PM

Hello

I'm afraid you did not include your error. Also always mention the database edition and version (example: Oracle EE 12.2) please.

Dee102 · Jan 20, 2021 4:59PM

Oracle SQL Developer 19.2.1.247

PLS-00428: an INTO clause is expected in this SELECT statement

06550. 00000 - "line %s, column %s:\n%s"

*Cause: Usually a PL/SQL compilation error.

Dear DBA Frank · Jan 20, 2021 5:06PM

It seems to be a syntax error. I'm not sure what you're trying to do, whether your SQL code is meant to retrieve some data for your PL/SQL code or if you mean to EXECUTE some SQL, but look at this example that showcases both:

DECLARE

c INT;

BEGIN

SELECT COUNT(*) INTO c FROM USER_CONSTRAINTS WHERE constraint_name = 'FK_LRE_COM'; -- here i'm putting the result of count(*) into the "c" variable

IF c = 1 THEN

dbms_output.put_line('DROP FK FK_LRE_COM');

EXECUTE immediate 'ALTER TABLE SRM_LIEU_REALISATION DROP CONSTRAINT FK_LRE_COM'; -- here I'm executing some SQL code inside this PL/SQL block

END IF;

END;

/

Dee102 · Jan 20, 2021 5:09PM

But I didn't want to insert into a variable. I just want to select the data for that date ranges.

mathguy · Jan 20, 2021 5:15PM

SQL Developer is not your database, it's the interface you use to interact with the database. To find your Oracle db version, run

select * from v$version;

To your question: If you want to run a SELECT statement to retrieve information from your tables, just run the SELECT statement directly - don't enclose it within a PL/SQL program unit (an "anonymous block" in your case - the structure with a DECLARE section and BEGIN and END markers).

It seems you want to run a query where you give the start date and the end date as parameters. The way to do that is to include BIND VARIABLES in your query (variable names are preceded by colon : ) You can declare the variables and give them values in a PL/SQL block before running the SELECT query.

Something like this:

SQL> variable start_date varchar2(10)
SQL> begin :start_date := '2021-01-15'; end;
 2 /

PL/SQL procedure successfully completed.


SQL> select to_date(:start_date, 'yyyy-mm-dd') as start_dt from dual;

START_DT
----------
2021-01-15

1 row selected.

Keep in mind the "division of labor" here: VARIABLE is a SQL*Plus command (a command understood by your interface program). The anonymous block where you assign a value to the bind variable is a PL/SQL block (not SQL*Plus interface, but also NOT SQL). Finally, the SELECT statement is a SQL statement. It helps to keep your thoughts organized, to understand which task is performed by which component of your complex setup.

Note that when you declare the variable (with the VARIABLE command) you are not using the colon before the variable name. You do use the colon everywhere else.

Can you adapt this to your example? Give it a try! Note: This is how it works in SQL*Plus; in SQL Developer you can just enter the SELECT statement, with the bind variables; when you execute it, SQL Developer will ask you for values for the bind variable(s). Also, while some systems support the DATE data type for bind variables, others don't; SQL*Plus doesn't, so I used VARCHAR2 and converted to a date in the query itself.

EDIT: I didn't explain the error message. In PL/SQL you can't run standard (SQL) SELECT statements. You can run SELECT statements that return values to PL/SQL variables (either scalar variables or collections, such as arrays of records, etc.) If you have a SELECT statement in a PL/SQL block, you must specify the variable(s) INTO which the values are to be returned. By the look of your code, I assumed you weren't trying to write PL/SQL code that does something, but instead you just wanted to run a SELECT statement that returns results depending on two runtime variables used in the WHERE clause. If I guessed wrong, say so.

Dee102 · Jan 20, 2021 6:58PM

Thank you. I am still absorbing.

mathguy · Jan 20, 2021 7:21PM

Odd thing...

When I posted my reply (and even when I edited it several minutes later), only the first two replies were visible. I just reported this as a platform bug: there were additional replies, where the original poster clarified that he just wanted a standard SELECT statement with variables in the WITH clause... I can now see all the replies, and in that context, one might ask why I said some things in my Reply. The reason is that those additional replies, which were earlier than mine, weren't visible to me when I wrote mine (including when I wrote the EDIT).

Just hoping that this can be addressed (by the forum owners), since it's quite annoying.

L. Fernigrini · Jan 20, 2021 7:28PM

Since Oracle 12.1, it is possible to simply "return" a resultset in an stored procedure (like SQL Server does) .

You may take a look at this article:

https://oracle-base.com/articles/12c/implicit-statement-results-12cr1

The section on RETURN_RESULT will explain how to just return the values you want without inserting them into variables using INTO

Dee102 · Jan 20, 2021 9:46PM

Good article. Thanks.

However, it brings up my another question. Not sure it should be discussed here or I should start a new discussion.

Why Oracle has so many examples using cursor? Isn't it slow? Fetching cursor is slow with larger dataset in SQL Server. I would loop through a temp table instead that speed up the process a lot.

mathguy · Jan 20, 2021 10:13PM

I don't know SQL Server and such - all I know is Oracle. (That's because I don't work in IT.)

In Oracle, in the vast majority of cases, if all you need is the result set of a query (even using "parameters" - bind variables - and such), you would just write a SELECT query; you wouldn't write PL/SQL code at all, unless your interface needs to use PL/SQL to assign values to bind variables (as I did in my example, where this was indeed needed - that is the only way to assign values to bind variables in SQL*Plus).

You may have seen a very large number of examples of using cursors and who knows what else... Many of those were bad examples (poor practices), things people were doing in PL/SQL that they should have done in plain SQL. I don't know if this applies to SQL Server also, but in Oracle a very common saying is "if you can do it in plain SQL, then do it in plain SQL; only use PL/SQL if you absolutely have to". It is possible that Oracle - compared to other db vendors - has many more SQL extensions written directly into the language, making the use of imperative programming (in PL/SQL or similar) far less necessary than it may be in other db environments / SQL dialects.

L. Fernigrini · Jan 20, 2021 11:39PM

https://community.oracle.com/tech/developers/discussion/comment/16782092#Comment_16782092

Every time you query data you have a cursor. A simple SELECT creates an implicit cursor.

You can also define explicit cursor, either declaring them (and you need to open, loop, fetch, close - very similar to SQL Server) with the FOR xx IN (SELECT) syntax that automatically opens, loops, fetches and closes it.... (way more simple than SQL Server).

Usually, performance problems exists when you process a cursor row by row, rather than doing things in a SET.

For example, a procedure that declares an explicit cursor, opens it, loop through it and updates the column A setting the value to 123 will probably be slower than a simple UPDATE ... SET A=123 WHERE <<same conditions as explicit cursor>>. Both programs wil use a cursos, but one will process rows one at a time and the other all of them in a SET.

Oracle also allows you to use a cursor using ROW by ROW logic but actually executing in batches (using BULK COLLECT, etc) that is a good way of doing things in a row by row way but optimizing resources, and not penalizing performance so much..... Thing about it as a intermediate solution, with the flexibility of row by row processing but with less performance impact.

As a general rule of thumb, in Oracle is usually beter to do things in SQL, than in PL/SQL, but if you need to use PL/SQL is better to use bulk collect tthan process row by row. There are exceptions, obviously, but most of the times that won't fail.

Forum Stats

Howdy, Stranger!

Quick Links

Categories

Why "an INTO clause is expected in this SELECT statement"

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Categories

Why "an INTO clause is expected in this SELECT statement"

Best Answer

Answers