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

 In article <3l75eb$huv_at_dcsun4.us.oracle.com>, surman_at_oracle.com (Scott Urman) writes:
> 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.

You just need to use the dbms_utility.analyze_schema() package procedure supplied by Oracle (see page A-3 in Application Developer's Guide) within your PL/SQL block. There is no need to re-invent the wheel here!!!

-- 
------------------------------------------------------------------------------
Pierre Hollard                                 
Oracle Database Administrator                  Phone: (404) 827-0656
Turner Broadcasting System, Inc.              E-Mail: phollard_at_dev1.turner.com
------------------------------------------------------------------------------