View creation!!Please help!

Hi all,

    I have encountered a problem regrading the creation of views. my aim is to create 1000 views basically to check if the server can support those many creations and the time it takes to access any particular view. I would be greatful if you could help me out in this.. Is there and way by which I can create a procedure and using a for loop, perform creation of the view in it.The names of the views could be the numeric iterator values of the for loop. when I tried this,it gave me the following error.

system_at_SUN> create procedure abc as begin   2 declare
  3 i integer(10);
  4 begin
  5 loop
  6 create view pd_view_i as
  7 select pd_id, pd_parent, pd_hierarchy, pd_id_val, pd_parent_val from   8 product
  9 where product.pd_id in
 10 ((select dimvalue dmval
 11 from product_permitvar
 12 where userid = 'Admin1'
 13 and (permitvalue = 1 or permitvalue = 2)  14 and dimvalue in
 15 (select pd_id
 16 from product
 17 where pd_hierarchy = 'STANDARD'))  18 union
 19 (select unique(dimvalue)
 20 from product_permitvar
 21 where userid in
 22 (select groupid from groupinfo
 23 connect by userid = prior groupid  24 start with userid = 'Admin1')
 25 and (permitvalue = 1 or permitvalue = 2)  26 and dimvalue in
 27 (select pd_id
 28 from product
 29 where pd_id NOT IN
 30 (select dimvalue
 31 from product_permitvar
 32 where userid = 'Admin1'
 33 and (permitvalue = 1 or permitvalue = 2)  34 and dimvalue in
 35 (select pd_id
 36 from product
 37 where pd_hierarchy = 'STANDARD'))  38 and pd_hierarchy = 'STANDARD')
 39 ))
 40 and pd_hierarchy = 'STANDARD'
 41 i= i+1;
 42 exit when i>1000;
 43 end loop;
 44 end;
 45 end;
 46 /
 47
 48 /

Warning: Procedure created with compilation errors.

system_at_SUN> sho errors
Errors for PROCEDURE ABC:

LINE/COL ERROR

-------- -----------------------------------------------------------------
6/2      PLS-00103: Encountered the symbol "CREATE" when expecting one of
         the following:
         begin declare exit for goto if loop mod null pragma raise
         return select update while <an identifier>


         <a double-quoted delimited-identifier> <a bind variable> <<


         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall


         <a single-quoted SQL string>

Is there any other way out...

Please help!

Thanks and Regards,
Deepa Received on Wed Mar 29 2000 - 02:07:10 CST