Confusion on using variables & constants in PL/SQL

Question

I've new to PL/SQL (and it's been a while since I've used vanilla SQL). I've got a query that I inherited that I'm trying to schedule in TOAD. In order for that to work I have to change hard coded date references to be calculated at run time.

To that end I added a Declare statement to the front of the query, added the necessary constants, setting them at declaration, and then had the query use them.

When I try to execute an error gets thrown saying a Select Into. To my understanding, SELECT Into is used to set a variable based on a value in the db (based on Constants in Oracle SQL query), whereas I'm looking to define the value independent of any value in the db (in this case the date on the server). The full error follows:
ORA-06550: line 6, column 5: PLS-00428: an INTO clause is expected in the Select statement

So I'm looking for a little guidance on where my understanding of variables/constants in PL/SQL is off, and also help with getting the following to execute:

DECLARE OLD CONSTANT char(11):= to_Char(SYSDATE - 6, 'DD-MON-YYYY');

 NEW CONSTANT char(11):= to_char(SYSDATE, 'DD-MON-YYYY');

 BEGIN
SELECT CASE
           WHEN (userhost LIKE 'a%'
                 AND userid IN ('s',
                                'sub')) THEN 'BATCH'
           WHEN userid LIKE 'N%' THEN 'N'
           WHEN ((userhost LIKE 'b%'
                  OR userhost LIKE 'c%')
                 AND userid IN ('s',
                                'sub')) THEN 'Forms'
           WHEN ((userid LIKE '%_IU%'
                  OR userid LIKE 'RPT%'
                  OR userid IN ('q',
                                'r',
                                'p'))
                 AND userhost <> 'n%') THEN 'Interface'
           ELSE 'Other'
       END app_type , round(sum(sessioncpu/100), 1) cpu_seconds , (sum(sessioncpu/100) / (119*1*60*60) * 100) pct_of_cpu,
                                                                                                              trunc(ntimestamp#,'MI')
FROM PERFSTAT.AUD$_ARCHIVE
WHERE ntimestamp# BETWEEN to_timestamp(OLD || ' 23:59','DD-MON-YYYY HH24:MI') AND to_timestamp(NEW || ' 00:00','DD-MON-YYYY HH24:MI')
  AND logoff$time < to_date(NEW || ' 00:00','DD-MON-YYYY HH24:MI')
GROUP BY CASE
             WHEN (userhost LIKE 'a%'
                   AND userid IN ('s',
                                  'sub')) THEN 'BATCH'
             WHEN userid LIKE 'N%' THEN 'N'
             WHEN ((userhost LIKE 'b%'
                    OR userhost LIKE 'c%')
                   AND userid IN ('s',
                                  'sub')) THEN 'Forms'
             WHEN ((userid LIKE '%_IU%'
                    OR userid LIKE 'RPT%'
                    OR userid IN ('q',
                                  'r',
                                  'p'))
                   AND userhost <> 'n%') THEN 'Interface'
             ELSE 'Other'
         END app_type,
         trunc(ntimestamp#,'MI')
ORDER BY trunc(ntimestamp#,'MI'),
         1;

 END;

Answer 1

Your query has an inherent flaw, in that anything that occurs between 23:59 and 0:00 will satisfy conditions at both ends of the range (e.g. something that happens at 23:59:30). If this query were my responsibility, I'd get rid of the variables and the text conversions altogether:

WHERE        ntimestamp# >= TRUNC (SYSDATE) - 6
         AND ntimestamp# < TRUNC (SYSDATE)
         AND logoff$time < TRUNC (SYSDATE)

Using >= and < for dates where you want to avoid an overlap tends to be safer that using between.

---

Taking a closer look, I'm not sure what the point of your query using one minute before midnight on the lower bound is. That kind of thing is more typically done on the upper bound. Assuming that you're actually doing that for a reason, you can still get around transforming to a string by using either of the following:

WHERE      ntimestamp# BETWEEN TRUNC (SYSDATE) - 6 - (1 / 24 / 60)
                           AND TRUNC (SYSDATE)
       AND logoff$time < TRUNC (SYSDATE)

WHERE      ntimestamp# BETWEEN   TRUNC (SYSDATE)
                               - NUMTODSINTERVAL (6, 'DAY')
                               - NUMTODSINTERVAL (1, 'MINUTE')
                           AND TRUNC (SYSDATE)
       AND logoff$time < TRUNC (SYSDATE)

---

All of that is really just an aside to your main problem though: you need to tell the interpreter what to do with the result of the query. That means that you need to provide a variable to put the result in, then (presumably) do something with the result. One way to do this is to use a cursor loop:

DECLARE
   CURSOR cur_query IS
      [your query goes here];
BEGIN
   FOR r_query IN cur_query LOOP
      DBMS_OUTPUT.put_line (r_query.app_type);
      DBMS_OUTPUT.put_line (r_query.cpu_seconds);
      DBMS_OUTPUT.put_line (r_query.pct_of_cpu);
   END LOOP;
END;

Of course, the alternative is just to run your query as SQL, rather than PL/SQL. With the variables eliminated, that will be easier.

---

Comment Response

PL/SQL blocks are not intended to return query results, like you get if you run straight SQL in Toad. There are ways to fake it via functions that return user-defined types or pipelined functions, but you're better off writing SQL if you are able to (and, in this case, you should be able to).

I'm not sure what you mean by "the variables are supposed to dynamically set the date range to look at". The code provided is returning data relative to sysdate, not getting outside data. You can do that in the query as easily as you can in a PL/SQL block.

Answer 2

You have two issues here. The first is trying to use the CHAR datatype and then not giving it a length. This defaults to a CHAR(1), i.e. a single character. For memory concerns, you might also consider VARCHAR2 instead. https://docs.oracle.com/cd/E17952_01/refman-5.1-en/char.html

The second issue has to do with the INTO clause as mentioned in your question. When you run a SELECT statement in PL/SQL (not associated to DML), you have to give Oracle something to return the result set into. You can then use those variables, whether printing them, storing them, or doing processing with them.

Answer 3

I'd have to see the error, but I think that it may want you to set a length to your char. So, something like char(30). Also, I'm a big fan of varchar2. Only uses as much space in the DB as the characters in the variable. So, it it's varchar2(500) and has 8 characters, it only uses 8 chars worth of memory.