用好UTL_FILE包其实并不是太容易

用好UTL_FILE包其实并不是太容易,不信吗?

我们来看两个跟UTL_FILE包有关的故障分析过程。

 

故障一:某环境最近用PL/SQL向远程服务器端写文件时遇到中文乱码情况,该程序主要是提取数据库中插入数据语句,通过UTL_FILE.PUT_LINE 在服务器端写入文本文件,但只要数据中含有中文,那输出文件里的insert语句中中文就是乱码。

 

故障分析过程:

首先要明白一点,UTL_FILE.GET_LINEUTL_FILE.PUT_LINE在读取行记录和写入行记录时并不做任何的字符集转换,所以如果源数据库的字符集是AL32UTF8,则当我们使用默认是中文字符集ZHS16GBKWindows查看从上述源数据库调用UTL_FILE.PUT_LINE写入的含中文的行记录时,就必然会是乱码

 

我们来看一个实例:

现在源数据库的字符集是AL32UTF8

SQL> select value$ from sys.props$ where name=’NLS_CHARACTERSET’;

 

VALUE$

——————————————————————————–

AL32UTF8

 

创建一个测试表T1,并插入一条含中文的行记录:

SQL> create table t1(c1 varchar2(2000));

 

Table created

 

SQL> insert into t1 values(‘TEST1崔华‘);

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select * from t1;

 

C1

——————————————————————————–

TEST1崔华

 

将上述行记录写入/ftptemp下的test1.txt中:

SQL> select directory_name,directory_path from dba_directories where directory_name=’TEST’;

 

DIRECTORY_NAME                 DIRECTORY_PATH

—————————— ——————————————————————————–

TEST                               /ftptemp

 

SQL> declare

  2     inf utl_file.file_type;

  3     line_org  varchar2(2000);

  4     o_vc_return_flag varchar2(2000);

  5  begin

  6     inf := utl_file.fopen(‘TEST’,’test1.txt’, ‘W’);

  7     select c1 into line_org from t1;

  8     utl_file.put_line(inf, line_org);

  9     utl_file.fclose(inf);

 10  exception

 11    when others then

 12      o_vc_return_flag := ‘E’ || ‘_’ || sqlcode || ‘_’ || sqlerrm;

 13      raise_application_error(-20109,o_vc_return_flag);

 14      return;

 15  end;

 16  /

 

PL/SQL procedure successfully completed

 

oracle:/ftptemp>pwd

/ftptemp

oracle:/ftptemp>ls -l test1.txt

-rw-r–r–    1 oracle   dba              12 Oct 26 10:07 test1.txt

 

从如下结果里可以看到,test1.txt中的中文“崔华”确实对应的是乱码:

oracle:/ftptemp>cat test1.txt

TEST1宕斿崕

 

解决方法很简单,就是用CONVERT函数手工转一下字符集就可以了:

SQL> declare

  2     inf utl_file.file_type;

  3     line_org  varchar2(2000);

  4     line_conv raw (2000);

  5     o_vc_return_flag varchar2(2000);

  6  begin

  7     inf := utl_file.fopen(‘TEST’,’test2.txt’, ‘W’);

  8     select c1 into line_org from t1;

  9   line_conv := utl_raw.cast_to_raw(CONVERT(line_org, ‘ZHS16GBK’, ‘AL32UTF8’));

 10     utl_file.put_raw(inf, line_conv);

 11     utl_file.fclose(inf);

 12  exception

 13    when others then

 14      o_vc_return_flag := ‘E’ || ‘_’ || sqlcode || ‘_’ || sqlerrm;

 15      raise_application_error(-20109,o_vc_return_flag);

 16      return;

 17  end;

 18  /

 

PL/SQL procedure successfully completed

 

oracle:/ftptemp>pwd

/ftptemp

oracle:/ftptemp>ls -l test2.txt

-rw-r–r–    1 oracle   dba              10 Oct 26 10:18 test2.txt

 

从如下结果里可以看到,现在中文“崔华”已经能正常显示了:

oracle:/ftptemp>cat test2.txt

TEST1崔华

 

 

