Discussions
Categories
- 197.2K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Why "an INTO clause is expected in this SELECT statement"

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;
Best Answer
-
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.
Answers
-
Hello
I'm afraid you did not include your error. Also always mention the database edition and version (example: Oracle EE 12.2) please.
-
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.
-
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;
/
-
But I didn't want to insert into a variable. I just want to select the data for that date ranges.
-
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.
-
Thank you. I am still absorbing.
-
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.
-
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:
The section on RETURN_RESULT will explain how to just return the values you want without inserting them into variables using INTO
-
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.
-
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.