Querying Data From Flat Files in Oracle

Couple of days before i had an interesting discussion with my client related to Oracle external tables,

Client: Is there any way to read the data from flat file(s) which are stored outside the database?

Raja: Could you please explain in more detail?

Client: Sure. We have data analytics team, what they will do is, they will export the file(s) (.csv/.txt) from their system and push it to our file system (Oracle directory). 

They have a job to push their files to our file system, which will run in predefined interval. Once it's uploaded we should read the file(s) data and display it in our system.

Raja: Yes! it's very much possible, if we go with Oracle external table.

Client: What is that?

Raja: It can be used for query, join and sort operations. External tables allow oracle to query data that is stored outside the database in flat files. Once it's running we can create views against it.

Client: Oh! Sounds interesting. That's what i needed. Is there any limitations on external tables?

Raja: Yes. No DML can be performed on external tables.

Client: That's Ok. It's not needed at all, since we are not going to perform any DML operation. So you please go ahead.

-----------------------------------------------------------

Here I am giving an example to read the data that is stored outside the database in flat files.

Step 1: File(s) should be saved to the file system available to the Oracle server.

Fig 1: Sample load file data (.CSV)

Fig 2: Sample load file data (.TXT)

Step 2: Create a directory, that should pointing to the location of the file.

Step 3: Understand the load file structure, which oracle external table going to read.

Step 4: Create the external table as per the load file structure.

CREATE TABLE fxgn_emp_ext_data

  (

    empno      NUMBER,

    ename      VARCHAR2(240),

    JOB        VARCHAR2(240),

    department VARCHAR2(240),

    LOCATION   VARCHAR2(240),

    manager    VARCHAR2(240),

    hiredate   DATE,

    salary     NUMBER,

    commission NUMBER

  )

  ORGANIZATION EXTERNAL

  (

    TYPE oracle_loader 

    DEFAULT DIRECTORY APEXUAT -- put your directory name here

    ACCESS PARAMETERS (

                        fields terminated BY ',' optionally enclosed BY '"'

                        missing field VALUES are NULL 

                      ( empno,

                        ename,

                        JOB,

                        department,

                        LOCATION,

                        manager,

                        hiredate,

                        salary,

                        commission) 

                      ) 

    LOCATION ('employee_details.csv') 

-- put your file(s) name here, which should be saved in appropriate directory

  )

  REJECT LIMIT UNLIMITED;

Step 6: Query the external table the following result will be displayed.

Fig 3: Output

Note: Once external table is created, it can be queried like a regular table.

ORA-29913: If the load file have not been saved in the appropriate directory the following result will be displayed.

Fig 4: ORA-29913

That's it. This is mind blowing.

Happy APEXing!!!...

References:

• External Tables: Querying Data From Flat Files in Oracle