Oracle:关于UTL_FILE的初阶使用(一)
如题,本文呈现UTL_FILE的基本使用。
1.目录对象
在通过SQL*Plus在Oracle数据库中创建目录对象(Directory Object)之前,需要在操作系统上确认目标路径存在,如果不存在,需要创建,语法:mkdir dir_path
创建完文件系统路径后,还需要注意拥有人、拥有组,以及权限的正确、适当。
否则,在后面使用“UTL_FILE”的时候会报错。
使用PL/SQL包:UTL_FILE之前,需要创建目录对象:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[oracle@rac2 ~]$ ps -ef | grep pmon oracle 6106 1 0 19:25 ? 00:00:00 asm_pmon_+ASM2 oracle 6343 1 0 19:25 ? 00:00:00 ora_pmon_orcl2 oracle 32532 19723 0 21:40 pts/1 00:00:00 grep pmon [oracle@rac2 ~]$ env | grep SID ORACLE_SID=orcl2 [oracle@rac2 ~]$ [oracle@rac2 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Mon Oct 6 21:40:17 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> col directory_name for a20 SQL> col directory_path for a30 SQL> col owner for a12 SQL> set linesize 300 SQL> set pagesize 300 SQL> SQL> select * from dba_directories where directory_path='/home/oracle'; OWNER DIRECTORY_NAME DIRECTORY_PATH ------------ -------------------- ------------------------------ SYS PIC_LOCATION /home/oracle SYS UTL_FILE_DIR /home/oracle SQL> |
如上所示,我的环境中已经有了目录对象。
如果没有,则需要创建,语法:
create directory dir_name as ‘dir_path’;
如果使用目录对象的用户不是sys,那么还需要授权:
grant read,write on directory dir_name to user_name;
grant read,write on directory dir_name to public;
2.执行UTL_FILE包的权限
如果操作永福不是sys,则需要授权:
grant execute on utl_file to user_name;
3.使用UTL_FILE
代码:
DECLARE
v_filename utl_file.file_type;
v_directory varchar2(50) := ‘UTL_FILE_DIR’;
BEGIN
— Now Open the File
v_filename := utl_file.fopen(v_directory,’scott_objects.txt’,’a’);
dbms_output.put_line(‘### The File has been OPENED. [DBMS OUTPUT] ###’);
utl_file.put_line(v_filename,’### The File has been OPENED. [DBMS OUTPUT] ###’);
for middle_data in (select owner,object_id,object_name,object_type,status,to_char(created,’yyyy-mon-dd hh24:mi:ss’) created,to_char(last_ddl_time,’yyyy-mon-dd hh24:mi:ss’) last_ddl_time from dba_objects where owner=’SCOTT’ order by 2)
loop
utl_file.put_line(v_filename,middle_data.owner || ‘ – ‘ || middle_data.object_id || ‘ – ‘ || middle_data.object_name || ‘ – ‘ || middle_data.object_type || ‘ – ‘ || middle_data.status || ‘ – ‘ || middle_data.created || ‘ – ‘ || middle_data.last_ddl_time);
end loop;
utl_file.fclose(v_filename);
end;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
[oracle@rac2 ~]$ ll total 4 -rw-r--r-- 1 oracle oinstall 840 Oct 6 21:44 output.log [oracle@rac2 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Mon Oct 6 21:58:45 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> DECLARE 2 v_filename utl_file.file_type; 3 v_directory varchar2(50) := 'UTL_FILE_DIR'; 4 BEGIN 5 -- Now Open the File 6 v_filename := utl_file.fopen(v_directory,'scott_objects.txt','a'); 7 dbms_output.put_line('### The File has been OPENED. [DBMS OUTPUT] ###'); 8 utl_file.put_line(v_filename,'### The File has been OPENED. [DBMS OUTPUT] ###'); 9 for middle_data in (select owner,object_id,object_name,object_type,status,to_char(created,'yyyy-mon-dd hh24:mi:ss') created,to_char(last_ddl_time,'yyyy-mon-dd hh24:mi:ss') last_ddl_time from dba_objects where owner='SCOTT' order by 2) 10 loop 11 utl_file.put_line(v_filename,middle_data.owner || ' - ' || middle_data.object_id || ' - ' || middle_data.object_name || ' - ' || middle_data.object_type || ' - ' || middle_data.status || ' - ' || middle_data.created || ' - ' || middle_data.last_ddl_time); 12 end loop; 13 utl_file.fclose(v_filename); 14 end; 15 / PL/SQL procedure successfully completed. SQL> show errors; No errors. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options [oracle@rac2 ~]$ [oracle@rac2 ~]$ ll total 8 -rw-r--r-- 1 oracle oinstall 840 Oct 6 21:44 output.log -rw-r--r-- 1 oracle oinstall 840 Oct 6 21:58 scott_objects.txt [oracle@rac2 ~]$ [oracle@rac2 ~]$ cat scott_objects.txt ### The File has been OPENED. [DBMS OUTPUT] ### SCOTT - 51573 - DEPT - TABLE - VALID - 2010-apr-20 08:42:06 - 2010-apr-20 08:42:06 SCOTT - 51574 - PK_DEPT - INDEX - VALID - 2010-apr-20 08:42:07 - 2010-apr-20 08:42:07 SCOTT - 51575 - EMP - TABLE - VALID - 2010-apr-20 08:42:07 - 2010-apr-20 08:42:07 SCOTT - 51576 - PK_EMP - INDEX - VALID - 2010-apr-20 08:42:07 - 2010-apr-20 08:42:07 SCOTT - 51577 - BONUS - TABLE - VALID - 2010-apr-20 08:42:07 - 2010-apr-20 08:42:07 SCOTT - 51578 - SALGRADE - TABLE - VALID - 2010-apr-20 08:42:07 - 2010-apr-20 08:42:07 SCOTT - 53078 - PIC_DATA - TABLE - VALID - 2014-sep-28 21:16:25 - 2014-sep-28 21:16:25 SCOTT - 53079 - SYS_LOB0000053078C00003$$ - LOB - VALID - 2014-sep-28 21:16:25 - 2014-sep-28 21:16:25 SCOTT - 53173 - EXPORT_DATA - PROCEDURE - INVALID - 2014-oct-06 20:22:44 - 2014-oct-06 20:22:44 [oracle@rac2 ~]$ |
源数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> run 1* select owner,object_id,object_name,object_type,status,to_char(created,'yyyy-mon-dd hh24:mi:ss'),to_char(last_ddl_time,'yyyy-mon-dd hh24:mi:ss') from dba_objects where owner='SCOTT' order by 2 OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS TO_CHAR(CREATED,'YYY TO_CHAR(LAST_DDL_TIM ------------ ---------- ------------------------------ ------------ ------- -------------------- -------------------- SCOTT 51573 DEPT TABLE VALID 2010-apr-20 08:42:06 2010-apr-20 08:42:06 SCOTT 51574 PK_DEPT INDEX VALID 2010-apr-20 08:42:07 2010-apr-20 08:42:07 SCOTT 51575 EMP TABLE VALID 2010-apr-20 08:42:07 2010-apr-20 08:42:07 SCOTT 51576 PK_EMP INDEX VALID 2010-apr-20 08:42:07 2010-apr-20 08:42:07 SCOTT 51577 BONUS TABLE VALID 2010-apr-20 08:42:07 2010-apr-20 08:42:07 SCOTT 51578 SALGRADE TABLE VALID 2010-apr-20 08:42:07 2010-apr-20 08:42:07 SCOTT 53078 PIC_DATA TABLE VALID 2014-sep-28 21:16:25 2014-sep-28 21:16:25 SCOTT 53079 SYS_LOB0000053078C00003$$ LOB VALID 2014-sep-28 21:16:25 2014-sep-28 21:16:25 SCOTT 53173 EXPORT_DATA PROCEDURE INVALID 2014-oct-06 20:22:44 2014-oct-06 20:22:44 9 rows selected. SQL> |
————————————————————————————
Ending。
impdp 在运行的时候也会调用UTL_FILE的相关方法,所以,也有可能因为UTL_FILE的一些方法的异常,导致impdp导入失败。