我用utl_file把表中的数据导出到EXCEL中,但文件大小好像限制在3M,我怎么做才能扩大这个限制?
解决方案 »
- nls_length_semantics参数这个意思吗?
- 请教,ora-12170:tns:connect timeout occurred
- oracle:存储过程中能否执行exp/imp 语句??
- solaris使用windows下的oracle
- pl/sql中提示"ORA-12541:TNS 没有监听器" 但tns listener 可以正常启动! (急,在线等待)
- 关于OracleDataReader问题,郁闷,(在线等)
- 请问ORACLE支持SCO UNIX吗?
- oracle9哪里有什么资料啊,基础的操作资料啊!
- MSSQL存储过程中的declare @在ORACL中怎么写
- 数据库行列瘦身
- 这样写的触发器怎么编译有错误啊??
- update
utl_file.put(file in file_type,buffer in varchar2);
是将缓冲区内容写入到文件中。如果报错说文件大小不够,可能是你缓冲区大小不合适,你可以设置大点,dbms_output.enable(32768);
put有最大长度32768限制,快到这个值就需要flush一下;
然后用utl_file.new_line(file);结束一行。
主要是就跟缓冲区有关系,应该是自动分配的
utl_file.put(file in file_type,buffer in varchar2);
是将缓冲区内容写入到文件中。如果报错说文件大小不够,可能是你缓冲区大小不合适,你可以设置大点,dbms_output.enable(32768);
put有最大长度32768限制,快到这个值就需要flush一下;
然后用utl_file.new_line(file);结束一行。
主要是就跟缓冲区有关系,应该是自动分配的 #4楼 得分:0回复于:2010-12-01 19:58:03应该是可以导出的,
utl_file.put(file in file_type,buffer in varchar2);
是将缓冲区内容写入到文件中。如果报错说文件大小不够,可能是你缓冲区大小不合适,你可以设置大点,dbms_output.enable(32768);
put有最大长度32768限制,快到这个值就需要flush一下;
然后用utl_file.new_line(file);结束一行。
主要是就跟缓冲区有关系,应该是自动分配的
我应该怎么做才能flush一下?
帮我写个语句.
谢谢!
CREATE OR REPLACE PROCEDURE SP_ItemMasterInfo
as
TYPE job_record_type is RECORD
(imitm proddta.f4101.imitm%TYPE,
imlitm proddta.f4101.imlitm%TYPE,
imdsc1 proddta.f4101.imdsc1%TYPE,
imsrp1 proddta.f4101.imsrp1%TYPE,
imrvno proddta.f4101.imrvno%TYPE,
imuom1 proddta.f4101.imuom1%TYPE,
imglpt proddta.f4101.imglpt%TYPE,
imstkt proddta.f4101.imstkt%TYPE,
imlnty proddta.f4101.imlnty%TYPE,
imsld proddta.f4101.imsld%TYPE,
imltlv proddta.f4101.imltlv%TYPE,
imacq proddta.f4101.imacq%TYPE,
immlq proddta.f4101.immlq%TYPE,
immtf1 proddta.f4101.immtf1%TYPE,
imuser proddta.f4101.imuser%TYPE
);
job_rec job_record_type;
CURSOR c_jobs IS
select imitm,imlitm,imdsc1,imsrp1,imrvno,imuom1,imglpt,imstkt,imlnty,imsld,imltlv,imacq,immlq,immtf1,imuser from
proddta.f4101 --where imitm=963
order by imitm;
l_file utl_file.file_type;
BEGIN
l_file :=utl_file.fopen('DIR_EXPORT','ItemMaster-W.xls','w');
utl_file.put(l_file,'Short_Item');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'Item');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'Description');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'imsrp1');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'imrvno');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'imuom1');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'imglpt');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'imstkt');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'imlnty');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'imsld');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'imltlv');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'imacq');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'immlq');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'immtf1');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,'imuser');
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,CHR(10)); OPEN c_jobs;
LOOP
FETCH c_jobs INTO job_rec.imitm,job_rec.imlitm,job_rec.imdsc1,job_rec.imsrp1,job_rec.imrvno,job_rec.imuom1,
job_rec.imglpt,job_rec.imstkt,job_rec.imlnty,job_rec.imsld,job_rec.imltlv,job_rec.imacq,job_rec.immlq,job_rec.immtf1,job_rec.imuser;
EXIT WHEN c_jobs%NOTFOUND;
utl_file.put(l_file,job_rec.imitm );
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imlitm);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imdsc1);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imsrp1);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imrvno);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imuom1);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imglpt);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imstkt);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imlnty);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imsld);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imltlv);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imacq);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.immlq);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.immtf1);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,job_rec.imuser);
utl_file.put(l_file,CHR(9));
utl_file.put(l_file,CHR(10));
END LOOP;
CLOSE c_jobs;
utl_file.fflush(l_file);
utl_file.fclose(l_file);
EXCEPTION
WHEN others THEN
IF utl_file.is_open(l_file) THEN
utl_file.fclose(l_file);
END IF;
END;