Re: pl/sql simple help - analyze table avail?

surman_at_oracle.com (Scott Urman) wrote:

>In article <odysscci.258.000E0507_at_teleport.com>, odysscci_at_teleport.com (Jim Kennedy) writes:
>|> I created a procedure in pl/sql and the whole thing compiles and runs except
>|> if I uncomment 1 line. Of course that one line is hte whole purpose of the
>|> procedure. The purpose of this procedure is to run analyze on the whole
>|> shooting match (except system and sys). Here is the code:
>|>
>|> create or replace procedure analyze_all is
>|> cursor c1 is
>|> select table_name,owner from all_tables where owner not in('SYS','SYSTEM');
>|> list_to c1%ROWTYPE;
>|> tabName varchar2(255);
>|>
>|> BEGIN
>|> open c1;
>|> loop
>|> fetch c1 into list_to;
>|> exit when c1%NOTFOUND;
>|> tabName:= list_to.owner ||'.' ||list_to.table_name ;
>|>
>|> -- won't work -> analyze table :tabName compute statistics;
>|>
>|> commit;
>|> end loop;
>|> close c1;
>|> END analyze_all;
>|>
>|> I have tried execute immediate and building a string for that.
>|>
>|> Anyone have some suggestions? I would prefer that this was a stored
>|> procedure that some process can kick off every so often.
>|>
>|> TIA,
>|> Jim Kennedy
>|>
 

>This is dynamic SQL, for which you need the DBMS_SQL package which is shipped
>with Oracle 7.1. You can't use a variable for the name of an Oracle object,
>such as a table or column name.

by the way - the package dbms_sql is documented only in oracle 7.1 but also exists in oracle 7.0 (don't know what i should think about it) Received on Thu Mar 30 1995 - 00:00:00 CEST