Hi,

I prepared an anonymous PL/Sql bloc and i would like to execute it
into a batch file (.sh) from unix.

in a .sh file, i wrote

export ORACLE_SID=SID
sqlplus -s username/password <<!EOF
declare
cursor cur_tables is
select table_name from user_tables;
nom_table varchar2(30);
req varchar2(255);
begin
open cur_tables;
loop
fetch cur_tables into nom_table;
exit when cur_tables%NOTFOUND;
EXECUTE IMMEDIATE 'analyze table ' || nom_table || ' compute statistics';
end loop;
close cur_tables;

when i execute the .sh file, i received no errors but the statistics are not updated in my schema.

any help is appreciated.

Your PL/SQL block is incomplete. It needs:

END; -- to end it
/ -- to run it

BTW, why go through all the pain of declaring, opening, fetching, testing NOTFOUND and closing cursor when this does the same job? :-

BEGIN
for r in (select table_name from user_tables) loop
EXECUTE IMMEDIATE 'analyze table ' || r.table_name || ' compute statistics';
end loop;
END;
/

It's missing also th eclosing part of this <<!EOF

Add ! as the last lane fo your shell script.


Hope that helps,

clio_usa
OCP - DBA