Avoiding "select into" error in PL/SQL when filtering a table with a variable-CodePudding

In the following minimal PL/SQL query example, I can't get the query to work, in order to filter records from a table based on a certain date, which is stored in a variable.

The goal obviously is to end up with different query results, by changing the date variable to some other moment, different from sysdate.

This query does succeed in printing the calculation date variable's content, but the select query afterwards fails with ORA-06550: line 6, column 3 (so on 'select *' below) : PLS-00428: an INTO clause is expected in this SELECT statement.

DECLARE
  v_calculation_date date := sysdate;
BEGIN
  dbms_output.put_line(v_calculation_date);
  
  select *
  from t
  WHERE to_number(to_char(trunc(t.some_date), 'YYYYMM')) 
        BETWEEN to_number(to_char(add_months(v_calculation_date,-24), 'YYYYMM')) 
        AND to_number(to_char(add_months(v_calculation_date,-1), 'YYYYMM'));
END;

OK, with a SELECT INTO then... This again prints the calculation date variable's content just fine, but the select query afterwards again fails, with the same error:

DECLARE
  v_calculation_date date;
BEGIN
  select sysdate into v_calculation_date from dual; -- dual is a dummy table with only one row and one column, just what we need here.
  dbms_output.put_line(v_calculation_date);
  
  select *
  from t
  WHERE to_number(to_char(trunc(t.some_date), 'YYYYMM')) 
        BETWEEN to_number(to_char(add_months(v_calculation_date,-24), 'YYYYMM')) 
        AND to_number(to_char(add_months(v_calculation_date,-1), 'YYYYMM'));
        
END;

I already have a SELECT INTO, why is the normal SELECT, which follows afterwards, then refused?

For the sake of completeness: this is a minimal query example for easy reproduction of the problem. My actual query is more complex, I eventually want something like this to work:

DECLARE
  v_calculation_date date;
BEGIN
  DROP TABLE t1;

  CREATE TABLE t1 as (
     WITH intermediary1 AS (
       -- The actual query from above, as a subquery
       SELECT *
       FROM t
       WHERE to_number(to_char(trunc(t.some_date), 'YYYYMM')) 
        BETWEEN to_number(to_char(add_months(v_calculation_date,-24), 'YYYYMM')) 
        AND to_number(to_char(add_months(v_calculation_date,-1), 'YYYYMM'));
    )
  )
  
  /* other drops, creates, inserts ... */

END

What is wrong in my query? Execution info: the SQL dialect is PL/SQL, the IDE is PL/SQL Developer version 13.0, the database server runs Oracle Database 12c Enterprise Edition Release 12.2.

CodePudding user response：

In PL/SQL, every SELECT (that isn't part of a cursor) must have an INTO clause. Therefore, it's not enough that one of your SELECTs has it.

As of your final code (the one that contains CREATE TABLE): that won't work. DDL can't be executed from PL/SQL, unless you use dynamic SQL (execute immediate). Therefore, all your "other drops, creates, ..." will also have to be dynamic.

Note that we usually do not create objects dynamically; create table at SQL level. Then, if you want to populate it with different data, do so (either from SQL or PL/SQL). First delete "old" data - you can use delete or truncate (but it is DDL so - as you already know by now - it requires execute immediate), and then re-populate the table.

Dynamic SQL is difficult to debug. Don't use it if you don't have to.

If you're concerned about your own data so that other users wouldn't interfere, consider creating a global temporary table (or private; depending on database version you use).

CodePudding user response：

You do not need a PL/SQL block for your query and can simplify it to:

SELECT *
FROM   t
WHERE  ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -24) <= some_date
AND    some_date < TRUNC(SYSDATE, 'MM');

If you want it in a PL/SQL block then you want to use SELECT ... INTO ... if the query is going to return a single row:

DECLARE
  v_calculation_date DATE := SYSDATE;

  v_col1      T.COL1%TYPE;
  v_col2      T.COL2%TYPE;
  v_some_date T.SOME_DATE%TYPE;
BEGIN
  SELECT col1,   col2,   some_date
  INTO   v_col1, v_col2, v_some_date
  FROM   t
  WHERE  ADD_MONTHS(TRUNC(v_calculation_date, 'MM'), -24) <= some_date
  AND    some_date < TRUNC(v_calculation_date, 'MM');

  -- Do something with the variables.
  DBMS_OUTPUT.PUT_LINE( v_col1 || ', ' || v_col2 || ', ' || v_some_date );
END;
/

If you can return multiple rows then use SELECT ... BULK COLLECT INTO ... or use a cursor:

DECLARE
  v_calculation_date DATE := SYSDATE;
BEGIN
  FOR cur IN (
    SELECT *
    FROM   t
    WHERE  ADD_MONTHS(TRUNC(v_calculation_date, 'MM'), -24) <= some_date
    AND    some_date < TRUNC(v_calculation_date, 'MM')
  )
  LOOP
    -- Do something with the cursor.
    DBMS_OUTPUT.PUT_LINE( cur.col1 || ', ' || cur.col2 || ', ' || cur.some_date );
  END LOOP;
END;
/

db<>fiddle here

Again, for your more complicated query, you do not need PL/SQL (and cannot have DDL statements in a PL/SQL context):

DROP TABLE t1;

CREATE TABLE t1 as
SELECT *
FROM   t
WHERE  ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -24) <= some_date
AND    some_date < TRUNC(SYSDATE, 'MM');

Or, if you want to use PL/SQL then do the DDL statements in the SQL context and then switch to PL/SQL for the DML statements:

DROP TABLE t1;

CREATE TABLE t1 AS
SELECT * FROM t WHERE 1 = 0;

-- or
-- TRUNCATE TABLE t1;

DECLARE
  v_calculation_date DATE := SYSDATE;
BEGIN
  INSERT INTO t1
  SELECT *
  FROM   t
  WHERE  ADD_MONTHS(TRUNC(v_calculation_date, 'MM'), -24) <= some_date
  AND    some_date < TRUNC(v_calculation_date, 'MM');
END;
/