Select statement inside for loop

0
votes

I'm trying to put my select statement inside a for loop and after looping I want to insert the result on a table but upon my execution I bump in to this error.

ORA-06550: line 5, column 7:
PLS-00428: an INTO clause is expected in this SELECT statement

So my query goes like this. After looping I want to insert the result on a table.

BEGIN
  for cur_rec in (Select Distinct SDESCRIPTION from TB_READER where SDESCRIPTION is not null and SDESCRIPTION not Like '%IN%' and SDESCRIPTION not like '%OUT%') 
   loop

      SELECT L.NEVENTLOGIDN, LPAD (nuserid, 6, '0') nuserid, u.susername, 
    TO_CHAR (TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + ( (ndatetime) / (60 * 60 * 24)), 'YYYY-MM-DD HH24:MI:SS') 
    date_time, l.nreaderidn, r.sname, 
    CASE WHEN l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription = cur_rec.SDESCRIPTION and upper(sname) like '%' || upper('OUT') || '%') THEN 'OUT' 
    WHEN l.nreaderidn  IN (SELECT nreaderidn FROM tb_reader where sdescription = cur_rec.SDESCRIPTION and upper(sname) like '%' || upper('IN') || '%') THEN 'IN' END logtype 
   FROM TB_EVENT_LOG l, TB_READER r, TB_USER u 
   WHERE 
   l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription = cur_rec.SDESCRIPTION) 
   AND NDATETIME >= (trunc(sysdate -1) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
   AND ndatetime <= (trunc(sysdate) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
   AND l.nuserid = u.suserid 
   AND l.nreaderidn = r.nreaderidn                           
   ORDER BY 2, 4;


   end loop;

 END;
 /
2
sqloracleplsqloracle11g
Welcome to StackOverflow! One thing I personally like to do before posting a question is to try Googling the error message "PLS-00428: an INTO clause is expected in this SELECT statement" - wweicker
You can select from one table and insert into another in plain SQL (no need for anonymous procedures, no need for FOR loop or any other kind of PL/SQL code). Why are you writing a loop for this task? Are you just learning loops in PL/SQL, and this task is just for practicing loops? (Bad illustration of using FOR loops, in my opinion; the illustrations should not use what are considered as very bad practices, such as using PL/SQL and loops to insert data from one table into another.) - mathguy

2 Answers

3
votes

A BEGIN ... END; block of code is commonly referred to as an "anonymous block."

It's important to understand here that this is not just entirely vanilla SQL but rather PL/SQL code i.e. within this block you are writing procedural code (PL/SQL stands for Procedural Language for SQL).

When you execute a simple SQL query such as SELECT columns FROM table outside of an anonymous block the database engine will return the data from that query to the tool you are using to execute it. But here you are writing code (which happens to include SQL) and when it executes, where do the results of that query go? How does the program know where to send those results?

While a large chunk of PL/SQL involves writing SQL and then performing some other tasks, and in many cases it is synonymous with SQL in that many keywords and built in functions have the same names, it's worth keeping this in mind that PL/SQL and SQL are two different environments and work slightly differently.

You can see the difference in error messages: ORA-##### comes from the database engine where as PLS-##### comes from the PL/SQL engine.

Anyways, I hope this background information was helpful. The specific problem you are having is that your program does not know where to store the output of the query, so you need to define some local variables and then store the results of your SELECT statement INTO those local variables. When using a SELECT ... INTO statement you need to be sure you only ever return one row, otherwise you'll get a different error. (You can use a cursor if your statement might return more than one row, then you'd have an inner loop as well).

DECLARE
  /* apologies for the quick and dirty example here; 
   * you'd want to use better variable names and correct data types and lengths
   */
  s1 VARCHAR2(100 CHAR);
  s2 VARCHAR2(100 CHAR);
  s3 VARCHAR2(100 CHAR);
  s4 VARCHAR2(100 CHAR);
  s5 VARCHAR2(100 CHAR);
  s6 VARCHAR2(100 CHAR);
  s7 VARCHAR2(100 CHAR);
BEGIN
  for cur_rec in (Select Distinct SDESCRIPTION
                    from TB_READER 
                   where SDESCRIPTION is not null 
                     and SDESCRIPTION not Like '%IN%' 
                     and SDESCRIPTION not like '%OUT%') 
   loop

      SELECT L.NEVENTLOGIDN, 
             LPAD (nuserid, 6, '0') nuserid,
             u.susername, 
             TO_CHAR (TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + ( (ndatetime) / (60 * 60 * 24)), 'YYYY-MM-DD HH24:MI:SS') date_time, 
             l.nreaderidn, 
             r.sname, 
             CASE WHEN l.nreaderidn IN (SELECT nreaderidn 
                                          FROM tb_reader 
                                          where sdescription = cur_rec.SDESCRIPTION 
                                            and upper(sname) like '%' || upper('OUT') || '%') THEN 'OUT' 
                  WHEN l.nreaderidn IN (SELECT nreaderidn 
                                          FROM tb_reader 
                                         where sdescription = cur_rec.SDESCRIPTION 
                                           and upper(sname) like '%' || upper('IN') || '%') THEN 'IN' END logtype 
    INTO s1, s2, s3, s4, s5, s6, s7
    FROM TB_EVENT_LOG l, TB_READER r, TB_USER u 
   WHERE l.nreaderidn IN (SELECT nreaderidn 
                            FROM tb_reader 
                           where sdescription = cur_rec.SDESCRIPTION) 
     AND NDATETIME >= (trunc(sysdate -1) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
     AND ndatetime <= (trunc(sysdate) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
     AND l.nuserid = u.suserid 
     AND l.nreaderidn = r.nreaderidn                           
   ORDER BY 2, 4;


   end loop;

 END;
 /
2
votes

When you use a singleton SELECT to retrieve data from the database you need to provide appropriate variable declarations and an INTO clause to tell the SELECT statement where to put the values it's retrieving. For example:

DECLARE
  vNeventlogidn          TB_EVENT_LOG.NEVENTLOGIDN%TYPE;
  vUserid                VARCHAR2(30);
  vSusername             TB_USER.SUSERNAME%TYPE;
  vDate_time             VARCHAR2(50);
  vNreaderidn            TB_EVENT_LOG.NREADERIDN%TYPE;
  vSname                 TB_READER.SNAME%TYPE;
  vLogtype               VARCHAR2(3);
BEGIN
  for cur_rec in (Select Distinct SDESCRIPTION
                    from TB_READER
                    where SDESCRIPTION is not null and
                          SDESCRIPTION not Like '%IN%' and
                          SDESCRIPTION not like '%OUT%') 
   loop
     SELECT L.NEVENTLOGIDN,
            LPAD (nuserid, 6, '0') nuserid,
            u.susername, 
            TO_CHAR (TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + ( (ndatetime) / (60 * 60 * 24)), 'YYYY-MM-DD HH24:MI:SS') date_time,
            l.nreaderidn,
            r.sname, 
            CASE
              WHEN l.nreaderidn IN (SELECT nreaderidn
                                      FROM tb_reader
                                      where sdescription = cur_rec.SDESCRIPTION and 
                                            upper(sname) like '%' || upper('OUT') || '%')
                THEN 'OUT' 
              WHEN l.nreaderidn IN (SELECT nreaderidn
                                      FROM tb_reader
                                      where sdescription = cur_rec.SDESCRIPTION and
                                            upper(sname) like '%' || upper('IN') || '%')
                THEN 'IN'
            END logtype 
       INTO vNeventlogidn,
            vUserid,
            vSusername,
            vDate_time,
            vNreaderidn,
            vSname,
            vLogtype
       FROM TB_EVENT_LOG l,
            TB_READER r,
            TB_USER u 
       WHERE l.nreaderidn IN (SELECT nreaderidn
                                FROM tb_reader
                                where sdescription = cur_rec.SDESCRIPTION) AND
             NDATETIME >= (trunc(sysdate -1) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 AND
             ndatetime <= (trunc(sysdate) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 AND
             l.nuserid = u.suserid AND
             l.nreaderidn = r.nreaderidn                           
       ORDER BY 2, 4;
   end loop;
 END;