Home » RDBMS Server » Server Utilities » Skip Last Line in External table (SQL)
Skip Last Line in External table [message #557779] Fri, 15 June 2012 13:34 Go to next message
punu77
Messages: 20
Registered: July 2005
Junior Member
Hi,

Can someone tell me please how to skip last record while loading external table.
Please see the example below I don't want to load the 000000005 in the AAA external table.



My Data is

100001***04252012***06:02:40***CignaGlobalHealthBenefits
201441424_7076551_OLC_1234567899.aaa
201441424_7075703_OLC_3456789134.aaa
201442669_7075775_RIE_5432167891.aaa
700223567_7077646_ECS_2345678912.aaa
700331352_7078197_RIE_5678901234.aaa
000000005

CREATE TABLE CIEBADM.EXT_AAA(
BATCH_NO VARCHAR2(6),
FILE_DATE VARCHAR2(8),
Employee_ID VARCHAR2(12 BYTE),
INTER_ID VARCHAR2(12 BYTE),
SOURCE_ID VARCHAR2(3 BYTE),
MISSION_ID VARCHAR2(12 BYTE),
FILE_NAME VARCHAR2(50 BYTE)

)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_TAB_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED by newline
BADFILE ext_tab_dir: 'EXT_CLAIMMAIL_RECON.BAD'
LOGFILE ext_tab_dir:'EXT_CLAIMMAIL_RECON.LOG'
FIELDS NOTRIM
(
BATCH_NO (1:6) CHAR(6),
FILE_DATE (10:8) CHAR(8),
Employee_ID (1:9) CHAR(9),
INTER (11:7) CHAR(7),
SOURCE_ID (19:3) CHAR(3),
MISSION_ID (23:33) CHAR(10),
FILE_NAME (1:36) CHAR(36)

)
)
LOCATION (EXT_TAB_DIR:'ditctrlfile.txt')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;


Thank you for help me

Punu

[mod-edit: smileys disabled by bb to prevent conversion of code to smileys]

[Updated on: Fri, 15 June 2012 13:44] by Moderator

Report message to a moderator

