Forum Stats

  • 3,741,012 Users
  • 2,248,356 Discussions
  • 7,861,580 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

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

    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.

    Dee102
Sign In or Register to comment.