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.

Answer 1

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.

Answer 2

That isn't anything to do with your parameter, it is because you're executing your code as a procedural block of code so it doesn't allow you to select to nothing.

What do you want to do with the result of the query? Display it to the screen? If so, select it to a cursor, iterate through and use dbms_output.