while loop for sql statement in Oracle

Question

I'm trying to loop through a set of views and gather the view name and counts. I created a new table and added an ID columns. I'd like to loop through my sql statment and retrieve the values while the ID < 10.

Here is my create table statement I'm using to set my id column;

create table  View_Table AS 
select VIEW_NAME,
row_number() over ( order by VIEW_NAME ) as id
from all_views;

--My attempt to loop through the sql statement

DECLARE
     x NUMBER := 0;
    BEGIN
      LOOP
        select view_name,count(*) from VIEW_TABLE where id = x group by VIEW_NAME;
        x := x + 1;  
      EXIT WHEN x > 10;
     END LOOP;
   END;

---Here is the error message I'm receiving:

Error starting at line : 13 in command -

DECLARE
     x NUMBER := 0;
    BEGIN
      LOOP
        select view_name,count(*) from VIEW_TABLE where id = x group by VIEW_NAME;
        x := x + 1;  
      EXIT WHEN x > 10;
     END LOOP;
   END;
Error report -
ORA-06550: line 5, column 9:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Any help would be appreciated.

Your count(*) is always going to be 1, since each view appears in your new table once (unless you have the same view in multiple schemas). Are you actually trying to count the number of rows in each view - which you would have to do with dynamic SQL? — Alex Poole
– Alex Poole, Commented Jun 6, 2015 at 10:57
I was trying to get the number of rows in each view and pass in the view name to my dynamic sql statement. — Underm1nd
– Underm1nd, Commented Jun 9, 2015 at 21:18

OldProgrammer · Accepted Answer · 2015-06-06 00:14:22Z

2

When you have a select statement in a PL/SQL block, you have to provide somewhere to store the results. Try this.

DECLARE
     x NUMBER := 0;
     cnt number;
     vname varchar2(100);

    BEGIN
      LOOP
        select view_name,count(*) 
        into vname, cnt
        from VIEW_TABLE where id = x group by VIEW_NAME;
        x := x + 1;  
      EXIT WHEN x > 10;
     END LOOP;
   END;

Is this just a learning exercise? Because you don't do anything with the results. I would recommend also reading Steve Feuerstein's articles. Lots of good stuff in there.

answered Jun 6, 2015 at 0:14

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

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

3 Comments

Ed Gibbs Over a year ago

Also note from the CREATE TABLE in the question that id will start at 1, so the first time through the loop there will be a NO_DATA_FOUND exception because the query is looking for id = 0. I'd recommend initializing x to 1 instead of zero, or moving the x := x + 1 to come before the select.

Underm1nd Over a year ago

This was just a learning exercise for me. I just needed to get a simple block of dynamic sql working and I can build onto that. Thank you all so much for your help.

Underm1nd Over a year ago

Thanks for the link to these article. Incredibly helpful.

Collectives™ on Stack Overflow

while loop for sql statement in Oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related