故障二:最近一段时间,国航生产库在写接口文件的时候,不定期出现ORA-29283错误。

具体表现为:

1、  某些模块的写接口文件的程序不定期报错ORA-29283,整个写接口文件的作业失败,但第二天再次尝试执行同样的程序则成功,不再报错;

2、  出问题的地方全部集中在Oracle内部的包“SYS.UTL_FILE”代码的第488行;

 

故障分析过程:

如下是国航生产库syslog里记录的所有跟上述错误相关的具体信息:

SQL> select ylopgm,yloerr from syslog where yloerr like ‘%ORA-29283%’ order by ylocde desc;

YLOPGM                    YLOERR                                 

 ————————- —————————————-

 P_Daily_Earlier_Prm_Tkt   -29283ORA-29283: invalid file operation

                           ORA-06512: at “SYS.UTL_FILE”, line 488

                           ORA-29283: invalid file operation

 

 P_Daily_Earlier_Prm_Xbg   -29283ORA-29283: invalid file operation

                           ORA-06512: at “SYS.UTL_FILE”, line 488

                           ORA-29283: invalid file operation

 

 ……省略显示类似内容

 

 OWBDaily:P_OGENRNRRESPONS -29283ORA-29283: invalid file operation

                              ORA-06512: at “SYS.UTL_FILE”, line 488

                              ORA-29283: invalid file operation

 

47 rows selected

 

经我仔细核查,所有常规的导致ORA-29283错误的原因均被我一一排除而且现在最关键的问题是所有相关经验均无法解释为什么在出错后第二天再执行同样的代码就可以成功执行不再报错了?

 

现在,我们在测试环境重现了这个问题并且给出了解决方法:

首先我们要了解utl_file在一个session里最多只能支持并发打开50个文件句柄,如果超过了50,则Oracle会报错,我们来看看如下测试结果:

 

SQL> create or replace procedure P_TEST_UTLFILE is

  2    f_Int UTL_FILE.FILE_TYPE;

  3  begin

  4 

  5    for i in 1 .. 51 loop

  6      f_Int := utl_file.fopen(‘DIR_PRP_INTERFACE’,i || ‘1.txt’,’w’,32767);

  7    end loop;

  8 

  9    utl_file.put_line(f_Int, rpad(‘1′,10000,’1’));

 10    utl_file.fflush(f_Int);

 11    utl_file.fclose(f_Int);

 12 

 13  end P_TEST_UTLFILE;

 14  /

 

Procedure created

 

在上述存储过程P_TEST_UTLFILE,我们尝试并发打开51个文件句柄,现在我们执行一下这个存储过程:

SQL> exec P_TEST_UTLFILE;

 

begin P_TEST_UTLFILE; end;

 

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 488

ORA-29283: invalid file operation

ORA-06512: at “CAIPRA.P_TEST_UTLFILE”, line 6

ORA-06512: at line 1

 

从结果里我们可以看到,我们已经一模一样的重现了国航生产的ORA-29283错误,并且出问题的地方就是在Oracle内部的包“SYS.UTL_FILE”代码的第488行。

看起来国航生产的情况就是因为文件句柄的并发open的数量超过了50,但是这里的超过50有两种情况:

1、  在某个时间段由于大量的并发,导致文件句柄的并发open的数量超过了50

2、  由于在调用utl_file.fopen打开文件句柄的时候没有与之相匹配的调用utl_file.fclose,导致某个sessionopen文件句柄的数量在缓慢增加,当增加到50的时候,这个session如果再次调用utl_file.fopen,则也会报上述错误。

 

上述情况1经询问,不太可能在我们系统里出现,IPRA里应该是没有在某个时间点(特别是在daily作业的时候)出现文件句柄的并发open的数量超过50的情况。

现在我们重点来关注情况2

 

我们依然在测试环境构造出情况2

