gram77
asked on
Loading Fields initiated by Piped delimited data into external table.
Q1. How to display fields in external table whose delimiter starts with |
Normally we have data where the field is terminated with |, here i have a field started with |
Oracle treats COL1 as second column and so on. The last column is not shown.
Q2. When i load data from external table into actual table, i want to filter the header and footer data, and load only the piped delimited data. How do i do that?
The problem here is that even the header is delimited and i want to filter it
**************
DROP TABLE mytable CASCADE CONSTRAINTS;
CREATE TABLE mytable
(
col1 VARCHAR2(500 BYTE),
col2 VARCHAR2(500 BYTE),
col3 VARCHAR2(500 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY mydir
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
NODISCARDFILE
NOLOGFILE
FIELDS TERMINATED BY '|' -->In my case a field starts by |, and thus last record col3 is not displayed
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
col1,
col2,
col3
)
)
LOCATION (EXT_DATA:'myfile.dl')
)
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;
*****
Data:
*****
START-OF-FILE
START-OF-HEADER
EXECUTION-DATE=2013-01-01- 13.10.41.0 00152
FILE-DATE=Wed JAN 01 00:00:00 BST 2013
PROGRAM-NAME=ABC
FEED-NAME=MYFEED
CONSUMER=NA
END-OF-HEADER
START-OF-DATA
-->Fields are initiated by a delimiter
|COL1 |COL2 |COL3
|A1180137 |10003 |2013-04-02
|A1100970 |10003 |2013-04-02
END-OF-DATA
START-OF-TRAILER
EXECUTION-DATE=2013-05-08- 13.10.41.0 00250
FILE-DATE=Wed Apr 03 00:00:00 BST 2013
PROGRAM-NAME=GPS
FEED-NAME=SWAP_ADJUSTMENT
RECORD COUNT= 1125
END-OF-TRAILER
END-OF-FILE
Normally we have data where the field is terminated with |, here i have a field started with |
Oracle treats COL1 as second column and so on. The last column is not shown.
Q2. When i load data from external table into actual table, i want to filter the header and footer data, and load only the piped delimited data. How do i do that?
The problem here is that even the header is delimited and i want to filter it
**************
DROP TABLE mytable CASCADE CONSTRAINTS;
CREATE TABLE mytable
(
col1 VARCHAR2(500 BYTE),
col2 VARCHAR2(500 BYTE),
col3 VARCHAR2(500 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY mydir
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
NODISCARDFILE
NOLOGFILE
FIELDS TERMINATED BY '|' -->In my case a field starts by |, and thus last record col3 is not displayed
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
col1,
col2,
col3
)
)
LOCATION (EXT_DATA:'myfile.dl')
)
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;
*****
Data:
*****
START-OF-FILE
START-OF-HEADER
EXECUTION-DATE=2013-01-01-
FILE-DATE=Wed JAN 01 00:00:00 BST 2013
PROGRAM-NAME=ABC
FEED-NAME=MYFEED
CONSUMER=NA
END-OF-HEADER
START-OF-DATA
-->Fields are initiated by a delimiter
|COL1 |COL2 |COL3
|A1180137 |10003 |2013-04-02
|A1100970 |10003 |2013-04-02
END-OF-DATA
START-OF-TRAILER
EXECUTION-DATE=2013-05-08-
FILE-DATE=Wed Apr 03 00:00:00 BST 2013
PROGRAM-NAME=GPS
FEED-NAME=SWAP_ADJUSTMENT
RECORD COUNT= 1125
END-OF-TRAILER
END-OF-FILE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.