分类: Oracle
2009-03-08 19:46:59
今天翻日记,突然看见以前一位网友提到的一个旧话题,即在脚本中用SPOOL动态创建文件,大概脚本如
下:
set serveroutput on;
declare
v_spid t1.spid%type;
v_filename varchar2(50);
cursor cur is select spid,spid || '.txt' from t1 group by spid;
begin
open cur;
loop
fetch cur into v_spid,v_filename;
exit when cur%notfound;
--dbms_output.put_line(v_filename);
spool v_filename;
--select * from t1 --where spid = v_spid;
spool off;
end loop;
close cur;
end;
/
exit;
上面的语句直接运行会报错,spool v_filename不能这么使用,替换成define filen=v_filename;spool
&filen也不行,会提示输入文件名,除非把文件名写死,但又不符合要求,文件名是从SQL中查到的,需
要用一个字段+txt的形式进行命名,试了半天也没想出结果,请问此问题有解决方案否?(在ORACLE内
部解决不用外部程序)
思路如下,生成一个新的脚本并执行之。具体再修改调试:
set serveroutput on;
SPOOL new.sql
declare
v_spid t1.spid%type;
v_filename varchar2(50);
cursor cur is select spid,spid || '.txt' from t1 group by spid;
begin
open cur;
loop
fetch cur into v_spid,v_filename;
exit when cur%notfound;
--dbms_output.put_line(v_filename);
DBMS_OUTPUT.PUT_LINE('spool '||v_filename);
DBMS_OUTPUT.PUT_LINE('select * from t1 where spid = '||v_spid||';');
DBMS_OUTPUT.PUT_LINE('spool off');
end loop;
close cur;
end;
/
SPOOL OFF
@@new.sql
exit;
思路如下,生成一个新的脚本并执行之。具体再修改调试:
set serveroutput on;
SPOOL new.sql
declare
v_spid t1.spid%type;
v_filename varchar2(50);
cursor cur is select spid,spid || '.txt' from t1 group by spid;
begin
open cur;
loop
fetch cur into v_spid,v_filename;
exit when cur%notfound;
--dbms_output.put_line(v_filename);
DBMS_OUTPUT.PUT_LINE('spool '||v_filename);
DBMS_OUTPUT.PUT_LINE('select * from t1 where spid = '||v_spid||';');
DBMS_OUTPUT.PUT_LINE('spool off');
end loop;
close cur;
end;
/
SPOOL OFF
@@new.sql
exit;