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