除了通过plsql以及spool功能,还可以通过utl_file包进行数据的导出
#首先声明存储生成txt文件的目录,注意oracle要有写权限,其权限不能由自己赋给自己,必须由sysbas赋给
#首先声明存储生成txt文件的目录,注意oracle要有写权限,其权限不能由自己赋给自己,必须由sysbas赋给
create directory DIR_DUMP as '/home/oracle/';
conn / as sysdba
grant read,write on directory dir_dump to psbc; (或者是public;)
然后是写过程
###########################################################
CREATE OR REPLACE PROCEDURE xixi
IS
testjiao_handle UTL_FILE.file_type;
BEGIN
testjiao_handle := UTL_FILE.FOPEN('DIR_DUMP','testjiao.txt','w');
FOR x IN (SELECT * FROM TESTJIAO) LOOP
UTL_FILE.PUT_LINE(testjiao_handle,x.ID || ',' || x.RQ ||',');
END LOOP;
UTL_FILE.FCLOSE(testjiao_handle);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));
END;
/
###########################################################
exec xixi
执行完之后可以看到数据目录下 有对应的文件。
至于文件设定目录,可以查询dba_directories查看所有directory.
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS UTL_FILE_DIR /opt/oracle/utl_file
SYS BDUMP_DIR /opt/oracle/admin/conner/bdump
SYS EXP_DIR /opt/oracle/utl_file
可以使用drop directory删除这些路径.
SQL> drop directory exp_dir;
Directory dropped www.2cto.com
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS UTL_FILE_DIR /opt/oracle/utl_file
SYS BDUMP_DIR /opt/oracle/admin/conner/bdump