{"id":1994,"date":"2025-05-27T01:10:59","date_gmt":"2025-05-27T05:10:59","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=1994"},"modified":"2025-05-22T01:16:37","modified_gmt":"2025-05-22T05:16:37","slug":"how-to-fix-pls-00428-an-into-clause-is-expected-in-this-select-statement","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/how-to-fix-pls-00428-an-into-clause-is-expected-in-this-select-statement\/","title":{"rendered":"How to Fix PLS-00428: An INTO Clause Is Expected in This SELECT Statement"},"content":{"rendered":"\n

In PL\/SQL, when you execute a SELECT<\/code> statement inside a PL\/SQL block (such as in a procedure, function, or anonymous block), you must fetch the selected data into variables<\/strong> using the INTO<\/code> clause.<\/p>\n\n\n\n

The error:<\/p>\n\n\n\n

PLS-00428: an INTO clause is expected in this SELECT statement
<\/code><\/pre>\n\n\n\n
is triggered when PL\/SQL expects you to store the result of a SELECT<\/code> query in a variable, but you forget to include the INTO<\/code> clause.<\/p>\n\n\n\n
1. Why This Error Occurs<\/strong><\/h2>\n\n\n\n
In SQL<\/strong>, you can write:<\/p>\n\n\n\n
SELECT first_name FROM employees WHERE employee_id = 100;
<\/code><\/pre>\n\n\n\n
and it simply displays the result.<\/p>\n\n\n\n
But in PL\/SQL<\/strong>, you must write:<\/p>\n\n\n\n
SELECT first_name INTO v_name FROM employees WHERE employee_id = 100;
<\/code><\/pre>\n\n\n\n
because PL\/SQL requires a target for the result \u2014 a variable to store the value.<\/p>\n\n\n\n
2. Example of the Error<\/strong><\/h2>\n\n\n\n
Code That Triggers PLS-00428<\/strong><\/h3>\n\n\n\n
BEGIN
   SELECT first_name FROM employees WHERE employee_id = 100;
END;
\/
<\/code><\/pre>\n\n\n\n
Error Output<\/strong><\/h3>\n\n\n\n
PLS-00428: an INTO clause is expected in this SELECT statement
<\/code><\/pre>\n\n\n\n
3. Correct Usage with INTO Clause<\/strong><\/h2>\n\n\n\n
Fixed Version<\/strong><\/h3>\n\n\n\n
DECLARE
   v_name employees.first_name%TYPE;
BEGIN
   SELECT first_name INTO v_name FROM employees WHERE employee_id = 100;
   DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_name);
END;
\/
<\/code><\/pre>\n\n\n\n
Output:<\/strong><\/p>\n\n\n\n
Employee name: Steven
<\/code><\/pre>\n\n\n\n
\n
The INTO<\/code> clause tells PL\/SQL to store the result of the query into the variable v_name<\/code>.<\/p>\n<\/blockquote>\n\n\n\n
4. Handling Multiple Columns<\/strong><\/h2>\n\n\n\n
If your SELECT<\/code> returns multiple columns, you need multiple variables in the INTO<\/code> clause.<\/p>\n\n\n\n
Example: Multiple Columns<\/strong><\/h3>\n\n\n\n
DECLARE
   v_fname employees.first_name%TYPE;
   v_lname employees.last_name%TYPE;
BEGIN
   SELECT first_name, last_name
   INTO v_fname, v_lname
   FROM employees
   WHERE employee_id = 100;

   DBMS_OUTPUT.PUT_LINE('Full Name: ' || v_fname || ' ' || v_lname);
END;
\/
<\/code><\/pre>\n\n\n\n
5. Using INTO with ROWTYPE<\/strong><\/h2>\n\n\n\n
If you’re selecting all columns, you can use %ROWTYPE<\/code> for cleaner code:<\/p>\n\n\n\n
DECLARE
   v_emp employees%ROWTYPE;
BEGIN
   SELECT * INTO v_emp FROM employees WHERE employee_id = 100;
   DBMS_OUTPUT.PUT_LINE('Employee Email: ' || v_emp.email);
END;
\/
<\/code><\/pre>\n\n\n\n
6. When You Don\u2019t Need an INTO Clause<\/strong><\/h2>\n\n\n\n
The only time you don\u2019t use an INTO clause<\/strong> is when you’re using:<\/p>\n\n\n\n
    \n
  • A cursor FOR loop<\/strong><\/li>\n\n\n\n
  • Dynamic SQL<\/strong> with EXECUTE IMMEDIATE<\/code><\/li>\n\n\n\n
  • SELECT INTO<\/strong> as a standalone SQL query (outside PL\/SQL)<\/li>\n<\/ul>\n\n\n\n
    Cursor FOR Loop Example<\/strong><\/h3>\n\n\n\n
    BEGIN
       FOR rec IN (SELECT employee_id, last_name FROM employees WHERE department_id = 10) LOOP
          DBMS_OUTPUT.PUT_LINE(rec.employee_id || ' - ' || rec.last_name);
       END LOOP;
    END;
    \/
    <\/code><\/pre>\n\n\n\n
    Notice that there’s no INTO clause here<\/strong> \u2014 PL\/SQL automatically handles it via the loop variable rec<\/code>.<\/p>\n\n\n\n
    7. Common Pitfalls and Fixes<\/strong><\/h2>\n\n\n\n
    Mistake<\/th>Fix<\/th><\/tr><\/thead>
    Writing SELECT ...<\/code> alone in PL\/SQL<\/td>Add INTO<\/code> clause with variable(s)<\/td><\/tr>
    Forgetting INTO<\/code> for multi-column SELECT<\/td>Include all target variables in same order<\/td><\/tr>
    Using SELECT in BEGIN...END;<\/code> block without INTO<\/td>Use cursor or dynamic SQL if needed<\/td><\/tr>
    SELECT returns more than one row<\/td>Use SELECT INTO<\/code> only when exactly one row is expected or use CURSOR<\/code>\/LOOP<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    8. Summary<\/strong><\/h2>\n\n\n\n
    PLS-00428<\/strong> is a very common beginner mistake in PL\/SQL programming. To resolve it:<\/p>\n\n\n\n
      \n
    • Always include an INTO<\/code> clause when using SELECT<\/code> inside PL\/SQL blocks.<\/li>\n\n\n\n
    • Ensure the number and type of variables in the INTO<\/code> clause match the SELECT<\/code> list.<\/li>\n\n\n\n
    • Use %TYPE<\/code> and %ROWTYPE<\/code> to ensure compatibility with column types.<\/li>\n\n\n\n
    • Use cursor loops or collections for multi-row queries.<\/li>\n<\/ul>\n\n\n\n
      Want to Avoid This Error Completely?<\/strong><\/h2>\n\n\n\n
        \n
      • Use cursor FOR<\/code> loops for queries that return multiple rows.<\/li>\n\n\n\n
      • Always test queries in SQL first, then add variables and INTO<\/code> clause in PL\/SQL.<\/li>\n\n\n\n
      • Add exception handling to capture cases where rows are missing or duplicated.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
        In PL\/SQL, when you execute a SELECT statement inside a PL\/SQL block (such as in a procedure, function, or anonymous block), you must fetch the selected data into variables using the INTO clause. The error: PLS-00428: an INTO clause is expected in this SELECT statement is triggered when PL\/SQL expects you to store the result […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[515],"tags":[529],"class_list":["post-1994","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-pl-sql"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1994","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=1994"}],"version-history":[{"count":1,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1994\/revisions"}],"predecessor-version":[{"id":1997,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1994\/revisions\/1997"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=1994"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=1994"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=1994"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}