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

Question

I am wanting to store a Rownum as a variable rather than use a costly Join. I need to get this from a Select statement as the Rownum will be different on various environments so it cannot be a literal string in the code.

For context, this query is executed on Oracle Siebel CRM schema and it retrieves some products of specific type and attributes.

I tried using the following SQL code in Toad and Oracle SQL Developer, however I am getting the following error:

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

Here is the code

    DECLARE     PROD_ROW_ID varchar(10) := NULL; 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'))         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; / 

Answer 1

In PLSQL block, columns of select statements must be assigned to variables, which is not the case in SQL statements.

The second BEGIN's SQL statement doesn't have INTO clause and that caused the error.

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; / 

References

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS00601 http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/selectinto_statement.htm#CJAJAAIG http://pls-00428.ora-code.com/