目录和外部表是Oracle数据仓库中获取外部文件数据一个比较重要的管理.
[@more@]创建外部目录并赋予相应的读写权限
SQL>CREATE OR REPLACE DIRECTORY source_dir as 'C:/UserDefine/oracle/oradata/source'; Directory created. SQL>CREATE OR REPLACE DIRECTORY target_dir as 'C:/UserDefine/oracle/oradata/target'; Directory created. SQL>CREATE OR REPLACE DIRECTORY log_dir as 'C:/UserDefine/oracle/oradata/log'; Directory created. SQL>grant read on directory source_dir to wbq; Grant succeeded. SQL>grant write on directory target_dir to wbq; Grant succeeded. SQL>grant write on directory log_dir to wbq; Grant succeeded. |
在源目录中创建并写入一个文件
declare |
从源目录中读取一个文件
declare |
通过数据字典查看目录
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ------------------------------ ------------- SYS MEDIA_DIR C:/UserDefine/oracle/ora92/demo/schema/product_media/ SYS LOG_FILE_DIR C:/UserDefine/oracle/ora92/demo/schema/log/ SYS DATA_FILE_DIR C:/UserDefine/oracle/ora92/demo/schema/sales_history/ SYS SOURCE_DIR C:/UserDefine/oracle/oradata/source SYS TARGET_DIR C:/UserDefine/oracle/oradata/target SYS LOG_DIR C:/UserDefine/oracle/oradata/log
6 rows selected |
在源目录中创建一个Student.txt的文件
Create a Text File, C:/UserDefine/oracle/oradata/source/student.txt, Content is below wbq English 70 wbq Maths 75 wbq History 90 |
定义外部表并读取相应的数据
SQL> CREATE TABLE Student 2 ( 3 StudName VARCHAR2(20), 4 ExamName VARCHAR2(20), 5 Score INTEGER 6 ) 7 ORGANIZATION EXTERNAL 8 ( 9 TYPE ORACLE_LOADER 10 DEFAULT DIRECTORY source_dir 11 ACCESS PARAMETERS 12 ( 13 records delimited by newline 14 badfile log_dir:'bad_student.dat' 15 logfile log_dir:'student.log' 16 fields terminated by ',' 17 missing field values are null 18 ( 19 StudName, ExamName, Score ) 20 ) 21 LOCATION ('student.txt') 22 ) 23 REJECT LIMIT UNLIMITED;
Table created
SQL> select * from student;
STUDNAME EXAMNAME SCORE -------------------- -------------------- --------------------------------------- wbq English 70 wbq Maths 75 wbq History 90
SQL> |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8128313/viewspace-913928/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8128313/viewspace-913928/