Solved

External tables with undelimited fields

Posted on 2002-06-20
5
1,950 Views
Last Modified: 2012-05-04
At the Oracle site there is an example of external tables.
Part of that SQL+ session states

FIELDS TERMINATED BY "|" LDRTRIM  .
The file i am trying to use is downloaded from a mainframe and there is nothing terminating each field.
If i just leave that line out it assumes each field is larger that 255 and sees nothing.
What should i use here?

http://otn.oracle.com/products/oracle9i/htdocs/9iobe/OBE9i-Public/
http://otn.oracle.com/products/oracle9i/htdocs/9iobe/OBE9i-Public/

High-speed Data Loading and Rolling Window Operations
.
   From a SQL*Plus session logged on to the SH schema, run @create_external_table.sql.
   The results are as follows:

   @create_external_table

   set echo on

   Rem *****
   Rem CREATE EXTERNAL TABLE
   Rem *****

   CREATE TABLE sales_delta_xt
     (
      prod_id NUMBER(6),
      cust_id NUMBER,
      time_id DATE,
      channel_id CHAR(1),
      promo_id NUMBER(6),
      quantity_sold NUMBER(3),
      amount_sold NUMBER(10,2)
     )
     ORGANIZATION external
     (
      TYPE oracle_loader
      DEFAULT DIRECTORY data_dir
      ACCESS PARAMETERS
       (
        RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
        BADFILE log_dir:'sh_sales_%p.bad'
        LOGFILE log_dir:'sh_sales_%p.log_xt'
        FIELDS TERMINATED BY "|" LDRTRIM
       )
      location
       (
        'salesDelta.dat'
       )
      )REJECT LIMIT UNLIMITED NOPARALLEL;
0
Comment
Question by:xoxomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
pennnn earned 100 total points
ID: 7097818
I think the format should be the same as in a SQL*Loader control file. You can specify the field positions instead of a delimiter. Here'a an example from the Oracle manual about the SQL*Loader control file format (only the field descriptions):
deptno POSITION(1:2)  INTEGER EXTERNAL(2)
              NULLIF deptno=BLANKS,
       job    POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE
              NULLIF job=BLANKS  "UPPER(:job)",
       mgr    POSITION(28:31) INTEGER EXTERNAL
              TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
       ename  POSITION(34:41) CHAR
              TERMINATED BY WHITESPACE  "UPPER(:ename)",
       empno  POSITION(45) INTEGER EXTERNAL
              TERMINATED BY WHITESPACE,
       sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE
              "TO_NUMBER(:sal,'$99,999.99')",
       comm   INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
              ":comm * 100"

So iin your case the command should be something like that:
CREATE TABLE sales_delta_xt
    (
     prod_id NUMBER(6),
     cust_id NUMBER,
     time_id DATE,
     channel_id CHAR(1),
     promo_id NUMBER(6),
     quantity_sold NUMBER(3),
     amount_sold NUMBER(10,2)
    )
    ORGANIZATION external
    (
     TYPE oracle_loader
     DEFAULT DIRECTORY data_dir
     ACCESS PARAMETERS
      (
       RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
       BADFILE log_dir:'sh_sales_%p.bad'
       LOGFILE log_dir:'sh_sales_%p.log_xt'
       (
        prod_id POSITION(1:6) INTEGER,
-- you'll have to calculate the positions of the rest of the fields
-- because you haven't specified the length of the second column
-- and I have no idea what it is :)
        cust_id POSITION(?:?) INTEGER,
        time_id POSITION(?:?) DATE,
        channel_id POSITION(?:?) CHAR,
        promo_id POSITION(?:?) INTEGER,
        quantity_sold POSITION(?:?) INTEGER,
        amount_sold POSITION(?:?) FLOAT
       )
      )
     location
      (
       'salesDelta.dat'
      )
     )REJECT LIMIT UNLIMITED NOPARALLEL;

Hope that helps!
0
 

Author Comment

by:xoxomos
ID: 7097846
Got a good feeling about this.  Will try it tomorrow afternoon.  Thanks.
0
 
LVL 2

Expert Comment

by:333
ID: 7097909
just check out that fields in file are always of the same (fixed) length. i.e. if field PROMO_ID=3 then number 3 should be left or right padded with 5 spaces.
0
 
LVL 2

Expert Comment

by:333
ID: 7097914
in case your records in file aren't fixed length, they may be separated with spaces. then you can change TERMINATED BY "|" to TERMINATED BY " " (empty space between "")
0
 

Author Comment

by:xoxomos
ID: 7099416
thought i clicked this thing last night.  Will try your solution this afternoon.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

696 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