Home » RDBMS Server » Server Utilities » EXTERNAL TABLE PROBLEM (ORACLE 11G)
EXTERNAL TABLE PROBLEM [message #563263] Fri, 10 August 2012 11:23 Go to next message
w0rtez
Messages: 6
Registered: August 2012
Junior Member
Hi im trying to create an external table, and i load my data without no problem, and everything is fine, but i got some behavior with one column that i would like to know whats behind scenes, ok let's get the example:

[*] Sample Data
Line 1:333 1111111112009100000000000080000000013450.33
Line 2:11111111111220091016000000004.48
Line 3:222222222 220091016000000004.48
Line 4:(This is a blank line left)


And this is my External Table Create Query:

CREATE TABLE EXT_TABLE_TEMP
(COL_A VARCHAR2(11),
COL_B VARCHAR2(1),
COL_C DATE,
COL_D NUMBER(12,2))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY TMP
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE TMP:'BAD_TEMPTABLE.bad'
LOGFILE TMP:'LOG_TEMPTABLE.log'
NODISCARDFILE
SKIP 1
LOAD WHEN (COL_A != BLANKS)
FIELDS LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
( COL_A CHAR(11),
COL_B CHAR(1),
COL_C CHAR(Cool DATE_FORMAT DATE MASK "YYYY/MM/DD",
COL_D CHAR(12)
)
)
LOCATION ('TEMP_DATA.txt')
)REJECT LIMIT UNLIMITED;


As you can see i can upload my table with no problem but i always get 3 lines counting last blank line if i try LOAD WHEN COL_A != BLANKS, i dont know if its a problem of the blank space left between fixed fields length, but if i do
LOAD WHEN COL_B != BLANKS i get correct result 2 lines instead of 3, i want to know why (missing fields...) and (reject rows...) are not working...
Note: COL_A could be 9-11 length, if length its 9 then 2 spaces left before next one...

Thanks
Re: EXTERNAL TABLE PROBLEM [message #563275 is a reply to message #563263] Fri, 10 August 2012 12:56 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
You need proper punctuation in your explanation as I have no idea what you are trying to explain.
Re: EXTERNAL TABLE PROBLEM [message #563276 is a reply to message #563275] Fri, 10 August 2012 13:11 Go to previous messageGo to next message
w0rtez
Messages: 6
Registered: August 2012
Junior Member
Hi,
I want to:
1) Remove Last Line that is blank but exists
2) When trying to use LOAD WHEN (COL_A != BLANKS) fails and bring me 3 lines instead of 2(I suppose thats because if total length of column A is 9, then 2 whitespaces are left to complete total column length that is 11), but if I use LOAD WHEN (COL_B != BLANKS) it works and brings me 2 lines.
Re: EXTERNAL TABLE PROBLEM [message #563283 is a reply to message #563263] Fri, 10 August 2012 18:13 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
w0rtez wrote on Fri, 10 August 2012 12:23
but i always get 3 lines counting last blank line if i try [i]LOAD WHEN COL_A != BLANKS[/i


I can't reproduce it:

SQL> select  *
  2    from  v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> CREATE TABLE EXT_TABLE_TEMP
  2  (COL_A VARCHAR2(11),
  3  COL_B VARCHAR2(1),
  4  COL_C DATE,
  5  COL_D NUMBER(12,2))
  6  ORGANIZATION EXTERNAL
  7  (TYPE ORACLE_LOADER
  8  DEFAULT DIRECTORY TEMP
  9  ACCESS PARAMETERS
 10  (RECORDS DELIMITED BY NEWLINE
 11  BADFILE TMP:'BAD_TEMPTABLE.bad'
 12  LOGFILE TMP:'LOG_TEMPTABLE.log'
 13  NODISCARDFILE
 14  SKIP 1
 15  LOAD WHEN (COL_A != BLANKS)
 16  FIELDS LDRTRIM
 17  MISSING FIELD VALUES ARE NULL
 18  REJECT ROWS WITH ALL NULL FIELDS
 19  ( COL_A CHAR(11),
 20  COL_B CHAR(1),
 21  COL_C CHAR(8) DATE_FORMAT DATE MASK "YYYY/MM/DD",
 22  COL_D CHAR(12)
 23  )
 24  )
 25  LOCATION ('TEMP_DATA.txt')
 26  )REJECT LIMIT UNLIMITED;

Table created.

SQL> select rownum,t.* from EXT_TABLE_TEMP t;

    ROWNUM COL_A       C COL_C          COL_D
---------- ----------- - --------- ----------
         1 11111111111 2 16-OCT-09       4.48
         2 222222222   2 16-OCT-09       4.48

SQL> 


SY.
Re: EXTERNAL TABLE PROBLEM [message #563303 is a reply to message #563283] Sat, 11 August 2012 18:15 Go to previous message
w0rtez
Messages: 6
Registered: August 2012
Junior Member
Hi,
Your test file has last line(line 3) with no data, fully blank?
Previous Topic: Restore
Next Topic: Exp / Imp
Goto Forum:
  


Current Time: Thu Mar 28 08:20:05 CDT 2024