Re: Skip Last Line in External table [message #557781 is a reply to message #557779] Fri, 15 June 2012 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
how do you know it is the "last" line until after it has been read & loaded then you discover no more data?

what about using EXTERNAL TABLE & where BATCHNO != 5

Report message to a moderator

Re: Skip Last Line in External table [message #557787 is a reply to message #557779] Fri, 15 June 2012 14:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
You will need something more than row position to distinguish it from the other rows, such as (batch_no != '000000') or (file_dat != '') or a combination, as shown below.

SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY ext_tab_dir AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> CREATE TABLE EXT_AAA
  2    (BATCH_NO     VARCHAR2(6),
  3  	FILE_DATE    VARCHAR2(8),
  4  	Employee_ID  VARCHAR2(12 BYTE),
  5  	INTER_ID     VARCHAR2(12 BYTE),
  6  	SOURCE_ID    VARCHAR2(3 BYTE),
  7  	MISSION_ID   VARCHAR2(12 BYTE),
  8  	FILE_NAME    VARCHAR2(50 BYTE))
  9  ORGANIZATION EXTERNAL
 10    (TYPE ORACLE_LOADER
 11  	DEFAULT DIRECTORY EXT_TAB_DIR
 12  	ACCESS PARAMETERS
 13  	  (RECORDS DELIMITED by newline
 14  	   LOAD WHEN (("BATCH_NO" != "000000") AND ("FILE_DATE" != BLANKS))
 15  	   BADFILE ext_tab_dir: 'EXT_CLAIMMAIL_RECON.BAD'
 16  	   LOGFILE ext_tab_dir:'EXT_CLAIMMAIL_RECON.LOG'
 17  	   FIELDS NOTRIM
 18  	     (BATCH_NO	  ( 1: 6) CHAR(6),
 19  	      FILE_DATE   (10: 8) CHAR(8),
 20  	      Employee_ID ( 1: 9) CHAR(9),
 21  	      INTER_ID	  (11: 7) CHAR(7),
 22  	      SOURCE_ID   (19: 3) CHAR(3),
 23  	      MISSION_ID  (23:33) CHAR(10),
 24  	      FILE_NAME   ( 1:36) CHAR(36)))
 25    LOCATION (EXT_TAB_DIR:'ditctrlfile.txt'))
 26  REJECT LIMIT UNLIMITED
 27  NOPARALLEL
 28  NOMONITORING
 29  /

Table created.

SCOTT@orcl_11gR2> SELECT * FROM ext_aaa
  2  /

BATCH_ FILE_DAT EMPLOYEE_ID  INTER_ID     SOU MISSION_ID
------ -------- ------------ ------------ --- ------------
FILE_NAME
--------------------------------------------------
100001 04252012 100001***    4252012      **0 :02:40***C
100001***04252012***06:02:40***Cigna

201441 _7076551 201441424    7076551      OLC 1234567899
201441424_7076551_OLC_1234567899.aaa

201441 _7075703 201441424    7075703      OLC 3456789134
201441424_7075703_OLC_3456789134.aaa

201442 _7075775 201442669    7075775      RIE 5432167891
201442669_7075775_RIE_5432167891.aaa

700223 _7077646 700223567    7077646      ECS 2345678912
700223567_7077646_ECS_2345678912.aaa

700331 _7078197 700331352    7078197      RIE 5678901234
700331352_7078197_RIE_5678901234.aaa


6 rows selected.

Report message to a moderator

Re: Skip Last Line in External table [message #557793 is a reply to message #557781] Fri, 15 June 2012 15:25 Go to previous messageGo to next message
punu77
Messages: 20
Registered: July 2005
Junior Member
Hi Barbara,

Thank you very much for replying
Table is generated and log file is generated to with following error:

LOG file opened at 06/15/12 16:21:40

Field Definitions for table EXT_CLAIMMAIL_RECON
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Load when ((BATCH_NO != 000000) AND (FILE_DATE != BLANKS))

Fields in Data Source:

BATCH_NO CHAR (6)
Record position (1, 6)
FILE_DATE CHAR (Cool
Record position (10, Cool
EMPLOYEE_ID CHAR (9)
Record position (1, 9)
INTER CHAR (7)
Record position (11, 7)
SOURCE_ID CHAR (3)
Record position (19, 3)
MISSION_ID CHAR (10)
Record position (23, 33)
FILE_NAME CHAR (36)
Record position (1, 36)
KUP-04021: field formatting error for field FILE_DATE
KUP-04023: field start is after end of record
KUP-04101: record 7 rejected in file FILENAME

Report message to a moderator

Re: Skip Last Line in External table [message #557798 is a reply to message #557793] Fri, 15 June 2012 15:47 Go to previous message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Can you select from the table? If not, then try modifying as below, checking just the batch_no and adding two lines to handle null values and null rows.

CREATE TABLE EXT_AAA
  (BATCH_NO     VARCHAR2(6),
   FILE_DATE    VARCHAR2(8),
   Employee_ID  VARCHAR2(12 BYTE),
   INTER_ID     VARCHAR2(12 BYTE),
   SOURCE_ID    VARCHAR2(3 BYTE),
   MISSION_ID   VARCHAR2(12 BYTE),
   FILE_NAME    VARCHAR2(50 BYTE))
ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
   DEFAULT DIRECTORY EXT_TAB_DIR
   ACCESS PARAMETERS
     (RECORDS DELIMITED by newline
      LOAD WHEN ("BATCH_NO" != "000000")
      BADFILE ext_tab_dir: 'EXT_CLAIMMAIL_RECON.BAD'
      LOGFILE ext_tab_dir:'EXT_CLAIMMAIL_RECON.LOG'
      FIELDS NOTRIM
      MISSING FIELD VALUES ARE NULL 
      REJECT ROWS WITH ALL NULL FIELDS 
        (BATCH_NO    ( 1: 6) CHAR(6),
         FILE_DATE   (10: 8) CHAR(8),
         Employee_ID ( 1: 9) CHAR(9),
         INTER_ID    (11: 7) CHAR(7),
         SOURCE_ID   (19: 3) CHAR(3),
         MISSION_ID  (23:33) CHAR(10),
         FILE_NAME   ( 1:36) CHAR(36)))
  LOCATION (EXT_TAB_DIR:'ditctrlfile.txt'))
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING
/

Report message to a moderator

Previous Topic: Concatenate timestamp to a constant value in a control file
Next Topic: oracle environment setting
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 03 16:16:29 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.