如何生成从批处理文件运行的SQL脚本日志

Question

我在下面delete_import.bat运行良好的文件，它有以下内容：

sqlplus @C:Exp_Imp_Utildelete_tmc.sql

删除tmc.sql文件包含我要删除的所有数据库对象。但是现在每当我运行批处理文件时，它都应该创建所有删除语句的日志，如果在删除 sql 语句时发生任何 oracle 错误，它也应该写入日志文件。

CONNECT TMC/TMC;
spool off
declare
stringa varchar2(100);
cursor cur is
select *
from user_objects;
begin
for c in cur loop
begin
stringa := '';
if c.object_type = 'VIEW' then
stringa := 'drop view ' || c.object_name;
EXECUTE immediate stringa; 
elsif c.object_type = 'TABLE' then
stringa := 'drop table ' || c.object_name || ' cascade constraints'; 
EXECUTE immediate stringa; 
elsif c.object_type = 'SEQUENCE' then
stringa := 'drop sequence ' || c.object_name; 
EXECUTE immediate stringa; 
elsif c.object_type = 'PACKAGE' then
stringa := 'drop package ' || c.object_name; 
EXECUTE immediate stringa;      
elsif c.object_type = 'TRIGGER' then
stringa := 'drop trigger ' || c.object_name; 
EXECUTE immediate stringa;      
elsif c.object_type = 'PROCEDURE' then
stringa := 'drop procedure ' || c.object_name; 
EXECUTE immediate stringa; 
elsif c.object_type = 'FUNCTION' then
stringa := 'drop function ' || c.object_name; 
EXECUTE immediate stringa;      
elsif c.object_type = 'SYNONYM' then
stringa := 'drop synonym ' || c.object_name; 
EXECUTE immediate stringa; 
elsif c.object_type = 'INDEX' then
stringa := 'drop index ' || c.object_name; 
EXECUTE immediate stringa; 
elsif c.object_type = 'PACKAGE BODY' then
stringa := 'drop PACKAGE BODY ' || c.object_name; 
EXECUTE immediate stringa;      
end if;
     exception
when others then
null;
end; 
end loop;
-- PURGE recyclebin
end;
/
EXIT;

Answer 1

您可以将SPOOL设置为写入文件，然后使用DBMS_OUTPUT：

脚本.sql：

spool spool.txt
set serveroutput on
declare
    vSQL varchar2(1000);
begin
    vSQL := 'create table tab1 ( a number)';
    begin
        execute immediate vSQL;
        dbms_output.put_line('OK - ' || vSQL);
    exception
    when others then
        dbms_output.put_line('KO - ' || vSQL || ' - ' || sqlerrm);        
    end;
    vSQL := 'drop table tab1';
    begin
        execute immediate vSQL;
        dbms_output.put_line('OK - ' || vSQL);
    exception
    when others then
        dbms_output.put_line('KO - ' || vSQL || ' - ' || sqlerrm);        
    end;
    vSQL := 'drop table tab1';
    begin
        execute immediate vSQL;
        dbms_output.put_line('OK - ' || vSQL);
    exception
    when others then
        dbms_output.put_line('KO - ' || vSQL || ' - ' || sqlerrm);        
    end;
end;
/   
spool off 

运行脚本后，文件假脱机.txt将为：

OK - create table tab1 ( a number)                                              
OK - drop table tab1                                                            
KO - drop table tab1 - ORA-00942: table or view does not exist                  
PL/SQL procedure successfully completed.