Shulou(Shulou.com)06/01 Report--

Environment:

Red Hat Enterprise Linux Server release 6.6

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

-- create a directory

Create or replace directory TXT_DIR as'/ opt/oracle/database/exptxt'

Grant read,write on directory TXT_DIR to manager_desk

-- write stored procedures and store them in the file / 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 (25pr 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_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

/

Execute:

SQL > @ / home/oracle/base.sql

Procedure created.

SQL > exec appdetail

PL/SQL procedure successfully completed.

Deal with the mistake:

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

Resolve:

Modify

Max_linesize is 3200

File_handle: = utl_file.fopen ('TXT_DIR','qn_desktop_appdetail.txt','a',32000)

Note: if you do not know max_linesize, its default value is 1024

Utl_file subprocedure fopen syntax:

Syntax

UTL_FILE.FOPEN (location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.