CODEWITHSUNDEEP
oracle-databaseplsql
Arturo Sbr
Arturo Sbr

Reputation: 6333

Oracle: Declare multiple constants and use them in WHERE clause

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.

Upvotes: 0

Views: 1869

Answers (4)

Arturo Sbr
Arturo Sbr

Reputation: 6333

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;

Upvotes: 0

kfinity
kfinity

Reputation: 9091

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;

Upvotes: 1

Ergi Nushi
Ergi Nushi

Reputation: 863

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;

Upvotes: 3

ekochergin
ekochergin

Reputation: 4129

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;

Upvotes: 1

Related Questions