SQL> create or replace procedure P_TEST_UTLFILE is

  2    f_Int UTL_FILE.FILE_TYPE;

  3  begin

  4 

  5    for i in 1 .. 50 loop

  6      f_Int := utl_file.fopen(‘DIR_PRP_INTERFACE’,i || ‘1.txt’,’w’,32767);

  7      utl_file.put_line(f_Int, rpad(‘1′,10000,’1’));

  8      utl_file.fflush(f_Int);

  9    end loop;

 10 

 11  end P_TEST_UTLFILE;

 12  /

 

Procedure created

 

现在我们改写了上述测试存储过程P_TEST_UTLFILE,使得其只调用了utl_file.fopen,而没有调用utl_file.fclose

我们同时开两个Session

 

首先在Session 1里执行上述存储过程P_TEST_UTLFILE

Session 1

SQL> exec P_TEST_UTLFILE;

 

PL/SQL procedure successfully completed

 

可以看到,在session 1里,存储过程P_TEST_UTLFILE是可以成功执行的,但请注意,Session 1里已经累计open50个文件句柄,也就是说如果我在session 1里再次执行P_TEST_UTLFILE,这时候Oracle一定会报错:

Session 1

SQL> exec P_TEST_UTLFILE;

 

begin P_TEST_UTLFILE; end;

 

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 488

ORA-29283: invalid file operation

ORA-06512: at “CAIPRA.P_TEST_UTLFILE”, line 6

ORA-06512: at line 1

 

但如果这时候再新开一个Session 2,大家可以看到,Session 2里依然是可以成功执行上述存储过程P_TEST_UTLFILE的,这很正常,文件句柄的并发open的上限50是针对session而言的,并不针对整个数据库

Session 2

SQL> exec P_TEST_UTLFILE;

 

PL/SQL procedure successfully completed

 

好了,现在我们已经可以解释为什么国航生产某些模块的写接口文件的程序不定期报错ORA-29283,整个写接口文件的作业失败,但第二天再次尝试执行同样的程序则成功,不再报错?

因为国航生产采用了连接池的机制,连接数据库的Session是在连接池里一直存在的,当前台有连接请求的时候,就随机从连接池里返回一个空闲的连接,前台用完这个连接后就将其释放回连接池,所以这就导致了可能某些Session是一直存在的,从来就没有真正断开过

我们现在假设这样一种情况:

我们的某段代码调用utl_file.fopen打开文件句柄后没有与之相匹配的调用utl_file.fclose,那么一旦连接池里的某个Session调用了这段代码,在这个Session里,这些打开的文件句柄就永远不会释放了,并且如果下次还是这个Session调用了同样的那段代码,则这个Session里的文件句柄数量是会缓慢增加的,当增加到50后,任何前台连接只要从连接池里选择了这个session并且这个前台连接执行了产生接口文件的操作,那么Oracle这里一定会报错ORA-29283: invalid file operation(这就是我在上述测试中用Session 1模拟的情况)。

但同时,只要前台连接并没有从连接池里选择到这个session,那么执行同样的产生接口文件的操作就不会报错了(这就是我在上述测试中用Session 2模拟的情况)。国航生产里发现错误后第二天再次执行同样代码的时候,很可能已经不是原来的那个Session了,所以会成功执行。

 

针对上述问题,我们的解决方法是:

1、  临时的缓解方法是重启一下IPRA应用的server,这样,连接池里的已有session就都被清掉了;

2、  根本的解决方法是在每个可能调用到utl_file.fopen的代码的末尾(包括代码里每一个return之前)和exception处理中加入utl_file.fclose_all()以强制关闭所有可能的文件句柄,注意Oracle已经帮你封装好了utl_file.fclose_all,可以直接调用,无需判断是否还有打开的文件句柄,如下所示:

SQL> create or replace procedure P_TEST_UTLFILE is

  2    f_Int UTL_FILE.FILE_TYPE;

  3  begin

  4    utl_file.fclose_all();

  5  end P_TEST_UTLFILE;

  6  /

 

Procedure created

 

SQL> exec P_TEST_UTLFILE;

 

PL/SQL procedure successfully completed

 

3、  另外一个简便的解决方法是在每个可能调用到utl_file.fopen的代码的最开始加入utl_file.fclose_all()以强制关闭所有可能的文件句柄,如下所示:

