Use a declared variable in a SELECT statement

Question

I'm using Oracle 10g and need to use a variable in a where clause of a SELECT; eg.

DECLARE
v_blah NUMBER;
BEGIN

v_blah := 13;

SELECT * FROM PEOPLE p WHERE p.LuckyNumber = v_blah;

END;

but am getting an error saying

PLS-00428: an INTO clause is expected in this SELECT statement

It seems to work fine in a DELETE or INSERT statement, so I'm not sure why it wouldn't work here.

user272735 user272735 · Accepted Answer · 2011-10-27T04:41:08

The correct syntax is:

DECLARE
  v_blah NUMBER := 13;
  v_people_rec PEOPLE%ROWTYPE;
BEGIN
  SELECT * INTO v_people_rec FROM PEOPLE p WHERE p.LuckyNumber = v_blah;
END;

The select statement in PL/SQL requires a place where store the query result. In this example the place is v_people_rec variable.

The example above expects exactly one row to be returned. In other cases it will throw exceptions NO_DATA_FOUND or TOO_MANY_ROWS.