oracle 文件写入错误,急!急!急!急!急!
帮忙看下,这个过程有什么错误,执行的时候 老是出错,文件写入错误
错误码是:ORA-29285
create or replace procedure show_open_user_test
(
in_date in date,
in_code out integer,
in_msg out varchar2
)
is
c1 iptv_ora.t_retdataset;
v_file_handle utl_file.file_type;
--异常变量
err_msg exception;
--生成文件名
v_filename varchar2(500);
--文件标题
v_result varchar2(500);
--文件内容
v_results varchar2(2000);
--查询出的内容
content_all varchar2(2000);
--开始时间
beg_time varchar2(19);
--结束时间
end_time varchar2(19);
--v_count number;
begin
if to_char(in_date, 'dd') = '01' then
v_filename := 'hw_openusercount_'||to_char(in_date - 1, 'yyyymm')||'.csv';
v_file_handle := utl_file.fopen('FILE_DIR', v_filename, 'w');
--v_file_handle := utl_file.fopen('FILE_DIR', v_filename, 'w');65534
--上月1号起
beg_time := to_char(add_months(in_date, -1), 'yyyy-mm-dd') || ' 00:00:00';
--本月1号止
end_time := to_char(in_date, 'yyyy-mm-dd') || ' 00:00:00';
open c1 for
select convert(user_id || ',' || open_user_count || ','
|| area_id || ',' || area_name,
'ZHS16GBK', 'UTF8')
--select user_id || ',' || open_user_count || ','
--|| area_id || ',' || area_name
from
(
select count(a.userid) as open_user_count,
b.areaid as area_id,
c.areaname as area_name,
a.userid as user_id
from tbl_userstatus a,
tbl_subscriberinfo b,
tbl_areainfo c
where a.userid = b.userid
and b.areaid = c.areaid
and a.statetime >= beg_time
and a.statetime < end_time
group by b.areaid,
c.areaname,
a.userid
order by c.areaname desc
) t;
v_result := 'user_id,open_user_count,area_id,area_name';
utl_file.put_line(v_file_handle, v_result);
fetch c1 into content_all;
while c1%found
loop
v_results := content_all;
utl_file.put_line(v_file_handle, v_results);
/* v_count := v_count+1;
if v_count >= 5000 then
utl_file.fflush(v_file_handle);
v_count := 0;
end if;*/
fetch c1 into content_all;
end loop;
close c1;
utl_file.fclose(v_file_handle);
end if;
in_code := sqlcode;
in_msg := 'execute SHOW_OPEN_USER success!!';
exception
when err_msg then
in_code := sqlcode;
in_msg := substr(sqlerrm, 1, 200);
end show_open_user_test;