oracle external table get selected columns from the file

Question

I'm having problems with oracle external table I have a text file will 50+ unnecessary columns, Now I want to select a few columns from the text file to my external table.

        create table tmpdc_ticket(
        SERVICE_ID CHAR(144),
        SERVICE_TYPE CHAR(50),
        CUSTOMER_NAME CHAR(200),
        TELEPHONE_NO CHAR(144),
        ACCOUNT_NUMBER CHAR(144),
        FAULT_STATUS CHAR(50),
        BUSINESS_GROUP CHAR(100)
    )
    organization external(
        type    oracle_loader
        default directory sample_directory
        access parameters(
            records delimited by newline
            nologfile
            skip 1
            fields terminated by '|'
            missing field values are null
                (SERVICE_ID CHAR(144),
                 SERVICE_TYPE CHAR(50),
                 CUSTOMER_NAME CHAR(200),
                 TELEPHONE_NO CHAR(144),
                 ACCOUNT_NUMBER CHAR(144),
                 FAULT_STATUS CHAR(50),
                 BUSINESS_GROUP CHAR(100)
            )
        )
        location(sample_directory:'sample_file.txt')
    )
    reject limit 1
    noparallel
    nomonitoring;

However it seems like the oracle_loader is doing inserting from the first column from my textfile. Is it possible to get already like for example the 3rd columns from the textfile?

score 5 · Accepted Answer · answered Mar 04 '15 at 11:47

If you have delimited fields in your file that you want to ignore, just specify them in the field list clause with a dummy field name, and don't include them in the table column list. This will ignore the first two fields in the file:

create table tmpdc_ticket(
    SERVICE_ID CHAR(144),
    SERVICE_TYPE CHAR(50),
    CUSTOMER_NAME CHAR(200),
    TELEPHONE_NO CHAR(144),
    ACCOUNT_NUMBER CHAR(144),
    FAULT_STATUS CHAR(50),
    BUSINESS_GROUP CHAR(100)
)
organization external(
    type    oracle_loader
    default directory sample_directory
    access parameters(
        records delimited by newline
        nologfile
        skip 1
        fields terminated by '|'
        missing field values are null
            (DUMMY_1,
             DUMMY_2,
             SERVICE_ID CHAR(144),
             SERVICE_TYPE CHAR(50),
             CUSTOMER_NAME CHAR(200),
             TELEPHONE_NO CHAR(144),
             ACCOUNT_NUMBER CHAR(144),
             FAULT_STATUS CHAR(50),
             BUSINESS_GROUP CHAR(100)
        )
    )
    location(sample_directory:'sample_file.txt')
)
reject limit 1
noparallel
nomonitoring;

So if you have a file containing (very contrived values in this case):

Header
dummy_1|dummy_2|service_id|service_type|customer_name|telephone_no|account_number|fault_status|business_group

Then the table will not see the first two fields, and will contain:

select * from tmpdc_ticket;

SERVICE_ID SERVICE_TYPE CUSTOMER_NAME TELEPHONE_NO ACCOUNT_NUMBER FAULT_STATUS BUSINESS_GROUP
---------- ------------ ------------- ------------ -------------- ------------ --------------
service_id service_type customer_name telephone_no account_number fault_status business_group

You can put more dummy fields in the middle of the field list, not just at the start, if you need to. And you can call them anything of course, as long as the names don't clash with fields you are using, so you might want to give them meaningful names to ease maintenance and make life easier if you decide you do want to include another one in the table columns later.

Incidentally, you probably want your table columns to be declared as VARCHAR2 rather than CHAR:

create table tmpdc_ticket(
    SERVICE_ID VARCHAR2(144),
    SERVICE_TYPE VARCHAR2(50),
    CUSTOMER_NAME VARCHAR2(200),
    TELEPHONE_NO VARCHAR2(144),
    ACCOUNT_NUMBER VARCHAR2(144),
    FAULT_STATUS VARCHAR2(50),
    BUSINESS_GROUP VARCHAR2(100)
)
organization external(
...

Thanks I never thought about that. :) – Zyrax Mar 05 '15 at 23:59 — Zyrax, Mar 05 '15 at 23:59

oracle external table get selected columns from the file

1 Answers1

Linked