Forum Stats

  • 3,751,226 Users
  • 2,250,333 Discussions
  • 7,867,348 Comments

Discussions

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

Dee102
Dee102 Member Posts: 42 Green Ribbon

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

«1

Answers

  • Dear DBA Frank
    Dear DBA Frank Member Posts: 166 Bronze Badge

    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
    Dee102 Member Posts: 42 Green Ribbon
    edited Jan 20, 2021 5:00PM

    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
    Dear DBA Frank Member Posts: 166 Bronze Badge

    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
  • Dee102
    Dee102 Member Posts: 42 Green Ribbon

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

  • Dee102
    Dee102 Member Posts: 42 Green Ribbon

    Thank you. I am still absorbing.

  • mathguy
    mathguy Member Posts: 9,982 Gold Crown

    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
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,550 Bronze Crown

    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

  • Dee102
    Dee102 Member Posts: 42 Green Ribbon

    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
    mathguy Member Posts: 9,982 Gold Crown

    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.

    Dee102