Oracle: Declare multiple constants and use them in WHERE clause

Question

I perform daily extractions from the same table. The only thing that ever changes is the range of a column called SAMPLE_ID, so I would like to assign a fixed value to two variables: id_min and id_max so that I can update their values before running the query.

I tried the following:

DECLARE
    id_min  CONSTANT INTEGER := 17778;
    id_max  CONSTANT INTEGER := 17803;
BEGIN
    SELECT
        *
    FROM
        MDB.SCORES
    WHERE
        SAMPLE_ID BETWEEN id_min AND id_max;
END;

I expected this code to use the numbers assigned to id_min and id_max as the lower and upper bounds of the BETWEEN function in the WHERE clause, but I'm getting this message:

ORA-06550: line 6, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement

It seems someone ran into something similar in this thread, but I do not understand the solutions provided.

You need to store the result of the select somewhere. You can't just write a SELECT statement like that in PL/SQL. See e.g. here: https://stackoverflow.com/questions/34520375/how-do-i-return-a-set-of-results-from-a-query-window-in-oracle-sql-developer/34522760#34522760 — , Jan 08 '20 at 15:14
Oh, I see. So PL/SQL is expecting me to write the query result into a table? — Arturo Sbr, Jan 08 '20 at 15:16
PL/SQL is a programming language, not a query language. It’s expecting you to process the results somehow. It can’t just process an implicit cursor and not do anything with it. — William Robertson, Jan 09 '20 at 23:03

Ergi Nushi · Answer 1 · 2020-01-08T15:30:33.607

Since you are running a PL/SQL code, you cannot just run a SELECT query without doing something with it.

There are some ways on dealing with select queries in PL/SQL.

1) INTO clause, where you take the row returned from the query and assign them to variables. *Notice is said row, not rows. If your query returns more then one row you cannot use INTO clause since it will generate TO_MANY_ROWS exception.

DECLARE
    id_min  CONSTANT INTEGER := 17778;
    id_max  CONSTANT INTEGER := 17803;
    variable1 your_variable_type;
    variable2 your_variable_type;
    variable3 your_variable_type;
BEGIN
    SELECT
        *
    INTO
        variable1,
        variable2,
        variable3
    FROM
        MDB.SCORES
    WHERE
        SAMPLE_ID BETWEEN id_min AND id_max;

    ---- DO SOMETHING WITH YOUR VARIABLES ----    
END;

2) FOR loop if you have one or many rows.

DECLARE
    id_min  CONSTANT INTEGER := 17778;
    id_max  CONSTANT INTEGER := 17803;
BEGIN

    FOR c IN (
        SELECT
            *
        FROM
            MDB.SCORES
        WHERE
            SAMPLE_ID BETWEEN id_min AND id_max
    ) LOOP
        ---- DO SOMETHING WITH YOUR VARIABLES ----  
        ---- ACCESS A COLUMN ----

        c.column_name;

    END LOOP;
END;

3) CURSOR declaring.

DECLARE
    id_min  CONSTANT INTEGER := 17778;
    id_max  CONSTANT INTEGER := 17803;
    v_cursor SYS_REFCURSOR;
    your_variables
    ...
BEGIN

    OPEN v_cursor FOR
        SELECT
            *
        FROM
            MDB.SCORES
        WHERE
            SAMPLE_ID BETWEEN id_min AND id_max;
    LOOP
        FETCH v_cursor INTO your_variables;
        EXIT WHEN v_cursor%NOTFOUND;

        ---- DO SOMETHING WITH YOUR VARIABLES ----  

    END LOOP;
END;

For option 3 "cursor declaring", a good "do something with your variables" might be `DBMS_SQL.RETURN_RESULT(v_cursor)`, since the OP seems to be trying to run the PL/SQL block as though it were a query. — Matthew McPeak, Jan 08 '20 at 20:48

score 1 · Answer 2 · answered Jan 08 '20 at 15:14

Once you are between BEGIN and END you are no longer in SQL, but in PLSQL.

In PL/SQL there is no just select statement, there is a "select ... INTO ..." statement instead.

In other words, the database wants from you a variable to save the results to.

Or you might use a loop in order to operate with data. Below is the loop example to print the data from some_column out

DECLARE
     id_min  CONSTANT INTEGER := 17778;
    id_max  CONSTANT INTEGER := 17803;
BEGIN
  for score in (SELECT *
                  FROM MDB.SCORES
                 WHERE SAMPLE_ID BETWEEN id_min AND id_max) loop
    dbms_output.put_line(sc.some_column_name);
  end loop;
END;

score 1 · Answer 3 · answered Jan 08 '20 at 16:05

You might find it simpler to use bind variables, and not run your query in PL/SQL at all.

var id_min NUMBER
var id_max NUMBER

begin
  :id_min := 17778;
  :id_max := 17803;
end;
/

SELECT
    *
FROM
    MDB.SCORES
WHERE
    SAMPLE_ID BETWEEN :id_min AND :id_max;

score 0 · Accepted Answer · answered Jan 17 '20 at 15:32

0

I did not realize DECLARE would require PL/SQL. I found it easier to do the following using DEFINE.

DEFINE id_min = 17778
DEFINE id_max = 17803;

SELECT
    *
FROM
    MDB.SCORES
WHERE
    SAMPLE_ID BETWEEN &id_min AND &id_max;

answered Jan 17 '20 at 15:32

Arturo Sbr

5,567
4
38
76

Oracle: Declare multiple constants and use them in WHERE clause

4 Answers4