Pls-00428: an into Clause Is Expected in This Select Statement

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

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/

an INTO clause is expected in this SELECT statement when working with PLSQL

you can´t have a random select inside your pl sql code. It must be in a loop, cursor, with an into clause. Just include an into clause here.

declare 
   v_value varchar2(1);
begin
...
FOR REMS IN cur_names LOOP

   SELECT CASE   
           WHEN EXISTS (SELECT 1 
                        FROM SupplyTable 
                        WHERE FirstName = REMS.Names) 
           THEN 'Y' 
           ELSE 'N' 
       END AS rec_exists
   INTO v_value 
   FROM dual;
   IF v_value = 'Y' THEN
      FName := REMS.Names;
   ELSE
      FName := 'Gen';
   END IF;
END LOOP;
...

or a loop

FOR REMS IN cur_names LOOP

   for i in 
   (   
       SELECT CASE   
           WHEN EXISTS (SELECT 1 
                        FROM SupplyTable 
                        WHERE FirstName = REMS.Names) 
           THEN 'Y' 
           ELSE 'N' 
       END AS rec_exists
       FROM dual
   )
   loop
      IF i.rec_exists = 'Y' THEN
         FName := REMS.Names;
      ELSE
         FName := 'Gen';
     END IF;
   end loop;
END LOOP;

PLS-00428: an INTO clause is expected in this SELECT statement pl/sql oracle trigger

You are missing the INTO in the second SELECT:

SELECT * INTO ligne FROM PASSAGER WHERE IDPS = :NEW.IDPS;

Error(9,1): PLS-00428: an INTO clause is expected in this SELECT statement while creating stored procedures

i want to insert in this procedure only

You can just use the insert ... select [syntax]:

CREATE OR REPLACE PROCEDURE FIP_VALID_TBL_TRANSMEDIA 
 (
   POUTMSG OUT VARCHAR2
  )

 AS 
BEGIN

 INSERT INTO TBL_VLD_FIBERINV_DATA (SNAP_ID, MAINT_ZONE_CODE)
 SELECT TO_CHAR(sp.RJ_SPAN_ID), TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE)
 ...
 MINUS
 SELECT TO_CHAR(sp.RJ_SPAN_ID), TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE)
 ...

You don't need distinct with MINUS; and the column aliases aren't useful.

Or if you want to set the other columns, and they don't already default, you can include values for those in the query part - here using the system time and current user:

 INSERT INTO TBL_VLD_FIBERINV_DATA (SNAP_ID, MAINT_ZONE_CODE, UPDATED_DATE, UPDATED_BY)
 SELECT TO_CHAR(sp.RJ_SPAN_ID), TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE), SYSDATE, USER
 ...
 MINUS
 SELECT TO_CHAR(sp.RJ_SPAN_ID), TO_CHAR(sp.RJ_MAINTENANCE_ZONE_CODE), SYSDATE, USER
 ...

---

You need to execute the procedure for it to actually do anything; just creating or compiling it doesn't cause the code within it (i.e. the insert) to be run. You can do that from a simple anonymous block:

DECLARE
 OUTMSG VARCHAR2(4000);
BEGIN
 FIP_VALID_TBL_TRANSMEDIA (POUTMSG => OUTMSG);
END;
/

You have to declare and pass a variable to match the procedure's formal argument, even though you don't currently populate that. (Hopefully you don't intend to catch exceptions and put the exception message into that variable...)

---

It seems a bit odd to be using nvarchar2 in your target table, and to have the size as 100 even for user ID; and to have to_char() calls for what appear to already be strings. You're using the to_char(char) function which always returns varchar2, so then putting that into nvarchar2 seems strange...

---

Related Topics