Oracle學習系列—資料倉儲部分—目錄管理和外部表

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
fhandle utl_file.file_type;
begin
fhandle := utl_file.fopen('SOURCE_DIR', 'example.txt', 'w');
utl_file.put_line(fhandle , 'wbq test write one');
utl_file.put_line(fhandle , 'wbq test write two');
utl_file.fclose(fhandle);
end;

declare
fhandle utl_file.file_type;
fp_buffer varchar2(4000);
begin
fhandle := utl_file.fopen ('SOURCE_DIR','example.txt', 'R');
utl_file.get_line (fhandle , fp_buffer );
dbms_output.put_line(fp_buffer );
utl_file.get_line (fhandle , fp_buffer );
dbms_output.put_line(fp_buffer );
utl_file.fclose(fhandle);
end;

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

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>