Solved

external tables in oracle 9i

Posted on 2004-08-06
2
1,732 Views
Last Modified: 2013-12-11
Hi....
  I want to test how can i read flat file by using external tables, I read some articles about this feature , but when i tried to test the tables, it's always give me an error " can not locate log file" , actually , I created flat file on my machnine and try to run the script from the server, I dont know if I should put the  fiels on db server?
one more thing , is this external tables read only flat files , can I use them to read dbase files , excel sheet files ?????

and what is the best practice of using this feature?

many thanks
regards
0
Comment
Question by:NHA
2 Comments
 
LVL 4

Accepted Solution

by:
ramumorla earned 100 total points
ID: 11746159
In Oracle 9i, the concept of external tables is introduced.  External tables are flat files on file system that can be browsed within  database as ordinary tables.  This feature can be used to load data into database from flat files without  using SQL*Loader utility. There are some advantages to use external tables for  load jobs. SQL*Loader can also benefit from the advantages of external tables  by the EXTERNAL_TABLE command-line parameter.  The value of EXTERNAL_TABLE command-line parameter can be one of the following:   - NOT_USED      This is the default value. In this case the load is done using                   conventional or direct path mode.   - GENERATE_ONLY The load job is not performed. Instead, all of the SQL                   statements to be executed for the whole load job are written                    to the specified SQL*Loader log file. The load can be done                   later by executing the statements in the log file.   - EXECUTE       The load job is performed using external tables by executing                   SQL statements. If any of the statements fail and return an                   error, the attempt to load stops. Statements are always placed                   in the log file except the directory statements as they are                    executed.   If the EXECUTE or GENERATE_ONLY option is specified, the userid that loads   should be granted the following privileges:  - CREATE ANY DIRECTORY  - DROP ANY DIRECTORY   Advantages of Using External Tables for SQL*Loader Jobs -------------------------------------------------------   Performance    -----------   - Load jobs can benefit from the parallel access of external tables. You can     perform the load after generating the SQL statements using GENERATE_ONLY by     modifying the SQL statements as follows:         --> Setting the parallel degree of the external table:           CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"             (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13))          ORGANIZATION external (TYPE oracle_loader            DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000            ACCESS PARAMETERS (              RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P9              BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ulcase1.bad'              LOGFILE 'ulcase1.log_xt' READSIZE 1048576 SKIP 20              FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM              REJECT ROWS WITH ALL NULL FIELDS (                DEPTNO CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',                DNAME CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',                LOC CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'))            location ('ulcase1.ctl'))REJECT LIMIT UNLIMITED PARALLEL 5;       --> Providing parallel hint to the insert statement:           INSERT /*+ append parallel */ INTO DEPT (DEPTNO, DNAME, LOC)             SELECT DEPTNO, DNAME, LOC             FROM "SYS_SQLLDR_X_EXT_DEPT";      Although parallel loads can be done without using external tables by means     of parallel direct load, this method has some restrictions (Please see      [NOTE:191538.1]):       --> Neither local or global indexes can be maintained by the load.      --> Referential integrity and CHECK constraints must be disabled.      --> Triggers must be disabled.      --> Rows can only be appended. REPLACE, TRUNCATE, and INSERT cannot be used         (this is due to the individual loads not being coordinated). If you          must truncate a table before a parallel load, you must do it manually.    - The external tables used for SQL*Loader are of type ORACLE_LOADER access     driver which takes advantage of multithreading to streamline the work as     much as possible. This is not available with usual loads.    Flexibility    -----------   After generating the SQL statements with EXTERNAL_TABLE=GENERATE_ONLY, the   statements can be modified to do intermediate/post processing using SQL with    full availability. Assume that you want to convert the locations to be loaded   in the DEPT table to InitCapped, modify the INSERT statement as follows:           INSERT /*+ append */ INTO DEPT (DEPTNO, DNAME, LOC)             SELECT DEPTNO, DNAME, INITCAP(LOC)             FROM "SYS_SQLLDR_X_EXT_DEPT";    NLS Issues   ----------   One of the main issues about SQL*Loader is the client NLS environment settings.   ([NOTE:110232.1], [NOTE:100033.1], etc.). When SQL*Loader utility is used with    EXTERNAL_TABLES=EXECUTE, the environment settings does not apply, only the NLS   settings on the server are applied.    Sequences   ---------   With usual loads, the sequences used are advanced even if a row to be loaded   is rejected. This causes gaps in the sequences. With external tables, rejected   rows do not update the sequences therefore do not cause gaps.    Other   -----   The most popular reason to use external tables in database is to enable users   to use data stored in flat files without storing them into database. It is    sometimes not so easy to use correct access parameters for a datafile. If you    have such a problem, use the EXTERNAL_TABLE=GENERATE_ONLY switch on with the    SQL*Loader. This creates the SQL statements and writes them to a log file    without executing them, so that you can see how a SQL*Loader control file can   be translated into the access parameters for an external table.   Disadvantage of Using External Tables for SQL*Loader Jobs --------------------------------------------------------- In an external table load for which the datafile character set is UTF8 or UTF16,  it is not possible to suppress checking for byte-order marks. The suppression is only necessary if the beginning of the datafile contains binary data that  matches the byte-order mark encoding. It is possible to suppress byte-order mark checking with usual loads.   Example -------  $ cd $ORACLE_HOME/rdbms/demo   $ sqlldr scott/tiger CONTROL=ulcase1.ctl EXTERNAL_TABLE=GENERATE_ONLY \    LOG=ulcase1.log  The produced ulcase1.log file is: ----------------------- BEGIN ulcase1.log -------------------------------- SQL*Loader: Release 9.2.0.1.0 - Production on Thu Sep 12 14:48:20 2002  Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.  Control File:   ulcase1.ctl Data File:      ulcase1.ctl   Bad File:     ulcase1.bad   Discard File:  none specified    (Allow all discards)  Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation:    none specified Path used:      External Table  Table DEPT, loaded from every logical record. Insert option in effect for this table: INSERT     Column Name                  Position   Len  Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO                              FIRST     *   ,  O(") CHARACTER             DNAME                                NEXT     *   ,  O(") CHARACTER             LOC                                  NEXT     *   ,  O(") CHARACTER                CREATE DIRECTORY statements needed for files ------------------------------------------------------------------------ CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/d08/app/oracle/product/9.2.0/rdbms/demo'   CREATE TABLE statement for external table: ------------------------------------------------------------------------ CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"  (   DEPTNO NUMBER(2),   DNAME VARCHAR2(14),   LOC VARCHAR2(13) ) ORGANIZATION external  (   TYPE oracle_loader   DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000   ACCESS PARAMETERS    (     RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P9     BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ulcase1.bad'     LOGFILE 'ulcase1.log_xt'     READSIZE 1048576     SKIP 20     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM      REJECT ROWS WITH ALL NULL FIELDS      (       DEPTNO CHAR(255)         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',       DNAME CHAR(255)         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',       LOC CHAR(255)         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'     )   )   location    (     'ulcase1.ctl'   ) )REJECT LIMIT UNLIMITED   INSERT statements used to load internal tables: ------------------------------------------------------------------------ INSERT /*+ append */ INTO DEPT  (   DEPTNO,   DNAME,   LOC ) SELECT    DEPTNO,   DNAME,   LOC FROM "SYS_SQLLDR_X_EXT_DEPT"   statements to cleanup objects created by previous statements: ------------------------------------------------------------------------ DROP TABLE "SYS_SQLLDR_X_EXT_DEPT" DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000    Run began on Thu Sep 12 14:48:20 2002 Run ended on Thu Sep 12 14:48:29 2002  Elapsed time was:     00:00:08.80 CPU time was:         00:00:00.15  ----------------------- END ulcase1.log --------------------------------  To manually execute the statements in the log file add semicolons (';')  at the end of each statement and execute them:   $ sqlplus "/ as sysdba"  SQL> GRANT CREATE ANY DIRECTORY TO SCOTT;  SQL> GRANT DROP ANY DIRECTORY TO SCOTT;  SQL> connect scott/tiger  SQL> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS         '/d08/app/oracle/product/9.2.0/rdbms/demo';  SQL> CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"          (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13))       ORGANIZATION external (TYPE oracle_loader         DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000         ACCESS PARAMETERS (           RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P9           BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ulcase1.bad'           LOGFILE 'ulcase1.log_xt' READSIZE 1048576 SKIP 20           FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM           REJECT ROWS WITH ALL NULL FIELDS (             DEPTNO CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',             DNAME CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',             LOC CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'))         location ('ulcase1.ctl'))REJECT LIMIT UNLIMITED;  SQL> INSERT /*+ append */ INTO DEPT (DEPTNO, DNAME, LOC)          SELECT DEPTNO, DNAME, INITCAP(LOC)          FROM "SYS_SQLLDR_X_EXT_DEPT";  SQL> DROP TABLE "SYS_SQLLDR_X_EXT_DEPT";  SQL> DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000;   Known Issues ------------                "*.BAD" FILE [BUG:2247233] SQLLDR DOES NOT GENERATE LOGFILE WITH EXTERNAL_TABLE=GENERATE_ONLY [BUG:2227237] ORA-29913,ORA-29400,KUP-00554,KUP-01005 DURING EXTERNAL TABLE                QUERY
0
 
LVL 14

Expert Comment

by:leoahmad
ID: 11749169
 1* create directory md as 'e:\q'
SQL> /

Directory created.

SQL> create table mytab(a number,b number, c varchar2(2000))
  2  Organization external
  3  (type oracle_loader
  4  default directory md
  5  access parameters
  6  (fields terminated by '.')
  7  location ('1.txt')
  8  )
  9  /

Table created.

SQL> select * from mytab;

         A          B
---------- ----------
C
--------------------------------------------------------------------------------
         1          1
  In the name of ALLAH, the Gracious, the Merciful

         1          2
  All praise is due to ALLAH alone, Lord of all the worlds

         1          3
  The Gracious, the Merciful


         A          B
---------- ----------
C
--------------------------------------------------------------------------------
         1          4
  Master of the Day of Judgment

         1          5
  THEE alone do we worship and THEE alone do we implore for help

         1          6
  Guide us in the straight path,


         A          B
---------- ----------
C
--------------------------------------------------------------------------------
         1          7
  The path of those on whom THOU hast bestowed THY favours, those who have not i
ncurred THY displeasure and those who have not gone astray


7 rows selected.

SQL>
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now