環(huán)境：
創(chuàng)新互聯(lián)長期為近千家客戶提供的網(wǎng)站建設(shè)服務(wù)，團隊從業(yè)經(jīng)驗10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為中陽企業(yè)提供專業(yè)的成都做網(wǎng)站、網(wǎng)站制作，中陽網(wǎng)站改版等技術(shù)服務(wù)。擁有十載豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。
Red Hat Enterprise Linux Server release 6.6
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--創(chuàng)建目錄
create or replace directory TXT_DIR as '/opt/oracle/database/exptxt';
grant read,write on directory TXT_DIR to manager_desk;
--編寫存儲過程，并存入文件/home/oracle/detail.sql
create or replace PROCEDURE appdetail
is
file_handle utl_file.file_type;
Write_content VARCHAR2(5000);
v_DETAILID NUMBER;
v_APPID NUMBER;
v_APPVER NUMBER;
v_APPVERNAME VARCHAR2(254);
v_PACKAGENAME VARCHAR2(254);
v_CRC32 VARCHAR2(20);
v_ICON VARCHAR2(254);
v_DESCRIPTION CLOB;
v_CSIZE NUMBER;
v_PAYTYPE NUMBER;
v_COST NUMBER(25,3);
v_STATE NUMBER;
v_CREATORID NUMBER;
v_MODDATE DATE;
v_APPPATH VARCHAR2(500);
v_ISDEF NUMBER;
v_DOWNLOADCOUNT NUMBER;
v_COOPERATIONID NUMBER;
v_REALAPPID NUMBER(38);
v_CREATEDATE DATE;
v_ISADVERTISER NUMBER(38);
v_EDITOR NUMBER;
v_ISGETDATA NUMBER;
v_SECURITYSTATUS NUMBER;
v_ADTYPES NUMBER;
v_PERMISSIONLEVEL NUMBER;
v_VERIFIED NUMBER;
v_BOTTOMDESC VARCHAR2(512);
v_SIGNATUREMD5 VARCHAR2(64);
v_APKMD5 VARCHAR2(64);
v_MINVERSIONCODE NUMBER(38);
v_PCLASSID NUMBER(38);
v_CLASSID NUMBER(38);
cursor cur_sp_out
is
select detailid, appid, appver, appvername, packagename, crc32, icon, description, csize, paytype, cost, state, creatorid, moddate, apppath, isdef, downloadcount, cooperationid, realappid, createdate, isadvertiser, editor, isgetdata, securitystatus, adtypes, permissionlevel, verified, bottomdesc, signaturemd5, apkmd5, minversioncode, pclassid, classid from applljkll;
begin
open cur_sp_out;
loop
fetch cur_sp_out into v_detailid, v_appid, v_appver, v_appvername, v_packagename, v_crc32, v_icon, v_description, v_csize, v_paytype, v_cost, v_state, v_creatorid, v_moddate, v_apppath, v_isdef, v_downloadcount, v_cooperationid, v_realappid, v_createdate, v_isadvertiser, v_editor, v_isgetdata, v_securitystatus, v_adtypes, v_permissionlevel, v_verified, v_bottomdesc, v_signaturemd5, v_apkmd5, v_minversioncode, v_pclassid, v_classid;
exit when cur_sp_out%notfound;
file_handle := utl_file.fopen('TXT_DIR','applljkll.txt','a',32000);
--specified directories ,file_name and max_linesize
write_content := v_detailid||', '||v_appid||', '||v_appver||', '||v_appvername||', '||v_packagename||', '||v_crc32||', '||v_icon||', '||v_description||', '||v_csize||', '||v_paytype||', '||v_cost||', '||v_state||', '||v_creatorid||', '||v_moddate||', '||v_apppath||', '||v_isdef||', '||v_downloadcount||', '||v_cooperationid||', '||v_realappid||', '||v_createdate||', '||v_isadvertiser||', '||v_editor||', '||v_isgetdata||', '||v_securitystatus||', '||v_adtypes||', '||v_permissionlevel||', '||v_verified||', '||v_bottomdesc||', '||v_signaturemd5||', '||v_apkmd5||', '||v_minversioncode||', '||v_pclassid||', '||v_classid;
--write file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
END IF;
--close file
utl_file.fclose(file_handle);
end loop;
close cur_sp_out;
end;
/
執(zhí)行：
SQL> @/home/oracle/base.sql
Procedure created.
SQL> exec appdetail;
PL/SQL procedure successfully completed.
處錯：
SQL> exec appbase
BEGIN appbase; END;
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 690
ORA-06512: at "MANAGER_DESK.APPBASE", line 55
ORA-06512: at line 1
解決：
修改
max_linesize 為 3200;
file_handle := utl_file.fopen('TXT_DIR','qn_desktop_appdetail.txt','a',32000);
注：如果不知道m(xù)ax_linesize，其默認值為1024；
utl_file 子過程 fopen語法：
Syntax
UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type;
重慶分公司
重慶分公司
