PL/SQL While Loop

Question

I am a beginner in PL/SQL. I want to run simple WHILE LOOP and I am receiving errors.

Here is my code:

DECLARE
  counter INTEGER := 01;
BEGIN
  WHILE counter <= 30 
  LOOP
    SELECT name, count(iid) as Counts
    FROM table.orders
    WHERE date_inserted >= TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM')
    AND date_inserted <= TO_DATE('14-SEP-16 12.'counter'.00 PM','DD-MON-YY HH.MI.SS AM')
    HAVING count(iid) >= 60
    ORDER BY count(iid) DESC;
  END LOOP;
END;

I am getting these errors when running the code:

*Error starting at line : 1 in command -
DECLARE
  counter INTEGER := 01;
BEGIN
  WHILE counter <= 30 
  LOOP
    SELECT iname, count(iid) as Counts
    FROM table.orders
    WHERE date_inserted >= TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM')
    AND date_inserted <= TO_DATE('14-SEP-16 12.'counter'.00 PM','DD-MON-YY HH.MI.SS AM')
    HAVING count(iid) >= 60
    ORDER BY count(iid) DESC;
  END LOOP;
END;

Error report - ORA-06550: line 9, column 49:
PL/SQL: ORA-00907: missing right parenthesis
ORA-06550: line 6, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
Cause: Usually a PL/SQL compilation error.
Action:

What seems to be the problem? Thanks in advance!

Can you reformat your question so that it appears as fixed text by indenting it? It's hard to read in paragraph form. — Brad
– Brad, Commented Sep 14, 2016 at 2:43
TO_DATE('14-SEP-16 12.' + counter + '.00 PM','DD-MON-YY HH.MI.SS AM') — artm
– artm, Commented Sep 14, 2016 at 2:45
artm - will this work also... TO_DATE('14-SEP-16 12.'||TO_CHAR(counter)|| + '.00 PM','DD-MON-YY HH.MI.SS AM') — Sql Surfer
– Sql Surfer, Commented Sep 14, 2016 at 2:52
I never initialize my variables in oracle above the first BEGIN line. I do not know why I do that however. — Sql Surfer
– Sql Surfer, Commented Sep 14, 2016 at 2:53

user330315 · Accepted Answer · 2016-09-14 06:02:26Z

3

Don't concatenate, it would be more efficient (and in my opinion easier to read) if you simply add the counter to a proper date value. You also don't increment the counter and you are missing the group by in the query. And finally, the result of a query must be stored somewhere.

DECLARE
  counter INTEGER := 1;
  l_from_date DATE;
  l_name varchar(200);
  l_count integer;
BEGIN
  from_date := TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM');

  WHILE counter <= 30 
  LOOP
    SELECT name, count(iid) as Counts
       into l_name, l_count --<< store the result somewhere
    FROM table.orders 
    WHERE date_inserted >= l_from_date 
    AND date_inserted <= l_from_date + counter
    GROUP BY name --<< this was missing as well
    HAVING count(iid) >= 60
    ORDER BY count(iid) DESC;

    counter := counter + 1;  --<< increment the counter

    -- do something with l_name and l_count ....
  END LOOP;
END;
/

Unrelated to this question: but you should avoid NLS specific format strings for date values. Better use a format that doesn't depend on the language used by the SQL client (or application) running the code. e.g. to_date('2016-09-14 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

answered Sep 14, 2016 at 6:02

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

William Robertson Over a year ago

Good advice, but date '2016-09-14 is a lot less bother.

user330315 Over a year ago

@WilliamRobertson: I know and I prefer the ANSI literals myself - I just don't wanted to introduce yet another "new" thing (seeing that the OP is clearly a newcomer)

archimede Over a year ago

Sorry but AND date_inserted <= l_from_date + counter would add a whole day. I think what the OP really wants is AND date_inserted <= l_from_date + counter/1440 (1440 being the number of minutes in a day).

user330315 Over a year ago

@archimede: No idea. But I'd prefer using l_from_date + interval '1' minute * counter in that case (to make intentions clear)

SyntaxCommander · Accepted Answer · 2016-09-14 02:50:30Z

0

You would want to use CONCAT or || after converting counter to char in order to add it in between those strings.

answered Sep 14, 2016 at 2:50

SyntaxCommander

11 bronze badge

Comments

Utsav · Accepted Answer · 2016-09-14 04:07:10Z

0

In your code, I don't see you increment the counter. Is it by design??

To answer your question, as mentioned in comments, you would want to use concat for concatenating the string. Change your where condition to

WHERE date_inserted >= TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM')
AND date_inserted <= TO_DATE('14-SEP-16 12.'||to_char(counter)||'.00 PM','DD-MON-YY HH.MI.SS AM')

In fact in Oracle 11 or newer versions (may be previous too but I never worked on those), you don't need to do conversions explicitly. So you can directly concatenate counter to the string.

WHERE date_inserted >= TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM')
AND date_inserted <= TO_DATE('14-SEP-16 12.'||counter||'.00 PM','DD-MON-YY HH.MI.SS AM')

answered Sep 14, 2016 at 4:07

Utsav

8,1632 gold badges22 silver badges40 bronze badges

3 Comments

Jeffrey Kemp Over a year ago

Oracle has supported implicit conversions since many versions ago.

William Robertson Over a year ago

Oracle has always supported implicit conversions, as have all mainstream SQL vendors.

Utsav Over a year ago

that's why I explicitly mentioned (may be previous too but I never worked on those). Thanks for confirming it.

Steffen Moritz · Accepted Answer · 2020-08-08 16:35:03Z

0

You can concatenate the strings directly

WHERE date_inserted >= TO_DATE('14-SEP-16 12.00.00 AM','DD-MON-YY HH.MI.SS AM')
AND date_inserted <= TO_DATE('14-SEP-16 12.'||counter||'.00 PM','DD-MON-YY HH.MI.SS AM')

edited Aug 8, 2020 at 16:35

Steffen Moritz

7,76811 gold badges41 silver badges57 bronze badges

answered Aug 8, 2020 at 15:23

mayureshrpatil

1

Collectives™ on Stack Overflow

PL/SQL While Loop

4 Answers 4

4 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related