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

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. Received on Mon Mar 27 1995 - 21:59:07 CEST