Re: View creation!!Please help!

Yep,
use dbms_sql (v7) or execute immediate (v8) with your create command assigned to a varchar2.

Hth,

Sybrand Bakker, Oracle DBA

Deepa Kamat <dkamat_at_questone.com> wrote in message news:OQiE4.3774$8k1.259260_at_paloalto-snr1.gtei.net...
> 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:47:36 CST