网站建设和seo讲师要求,青岛网站建设‘’,贺州市八步区建设局网站,南宁广告网页设计招聘信息最近有需求要导出一个专业的详单#xff0c;用于倒入sqlserver系统#xff0c;要求用分隔符隔开各字段。一开始准备用toad查询出来在save as#xff0c;但发现表太多#xff0c;要一张一张表盯着#xff0c;而且由于详单数据量大#xff0c;查询导出占用了大量的客户端内…最近有需求要导出一个专业的详单用于倒入sqlserver系统要求用分隔符隔开各字段。一开始准备用toad查询出来在save as但发现表太多要一张一张表盯着而且由于详单数据量大查询导出占用了大量的客户端内存其它活干不了了。后来想用||,||的方式间隔sql语句中的字段这种方法虽然可以实现脚本导入但由于数据量大而sqlplus中pagesize毕竟有现在文本中会出现分隔符等问题清理起来很麻烦。最后搜到了一个比较不错的方法据说是tom写的。下面介绍下简单的方法。首先需要定义utl_file_dirSQL show parameter utlNAME TYPE VALUE------------------------------------ ----------- ------------------------------utl_file_dir string /oracle/script, /archivelog这里系统以前就指定了/archivelog作为目录(这个目录虽然足够很大不过大文件尽量不要放在这里比较危险我这里空间紧张也没有办法)然后需要创建一个directory方法很简单CREATE OR REPLACE DIRECTORY UTLDIR AS /archivelog;然后需要在查询用户下建个function了这个function是解决这个问题的核心CREATE function dump_csv( p_query in varchar2,p_separator in varchar2default ,,p_dir in varchar2 ,p_filename in varchar2 )return numberAUTHID CURRENT_USERisl_output utl_file.file_type;l_theCursor integer default dbms_sql.open_cursor;l_columnValue varchar2(2000);l_status integer;l_colCnt number default 0;l_separator varchar2(10) default ;l_cnt number default 0;beginl_output : utl_file.fopen( p_dir, p_filename, w );dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );for i in 1 .. 255 loopbegindbms_sql.define_column( l_theCursor, i,l_columnValue, 2000 );l_colCnt : i;exceptionwhen others thenif ( sqlcode -1007 ) then exit;elseraise;end if;end;end loop;dbms_sql.define_column( l_theCursor, 1, l_columnValue,2000 );l_status : dbms_sql.execute(l_theCursor);loopexit when ( dbms_sql.fetch_rows(l_theCursor) 0 );l_separator : ;for i in 1 .. l_colCnt loopdbms_sql.column_value( l_theCursor, i,l_columnValue );utl_file.put( l_output, l_separator ||l_columnValue );l_separator : p_separator;end loop;utl_file.new_line( l_output );l_cnt : l_cnt1;end loop;dbms_sql.close_cursor(l_theCursor);utl_file.fclose( l_output );return l_cnt;end dump_csv;/之后只需要使用命令SQL select dump_csv(select * from TEST,,,/archivelog,emp.csv) from dual;DUMP_CSV(SELECT*FROMTEST,,,/ARCHIVELOG,EMP.CSV)-------------------------------------------------------3查看/archivelog/emp.csv文件1,12,24,4这里要注意查询用户必须有对directory的读写权限grant read, write on directory utldir to rongyu;这个方法利用了utl_file和dbms_sql两个过程ps/sql本身的功能不容忽视啊。不过这里我还存在一个疑问对于utl_file.fopen的解释官方文档是这样描述的UTL_FILE.FOPEN (location IN VARCHAR2,filename IN VARCHAR2,open_mode IN VARCHAR2,max_linesize IN BINARY_INTEGER)RETURN file_type;ParametersTable 95-3 FOPEN Function ParametersParameterDescriptionlocationDirectory location of file.filenameFile name, including extension (file type), without directory path. In Unix, the filename cannot end with /.open_modeSpecifies how the file is opened. Modes include:r--read textw--write texta--append textIf you try to open a file that does not exist using a value for open_mode, then the file is created in write mode.max_linesizeMaximum number of characters per line, including the newline character, for this file. (minimum value 1, maximum value 32767). The default is approximately 1000 bytes.也就是说例子里UTL_FILE.FOPEN中p_dir路径应该是以文件目录位置名作为导入变量但有人说要指定directory才有效这里我发现用utldir是无效的SQL select dump_csv(select * from TEST,,,utldir,emp.csv) from dual;select dump_csv(select * from TEST,,,utldir,emp.csv) from dual*ERROR at line 1:ORA-29280: invalid directory pathORA-06512: at SYS.UTL_FILE, line 18ORA-06512: at SYS.UTL_FILE, line 424ORA-06512: at RONGYU.DUMP_CSV, line 17ORA-06512: at line 1不知道是不是版本问题造成的不同还是其它什么原因这里还需要打个问号先
