108 - [Oracle 19C] External Table - ORACLE_LOADER

 

-now we will learn how to create external tables
-external table is read only table whose metadata is stored in the Db, 
-but whose data is stored outside the db.
-no DML allowed, no indexes can be created on external tables
-you can access the data with 2 methods (oracle_loader or oracle_datapump )
-to read external data, first you need to create directory in the database

 

conn sqlplus sys as sysdba
alter session set container=orclpdb;
grant create any DIRECTORY to hr;

 

 

 

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 20 20:37:09 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=orclpdb;

Session altered.

SQL> grant create any directory to hr;

Grant succeeded.

 

 

 

# now go to server /u01/app/oracle/pump and create old_emp_data.csv
# put these values
# 3번 라인의 마지막에는 ',' 뒤에 공백이 있어야 한다.

/*
1,khaled,ali
2,lana,diaz
3,lord, 
4,meme,"moon,f"
*/



[oracle@test pump]$ pwd
/u01/app/oracle/pump

[oracle@test pump]$ touch old_emp_data.csv
[oracle@test pump]$ chmod 755 old_emp_data.csv
[oracle@test pump]$ ll
합계 0
-rwxr-xr-x 1 oracle oinstall 0 12월 20 21:09 old_emp_data.csv
[oracle@test pump]$

# 3번 라인의 마지막에는 ',' 뒤에 공백이 있어야 한다.
[oracle@test pump]$ cat old_emp_data.csv 
1,khaled,ali
2,lana,diaz
3,lord, 
4,meme,"moon,f"

 

 

 

# hr 사용자로 로그인.

show user
show con_name
/*
USER is "HR"
CON_NAME 
------------------------------
ORCLPDB
*/



# hr 유저는 이미 "create any directory" 권한을 받았기 때문에 아래 명령어 실행 가능.
CREATE OR REPLACE DIRECTORY EMP_DIR
AS '/u01/app/oracle/pump';
/*
Directory EMP_DIR created.
*/



SELECT * FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME = 'EMP_DIR';
/*
OWNER      DIRECTORY_NAME  DIRECTORY_PATH                 ORIGIN_CON_ID
---------- --------------- ------------------------------ -------------
SYS        EMP_DIR         /u01/app/oracle/pump                       3
*/



CREATE TABLE EMP_LOAD_ext
     (EMPLOYEE_NUMBER      NUMBER,
      FNAME   VARCHAR2(100),
      LNAME   VARCHAR2(100)
      )
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY EMP_DIR
      ACCESS PARAMETERS
        (RECORDS DELIMITED BY NEWLINE
         FIELDS TERMINATED BY ','  optionally enclosed by '"' 
        )
      LOCATION ('old_emp_data.csv')
     )
     reject limit unlimited;
/*
Table EMP_LOAD_EXT created.
*/



SELECT * FROM EMP_LOAD_EXT;
/*
EMPLOYEE_NUMBER FNAME                LNAME               
--------------- -------------------- --------------------
              1 khaled               ali                 
              2 lana                 diaz                
              3 lord                                     
              4 meme                 moon,f 
*/


# External Table 은 삭제나 수정이 불가능하다
DELETE EMP_LOAD_EXT; --dml operation not supported on external organized table
/*
Error starting at line : 142 in command -
DELETE EMP_LOAD_EXT
Error at Command Line : 142 Column : 8
Error report -
SQL Error: ORA-30657: operation not supported on external organized table
30657.0000 -  "operation not supported on external organized table"
*Cause:    User attempted on operation on an external table which is
           not supported.
*Action:   Don''t do that!
*/



# go to the file old_emp_data.csv and add this
# 200, dana,ford
# then


SELECT * FROM EMP_LOAD_EXT;
/*
EMPLOYEE_NUMBER FNAME                LNAME               
--------------- -------------------- --------------------
              1 khaled               ali                 
              2 lana                 diaz                
              3 lord                                     
              4 meme                 moon,f              
            200 dana                 ford 
*/



# go to the file old_emp_data.csv and change the id 1 to be xx

SELECT * FROM EMP_LOAD_EXT;
/*
EMPLOYEE_NUMBER FNAME                LNAME               
--------------- -------------------- --------------------
              2 lana                 diaz                
              3 lord                                     
              4 meme                 moon,f              
            200 dana                 ford  
            


[oracle@test pump]$ cat *.bad
x,khaled,ali


*/


# external table 을 읽을때마다(select 할때마다) log 파일에 오류여부 등 기록이 추가된다.