首先执行存储过程P_TEST_UTLFILE,并发打开50个文件句柄,并且不关闭:

SQL> create or replace procedure P_TEST_UTLFILE is

  2    f_Int UTL_FILE.FILE_TYPE;

  3  begin

  4    for i in 1 .. 50 loop

  5    f_Int := utl_file.fopen(‘DIR_PRP_INTERFACE’,i || ‘1.txt’,’w’,32767);

  6    end loop;

  7    utl_file.put_line(f_Int, rpad(‘1′,10000,’1’));

  8    utl_file.fflush(f_Int);

  9  end P_TEST_UTLFILE;

 10  /

 

Procedure created

 

SQL> exec P_TEST_UTLFILE;

 

PL/SQL procedure successfully completed

 

此时只要我在上述session中执行任何一个需要调用到utl_file.fopen的存储过程oracle都会报错:

SQL> create or replace procedure P_TEST_UTLFILE_1 is

  2    f_Int UTL_FILE.FILE_TYPE;

  3  begin

  4 

  5    f_Int := utl_file.fopen(‘DIR_PRP_INTERFACE’,’1.txt’,’w’,32767);

  6    utl_file.put_line(f_Int, rpad(‘1′,10000,’1’));

  7    utl_file.fflush(f_Int);

  8    utl_file.fclose(f_Int);

  9  end P_TEST_UTLFILE_1;

 10  /

 

Procedure created

 

SQL> exec P_TEST_UTLFILE_1;

 

begin P_TEST_UTLFILE_1; end;

 

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 488

ORA-29283: invalid file operation

ORA-06512: at “CAIPRA.P_TEST_UTLFILE_1”, line 5

ORA-06512: at line 1

 

为了让上述存储过程P_TEST_UTLFILE_1能够成功执行,我们修改一下P_TEST_UTLFILE_1的代码,强制在其代码最开头加入utl_file.fclose_all()以强制关闭所有可能的文件句柄,可以看到,修改代码后P_TEST_UTLFILE_1已经可以成功执行,因为那50个打开的文件句柄已经被我们强制清除了:

SQL> create or replace procedure P_TEST_UTLFILE_1 is

  2    f_Int UTL_FILE.FILE_TYPE;

  3  begin

  4    utl_file.fclose_all();

  5    f_Int := utl_file.fopen(‘DIR_PRP_INTERFACE’,’1.txt’,’w’,32767);

  6    utl_file.put_line(f_Int, rpad(‘1′,10000,’1’));

  7    utl_file.fflush(f_Int);

  8    utl_file.fclose(f_Int);

  9  end P_TEST_UTLFILE_1;

 10  /

 

Procedure created

 

SQL> exec P_TEST_UTLFILE_1;

 

PL/SQL procedure successfully completed

 

但请注意,调用utl_file.fclose_all()以强制关闭所有可能的文件句柄可能是有副作用的,如下所示:

我先修改P_TEST_UTLFILE_1的代码,使其在代码的末尾调用utl_file.fclose_all()

SQL> create or replace procedure P_TEST_UTLFILE_1 is

  2    f_Int UTL_FILE.FILE_TYPE;

  3  begin

  4    f_Int := utl_file.fopen(‘DIR_PRP_INTERFACE’,’1.txt’,’w’,32767);

  5    utl_file.put_line(f_Int, rpad(‘1′,10000,’1’));

  6    utl_file.fflush(f_Int);

  7    utl_file.fclose_all();

  8  end P_TEST_UTLFILE_1;

  9  /

 

Procedure created

 

接着我创建存储过程P_TEST_UTLFILE_2P_TEST_UTLFILE_2模拟了一种极端的情况——就是在打开了一个文件句柄的情况下又同时调用了P_TEST_UTLFILE_1,这样随着P_TEST_UTLFILE_1的成功执行,P_TEST_UTLFILE_2中那个打开的文件句柄也会被关闭,等到P_TEST_UTLFILE_2的后续代码想往已经被关闭的那个文件句柄里写数据的时候,Oracle这时候就报错了:

