utl_file导出的文件大小受了限制,怎么解决?/急

Question

我用utl_file把表中的数据导出到EXCEL中,但文件大小好像限制在3M,我怎么做才能扩大这个限制?

Answer 1

应该是可以导出的，
utl_file.put(file in file_type,buffer in varchar2);
是将缓冲区内容写入到文件中。如果报错说文件大小不够，可能是你缓冲区大小不合适，你可以设置大点，dbms_output.enable(32768);
put有最大长度32768限制，快到这个值就需要flush一下;
然后用utl_file.new_line(file);结束一行。
主要是就跟缓冲区有关系，应该是自动分配的

Answer 2

错了,应该限制的是33K,我所有的导出都是超过33K的都不出来了>

Answer 3

看看我刚发表的blog吧：http://blog.csdn.net/gelyon/archive/2010/12/01/6048662.aspx

Answer 4

网上研究了下,回来发现1楼的好眼熟,小鄙视下.
楼主可以尝试在倒出过程中隔一会就执行下  alter system flush shared_pool;或者alter system flush buffer_cache;

Answer 5

用PLSQL是不是就不需要这些了啊,好像记得能直接把表导成EXCEL吧

Answer 6

developer 有自带的工具直接导出还是一样的啊

Answer 7

7楼到底是指用PLSQL就不会考虑这大小 限制了,还是说也会有大小限制? 迷茫

Answer 8

存储过程:
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;