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/