Monday, May 12, 2014

External tables:

External tables: querying data from flat files in oracle external tables allow oracle to query data that is stored outside the database in flat files. The oracle_loader driver can be used to access any data stored in any format that can be loaded by sql*loader. No dml can be performed on external tables but they can

be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the etl process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.
Basic usage
Querying the alert log
11gr2 update
Related articles.
External tables containing lob data
Basic usage
Download the files (countries1.txt, countries2.txt) containing the data to be queried. In this example the data is split across two files which should be saved to a filesystem available to the oracle server.
Create a directory object pointing to the location of the files.
Create or replace directory ext_tab_data as '/data';
Ed by newline
        fields terminated
By ','
        missing field
Values are null
        (
Country_code      char(5),
Country_name      char(50),
Create the external table using the create table..organization external syntax. This defines the metadata for the table describing how it should appear and how the data is loaded.
    create table countries_ext (
      country_code      varchar2(5),
      country_name      varchar2(50),
      country_language  varchar2(50)
    )
    organization external (
      type oracle_loader
      default directory ext_tables
      access parameters (
        records delimi
T country_language  char(50)
        )
      )
      location ('countries1.txt','countries2.txt')
    )
    parallel 5
    reject limit unlimited;
Once the external table created, it can be queried like a regular table.
    sql> select *
      2  from   countries_ext
      3  order by country_name;
    count country_name                 country_language
    ----- ---------------------------- -----------------------------
    eng   england                           english
    fra     france                              french
    ger   germany                          german
    ire    ireland                             english
: at line 1
    sql>
Once the table is funct
Sco   scotland                               english
usa   unites states of america     english
wal   wales                                     welsh
    7 rows selected.
    sql>
If the load files have not been saved in the appropriate directory the following result will be displayed.
    sql> select *
      2  from   countries_ext
      3  order by country_name;
    select *
    *
    error at line 1:
    ora-29913: error in executing odciexttableopen callout
    ora-29400: data cartridge error
    kup-04040: file countries1.txt in ext_tables not found
    ora-06512: at "sys.oracle_loader", line 14
    ora-0651
Jioning correctly we can create views against it.
    create or replace view english_speaking_countries as
      select *
      from   countries_ext
      where  country_language = 'english'
      order by country_name;
    select *
    from   english_speaking_countries;
    count country_name                 language
    ----- ---------------------------- ----------------------------
    eng   england                               english
    ire   ireland                                   english
    sco   scotland                              english
    usa   unites states of america    english
    4 rows selected.

    sql>
Trim whitespace same as sql loader      
        country_n
A log of load operations is created in the same directory as the load files.
     log file opened at 10/15/02 14:06:44
    field definitions for table countries_ext     
      record format delimited by newline          
      data in file has same endianness as the platform
      rows with all null fields are accepted     
        fields in data source:                 
        country_code                    char (5)  
          terminated by ","                   
 ame                    char (50)  
          terminated by ","                       
          trim whitespace same as sql loader      
        country_language                char (50) 
          terminated by ","                       
          trim whitespace same as sql loader
Querying the alert log
The following example shows how an external table can be used to query the contents of the alert log.
    create or replace directory bdump as '/u01/app/oracle/admin/sid/bdump/';
    drop table alert_log;
    create table alert_log (
      line  varchar2(4000)
    )
    organization external
    (
      type oracle_loader
      default directory bdump
Specially useful for re
Access parameters
      (
        records delimited by newline
        badfile bdump:'read_alert_%a_%p.bad'
        logfile bdump:'read_alert_%a_%p.log'
        fields terminated by '~'
        missing field values are null
        (
          line  char(4000)
        )
      )
      location ('alert_sid.log')
    )
    parallel 10
    reject limit unlimited
    /
    set linesize 1000
    select * from alert_log;
11gr2 updates
Oracle 11g release 2 introduced the preprocessor clause to identify a directory object and script used to process the files before they are read by the external table. This feature was backported to 11gr1 (11.1.0.7). The preprocessor clause is
Eading compressed files, since they are unzipped and piped straight into the external table process without ever having to be unzipped on the file system.
    create or replace directory exec_dir as '/bin';
    create table countries_ext (
      country_code      varchar2(5),
      country_name      varchar2(50),
      country_language  varchar2(50)
    )
    organization external (
      type oracle_loader
      default directory ext_tables
      access parameters (
        records delimited by newline
        preprocessor exec_dir:'gunzip' options '-c'
        fields terminated by ','
        missing field values are null
        (
          country_code      char(5),
Country_name      char(50),
          country_language  char(50)
        )
      )
      location ('countries1.txt.gz','countries2.txt.gz')
    )

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...