SQL> create or replace procedure P_TEST_UTLFILE_2 is

  2    f_Int UTL_FILE.FILE_TYPE;

  3  begin

  4    f_Int := utl_file.fopen(‘DIR_PRP_INTERFACE’,’2.txt’,’w’,32767);

  5 

  6    P_TEST_UTLFILE_1;

  7 

  8    utl_file.put_line(f_Int, rpad(‘1′,10000,’1’));

  9    utl_file.fflush(f_Int);

 10    utl_file.fclose_all();

 11  end P_TEST_UTLFILE_2;

 12  /

 

Procedure created

 

SQL> exec P_TEST_UTLFILE_2;

 

begin P_TEST_UTLFILE_2; end;

 

ORA-29282: invalid file ID

ORA-06512: at “SYS.UTL_FILE”, line 136

ORA-06512: at “SYS.UTL_FILE”, line 813

ORA-06512: at “CAIPRA.P_TEST_UTLFILE_2”, line 8

ORA-06512: at line 1

 

所以如果IPRA里写接口文件有互相嵌套的情况,那么相关模块的负责人就要注意了,这种情况下就不应该用utl_file.fclose_all()了!

这时候我们应该怎么办呢?很简单,直接调用utl_file.fclose(文件句柄名)就可以了:

SQL> create or replace procedure P_TEST_UTLFILE_1 is

  2    f_Int UTL_FILE.FILE_TYPE;

  3  begin

  4    f_Int := utl_file.fopen(‘DIR_PRP_INTERFACE’,’1.txt’,’w’,32767);

  5    utl_file.put_line(f_Int, rpad(‘1′,10000,’1’));

  6    utl_file.fflush(f_Int);

  7    utl_file.fclose(f_Int);

  8  end P_TEST_UTLFILE_1;

  9  /

 

Procedure created

 

SQL> create or replace procedure P_TEST_UTLFILE_2 is

  2    f_Int UTL_FILE.FILE_TYPE;

  3  begin

  4    f_Int := utl_file.fopen(‘DIR_PRP_INTERFACE’,’2.txt’,’w’,32767);

  5 

  6    P_TEST_UTLFILE_1;

  7 

  8    utl_file.put_line(f_Int, rpad(‘1′,10000,’1’));

  9    utl_file.fflush(f_Int);

 10    utl_file.fclose_all();

 11  end P_TEST_UTLFILE_2;

 12  /

 

Procedure created

 

SQL> exec P_TEST_UTLFILE_2;

 

PL/SQL procedure successfully completed

 

可以用如下SQL检查出IPRA国航生产所有的调用了utl_file.fopen的地方,请相关负责人去检查一下代码,必要的时候在每个可能调用到utl_file.fopen的代码的末尾和exception处理中加入utl_file.fclose_all()以强制关闭所有可能的文件句柄(另外就是如果你的代码里还有分支,没执行到末尾就return了,那么必要的时候在每个return之前加入utl_file.fclose_all()以强制关闭所有可能的文件句柄):

SQL> select * from dba_source where owner not in(‘ORACLE_OCM’,’SYS’,’OLAPSYS’,’MDSYS’) and lower(text) like ‘%utl_file.fopen%’ order by name;

……省略显示输出内容

 

总结一下,从上述两个UTL_FILE包的故障处理过程我们可以看出,要想用好UTL_FILE包其实并不是那么容易的,我们特别需要关注如下这些方面:

1UTL_FILE.GET_LINEUTL_FILE.PUT_LINE在读取行记录和写入行记录时并不做任何的字符集转换,所以你要小心可能由此产生的乱码;

2UTL_FILE包在一个session里最多只能支持并发打开50个文件句柄;

3、调用UTL_FILE.FOPEN打开文件句柄,一旦使用完毕后别忘了调用UTL_FILE.FCLOSE关闭相应的文件句柄;

 


2 Comments on “用好UTL_FILE包其实并不是太容易”

  1. Xin says:

    崔华,你的书啥时候出版呀?

  2. Cui Hua says:

    得等到明年年初了,泪……


Leave a Reply

Your email address will not be published. Required fields are marked *