Oracle uses Utl_file to export table data to TXT files

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 the stored procedure and deposit 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 (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, Securitys Tatus, Adtypes, Permissionlevel, verified, Bottomdesc, SIGNATUREMD5, Apkmd5, Minversioncode, Pclassid, ClassID from APPLL Jkll;

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

/

Perform:

Sql> @/home/oracle/base.sql

Procedure created.

sql> exec Appdetail;

PL/SQL procedure successfully completed.

At fault:

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

Solve:

Modify

Max_linesize is 3200;

File_handle: = Utl_file.fopen (' Txt_dir ', ' qn_desktop_appdetail.txt ', ' a ',32000);

Note: If Max_linesize is not known, its default value is 1024;

Utl_file Sub-procedure fopen syntax:

Syntax

Utl_file. FOPEN (Location-VARCHAR2, filename in VARCHAR2, Open_mode-VARCHAR2, max_linesize in Binary_integ ER) RETURN File_type;

This article is from the "Bug" blog, please be sure to keep this source http://worms.blog.51cto.com/969144/1666046

Oracle uses Utl_file to export table data to TXT file