Why am I getting the 'PLS-00428: an INTO clause is missing from this SELECT statement' error in my PL/SQL code?

"PLS-00428: INTO Clause Missing from Select Statement"

To retrieve a rownum as a variable rather than utilizing a costly join, a SELECT statement must be employed, as the rownum can vary based on the environment and cannot be represented as a static string within the code.

Unfortunately, attempting to execute this query in Toad and Oracle SQL Developer yields the following error:

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

This error stems from the fact that in PL/SQL blocks, variables must be explicitly assigned to columns in select statements. This differs from SQL statements, where column values can be directly referenced.

The issue in the provided code lies with the second BEGIN block's SQL statement, which lacks an INTO clause. This omission triggers the error. To rectify this, the code must be modified as follows:

DECLARE
   PROD_ROW_ID   VARCHAR (10) := NULL;
   VIS_ROW_ID    NUMBER;
   DSC           VARCHAR (512);
BEGIN
   SELECT ROW_ID
     INTO VIS_ROW_ID
     FROM SIEBEL.S_PROD_INT
    WHERE PART_NUM = 'S0146404';

   BEGIN
      SELECT    RTRIM (VIS.SERIAL_NUM)
             || ','
             || RTRIM (PLANID.DESC_TEXT)
             || ','
             || CASE
                   WHEN PLANID.HIGH = 'TEST123'
                   THEN
                      CASE
                         WHEN TO_DATE (PROD.START_DATE) + 30 > SYSDATE
                         THEN
                            'Y'
                         ELSE
                            'N'
                      END
                   ELSE
                      'N'
                END
             || ','
             || 'GB'
             || ','
             || RTRIM (TO_CHAR (PROD.START_DATE, 'YYYY-MM-DD'))
        INTO DSC
        FROM SIEBEL.S_LST_OF_VAL PLANID
             INNER JOIN SIEBEL.S_PROD_INT PROD
                ON PROD.PART_NUM = PLANID.VAL
             INNER JOIN SIEBEL.S_ASSET NETFLIX
                ON PROD.PROD_ID = PROD.ROW_ID
             INNER JOIN SIEBEL.S_ASSET VIS
                ON VIS.PROM_INTEG_ID = PROD.PROM_INTEG_ID
             INNER JOIN SIEBEL.S_PROD_INT VISPROD
                ON VIS.PROD_ID = VISPROD.ROW_ID
       WHERE     PLANID.TYPE = 'Test Plan'
             AND PLANID.ACTIVE_FLG = 'Y'
             AND VISPROD.PART_NUM = VIS_ROW_ID
             AND PROD.STATUS_CD = 'Active'
             AND VIS.SERIAL_NUM IS NOT NULL;
   END;
END;
/

By adding the INTO clause, the columns in the select statement are now assigned to the appropriate variables, resolving the error.

References

• [Oracle documentation on INTO clause](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS00601)
• [Oracle documentation on SELECT INTO statement](http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/selectinto_statement.htm#CJAJAAIG)
• [ORA-Code documentation on PLS-00428](http://pls-00428.ora-code.com/)

The above is the detailed content of Why am I getting the 'PLS-00428: an INTO clause is missing from this SELECT statement' error in my PL/SQL code?. For more information, please follow other related articles on the PHP Chinese website!