PLS-00428: an INTO clause is expected in this SELECT statement error occurs if the INTO clause is missing in the SELECT statement in the Oracle PL/SQL block. While creating a PL/SQL block, you forgot to include the INTO clause in the select statement.
The INTO clause is used to assign the select query result to the PL/SQL block’s local variable. Further modifications can be done with the local variables. The INTO clause is not necessary in the Oracle sql select statement since the query stops once the values are shown.
Cause
Usually a PL/SQL compilation error.
Action
INTO clause should be added
The Problem
In PL/SQL, the result of the select statement is necessary for subsequent operations. For this, the result of the select statement must be assigned to a local variable. Oracle assigns select statement values to local variables using the INTO clause in the select statement. Further manipulations can be done with the values in the local variable.
begin
select employee_id from employees where employee_id=0;
end;Error
Error starting at line : 1 in command -
begin
select employee_id from employees where employee_id=100;
end;
Error report -
ORA-06550: line 2, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:Solution 1
The INTO clause is missing in the select statement in the Oracle PL/SQL block. The INTO clause is used to assign the values from the select statement to the local variables. To add the employee id value to a local variable, use the INTO clause as shown below. This will resolve the error “PLS-00428: an INTO clause is expected in this SELECT statement”.
SET SERVEROUTPUT ON;
declare
empid varchar2(100);
begin
select employee_id into empid from employees where employee_id=100;
DBMS_OUTPUT.put_line ('empid : '||empid);
end;Output
empid : 100Solution 2
To use multiple values from the multiple column in the select statement, use the INTO clause with comma separated variables in the select statement. The order of the INTO clause variable will map the order of the select statement columns. The select statements’ values will be assigned to local variables.
SET SERVEROUTPUT ON;
declare
empid varchar2(100);
name varchar2(100);
begin
select employee_id, first_name into empid, name from employees where employee_id=100;
DBMS_OUTPUT.put_line ('empid : '||empid ||' name : '||name);
end;Output
empid : 100 name : YawinSolution 3
The column datatype mismatch will occur with the local variable if you are unaware of the data type of the table column. If the datatypes of the table column and the local variable do not match, an error will be thrown. In PL/SQL, Oracle will allow you to construct local variables with the same datatype as the table column data type.
SET SERVEROUTPUT ON;
declare
empid employees.employee_id%type;
begin
select employee_id into empid from employees where employee_id=100;
DBMS_OUTPUT.put_line ('empid : '||empid);
end;Output
empid : 100Solution 4
If the result of all table column values is needed in the local variable, you can’t create local variables for all columns. It’s tough to keep track of all the local variables in PL/SQL code. Oracle allows you to create a row type local variable that encapsulates all of the column data. The row type variable can be used as a reference to the database table selected row.
declare
emp employees%rowtype;
begin
select * into emp from employees where employee_id=100;
DBMS_OUTPUT.put_line ('empid : '||emp.employee_id);
end;Output
empid : 100