Home » SQL & PL/SQL » SQL & PL/SQL » External Table on tab delimited data not returning any rows (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 / Windows XP v2002 SP2)
| External Table on tab delimited data not returning any rows [message #279693] |
Fri, 09 November 2007 11:48  |
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
|
Junior Member |
|
|
Hi
Am working on a Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
running on Windows XP v2002 SP2
Have been trying to create an External table based on a tab delimited file.
The table does get created but try as I may, the subsequent query on the file-based external table just doesn't return any records. the logs are not showing any errors as well.
Here's all the information that I have for this problem.
1. Directory creation
CREATE OR REPLACE DIRECTORY extfiles AS 'D:\Profiles\piyush'
2. Put the file with the below sample content in the above directory on my filesystem
Commerce Id Company Cd Core Directory Id First Name Middle Initial Name Last Name Full Name Lvl4 Mgr Emp Id Rpt To Comm Id Rpt To First Name Rpt To Last Name Emp Status Cd Emp Status Desc Lvl1 Org Unit Cd Lvl2 Org Unit Cd Lvl3 Org Unit Cd Lvl4 Org Unit Cd Corp Func Cd Dept Id Separation Desc Locn Cd Mail Drop Cd Tel Num Fax Phone Num Res Stat Desc Pers Id Auth Name Pager Skytel Num Pager In House Num Add Line 1 Txt Add Line 2 Txt City Name State Province Cd Postal Cd Country Cd Country Desc City State Zip Country Personal Id Nationality Cd Nationality2 Cd Nationality3 Cd
12345678 CN02 xxxxxx DAVID LEAN LEAN DAVID 12345678 A Active CONDORAM OPS MD LOCALE GTM VW XY605 01 ABC07 ZCH07 12345678 (00) (00) 12345678 N 123 ANYWHERE ST 123 2nd ADDRESS ANYCITY HK Hong Kong BEIJING 123456 IN
09876543 CN02 xxxxxx JOHN LANG LANG JOHN 98765432 01234567 DOE DAVID DOE A Active CONDORAM OPS MD LOCALE GTM VW XY605 01 ABC07 +00-0123456789 00-12-34567890 123 ANYWHERE ST 123 2nd ADDRESS ANYCITY 010 100088 CN China BEIJING 123456 CN
2. created the external table with the below definition
CREATE TABLE sample_ext
(commerce_id VARCHAR2(8)
,company_cd VARCHAR2(4)
,core_directory_id VARCHAR2(20)
,first_nam VARCHAR2(12)
,middle_initial_nam VARCHAR2(10)
,last_nam VARCHAR2(8)
,full_nam VARCHAR2(40)
,lvl_4_mgr_emp_id VARCHAR2(8)
,rpt_to_comm_id VARCHAR2(40)
,rpt_to_first_nam VARCHAR2(40)
,rpt_to_last_nam VARCHAR2(40)
,emp_status_cd VARCHAR2(1)
,emp_status_desc VARCHAR2(25)
,lvl1_org_unit_cd VARCHAR2(12)
,lvl2_org_unit_cd VARCHAR2(12)
,lvl3_org_unit_cd VARCHAR2(12)
,lvl4_org_unit_cd VARCHAR2(12)
,corp_func_cd VARCHAR2(4)
,dept_id VARCHAR2(12)
,separation_desc VARCHAR2(30)
,locn_cd VARCHAR2(30)
,mail_drop_cd VARCHAR2(30)
,tel_num VARCHAR2(20)
,fax_phone_num VARCHAR2(30)
,res_stat_desc VARCHAR2(30)
,pers_id_auth_nam VARCHAR2(60)
,pager_skytel_num VARCHAR2(30)
,pager_in_house_num VARCHAR2(30)
,add_line_1_txt VARCHAR2(60)
,add_line_2_txt VARCHAR2(40)
,city_nam VARCHAR2(40)
,state_province_cd VARCHAR2(20)
,postal_cd VARCHAR2(10)
,country_cd VARCHAR2(20)
,country_desc VARCHAR2(50)
,city VARCHAR2(50)
,state VARCHAR2(50)
,zip VARCHAR2(20)
,country VARCHAR2(50)
,personal_id VARCHAR2(20)
,nationality_cd VARCHAR2(3)
,nationality2_cd VARCHAR2(3)
,nationality3_cd VARCHAR2(3)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTFILES
ACCESS PARAMETERS
(records delimited BY newline
skip 1
fields terminated BY 0x'09'
missing field VALUES are NULL
(commerce_id VARCHAR(8)
,company_cd VARCHAR(4)
,core_directory_id VARCHAR(20)
,first_nam VARCHAR(12)
,middle_initial_nam VARCHAR(10)
,last_nam VARCHAR(8)
,full_nam VARCHAR(40)
,lvl_4_mgr_emp_id VARCHAR(8)
,rpt_to_comm_id VARCHAR(40)
,rpt_to_first_nam VARCHAR(40)
,rpt_to_last_nam VARCHAR(40)
,emp_status_cd VARCHAR(1)
,emp_status_desc VARCHAR(25)
,lvl1_org_unit_cd VARCHAR(12)
,lvl2_org_unit_cd VARCHAR(12)
,lvl3_org_unit_cd VARCHAR(12)
,lvl4_org_unit_cd VARCHAR(12)
,corp_func_cd VARCHAR(4)
,dept_id VARCHAR(12)
,separation_desc VARCHAR(30)
,locn_cd VARCHAR(30)
,mail_drop_cd VARCHAR(30)
,tel_num VARCHAR(20)
,fax_phone_num VARCHAR(30)
,res_stat_desc VARCHAR(30)
,pers_id_auth_nam VARCHAR(60)
,pager_skytel_num VARCHAR(30)
,pager_in_house_num VARCHAR(30)
,add_line_1_txt VARCHAR(60)
,add_line_2_txt VARCHAR(40)
,city_nam VARCHAR(40)
,state_province_cd VARCHAR(20)
,postal_cd VARCHAR(10)
,country_cd VARCHAR(20)
,country_desc VARCHAR(50)
,city VARCHAR(50)
,state VARCHAR(50)
,zip VARCHAR(20)
,country VARCHAR(50)
,personal_id VARCHAR(20)
,nationality_cd VARCHAR(3)
,nationality2_cd VARCHAR(3)
,nationality3_cd VARCHAR(3)
)
)
location ('sample_id.txt')
)
REJECT LIMIT UNLIMITED
4. the log has the output as shown below with all fields listed but query on the External table keeps saying no rows selected.
Field Definitions for table SAMPLE_EXT
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
COMMERCE_ID VARCHAR (2, 8)
Terminated by " "
Trim whitespace same as SQL Loader
.
.
.
NATIONALITY3_CD VARCHAR (2, 3)
Terminated by " "
Trim whitespace same as SQL Loader
Tried to switch the field termination to tab in single quotes, ' ' as well as '\t'
Please don't ask me to switch the delimiter to comma as the data itself has comma.
Any help will be much appreciated.
Regards,
Piyush
|
|
|
|
|
|
|
|
|
|
|
|
| Re: External Table on tab delimited data not returning any rows [message #279734 is a reply to message #279729] |
Fri, 09 November 2007 17:48  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When describing your data, use CHAR instead of VARCHAR or VARCHAR2:
CREATE TABLE sample_ext
(commerce_id VARCHAR2(8)
,company_cd VARCHAR2(4)
,core_directory_id VARCHAR2(20)
,first_nam VARCHAR2(12)
,middle_initial_nam VARCHAR2(10)
,last_nam VARCHAR2(8)
,full_nam VARCHAR2(40)
,lvl_4_mgr_emp_id VARCHAR2(8)
,rpt_to_comm_id VARCHAR2(40)
,rpt_to_first_nam VARCHAR2(40)
,rpt_to_last_nam VARCHAR2(40)
,emp_status_cd VARCHAR2(1)
,emp_status_desc VARCHAR2(25)
,lvl1_org_unit_cd VARCHAR2(12)
,lvl2_org_unit_cd VARCHAR2(12)
,lvl3_org_unit_cd VARCHAR2(12)
,lvl4_org_unit_cd VARCHAR2(12)
,corp_func_cd VARCHAR2(4)
,dept_id VARCHAR2(12)
,separation_desc VARCHAR2(30)
,locn_cd VARCHAR2(30)
,mail_drop_cd VARCHAR2(30)
,tel_num VARCHAR2(20)
,fax_phone_num VARCHAR2(30)
,res_stat_desc VARCHAR2(30)
,pers_id_auth_nam VARCHAR2(60)
,pager_skytel_num VARCHAR2(30)
,pager_in_house_num VARCHAR2(30)
,add_line_1_txt VARCHAR2(60)
,add_line_2_txt VARCHAR2(40)
,city_nam VARCHAR2(40)
,state_province_cd VARCHAR2(20)
,postal_cd VARCHAR2(10)
,country_cd VARCHAR2(20)
,country_desc VARCHAR2(50)
,city VARCHAR2(50)
,state VARCHAR2(50)
,zip VARCHAR2(20)
,country VARCHAR2(50)
,personal_id VARCHAR2(20)
,nationality_cd VARCHAR2(3)
,nationality2_cd VARCHAR2(3)
,nationality3_cd VARCHAR2(3)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTFILES
ACCESS PARAMETERS
(records delimited BY newline
skip 1
fields terminated BY 0x'09'
missing field VALUES are NULL
(commerce_id CHAR(8)
,company_cd CHAR(4)
,core_directory_id CHAR(20)
,first_nam CHAR(12)
,middle_initial_nam CHAR(10)
,last_nam CHAR(8)
,full_nam CHAR(40)
,lvl_4_mgr_emp_id CHAR(8)
,rpt_to_comm_id CHAR(40)
,rpt_to_first_nam CHAR(40)
,rpt_to_last_nam CHAR(40)
,emp_status_cd CHAR(1)
,emp_status_desc CHAR(25)
,lvl1_org_unit_cd CHAR(12)
,lvl2_org_unit_cd CHAR(12)
,lvl3_org_unit_cd CHAR(12)
,lvl4_org_unit_cd CHAR(12)
,corp_func_cd CHAR(4)
,dept_id CHAR(12)
,separation_desc CHAR(30)
,locn_cd CHAR(30)
,mail_drop_cd CHAR(30)
,tel_num CHAR(20)
,fax_phone_num CHAR(30)
,res_stat_desc CHAR(30)
,pers_id_auth_nam CHAR(60)
,pager_skytel_num CHAR(30)
,pager_in_house_num CHAR(30)
,add_line_1_txt CHAR(60)
,add_line_2_txt CHAR(40)
,city_nam CHAR(40)
,state_province_cd CHAR(20)
,postal_cd CHAR(10)
,country_cd CHAR(20)
,country_desc CHAR(50)
,city CHAR(50)
,state CHAR(50)
,zip CHAR(20)
,country CHAR(50)
,personal_id CHAR(20)
,nationality_cd CHAR(3)
,nationality2_cd CHAR(3)
,nationality3_cd CHAR(3)
)
)
location ('sample_id.txt')
)
REJECT LIMIT UNLIMITED
/
SCOTT@orcl_11g> CREATE TABLE sample_ext
2 (commerce_id VARCHAR2(8)
3 ,company_cd VARCHAR2(4)
4 ,core_directory_id VARCHAR2(20)
5 ,first_nam VARCHAR2(12)
6 ,middle_initial_nam VARCHAR2(10)
7 ,last_nam VARCHAR2(8)
8 ,full_nam VARCHAR2(40)
9 ,lvl_4_mgr_emp_id VARCHAR2(8)
10 ,rpt_to_comm_id VARCHAR2(40)
11 ,rpt_to_first_nam VARCHAR2(40)
12 ,rpt_to_last_nam VARCHAR2(40)
13 ,emp_status_cd VARCHAR2(1)
14 ,emp_status_desc VARCHAR2(25)
15 ,lvl1_org_unit_cd VARCHAR2(12)
16 ,lvl2_org_unit_cd VARCHAR2(12)
17 ,lvl3_org_unit_cd VARCHAR2(12)
18 ,lvl4_org_unit_cd VARCHAR2(12)
19 ,corp_func_cd VARCHAR2(4)
20 ,dept_id VARCHAR2(12)
21 ,separation_desc VARCHAR2(30)
22 ,locn_cd VARCHAR2(30)
23 ,mail_drop_cd VARCHAR2(30)
24 ,tel_num VARCHAR2(20)
25 ,fax_phone_num VARCHAR2(30)
26 ,res_stat_desc VARCHAR2(30)
27 ,pers_id_auth_nam VARCHAR2(60)
28 ,pager_skytel_num VARCHAR2(30)
29 ,pager_in_house_num VARCHAR2(30)
30 ,add_line_1_txt VARCHAR2(60)
31 ,add_line_2_txt VARCHAR2(40)
32 ,city_nam VARCHAR2(40)
33 ,state_province_cd VARCHAR2(20)
34 ,postal_cd VARCHAR2(10)
35 ,country_cd VARCHAR2(20)
36 ,country_desc VARCHAR2(50)
37 ,city VARCHAR2(50)
38 ,state VARCHAR2(50)
39 ,zip VARCHAR2(20)
40 ,country VARCHAR2(50)
41 ,personal_id VARCHAR2(20)
42 ,nationality_cd VARCHAR2(3)
43 ,nationality2_cd VARCHAR2(3)
44 ,nationality3_cd VARCHAR2(3)
45 )
46 ORGANIZATION EXTERNAL
47 (TYPE ORACLE_LOADER
48 DEFAULT DIRECTORY EXTFILES
49 ACCESS PARAMETERS
50 (records delimited BY newline
51 skip 1
52 fields terminated BY 0x'09'
53 missing field VALUES are NULL
54 (commerce_id CHAR(8)
55 ,company_cd CHAR(4)
56 ,core_directory_id CHAR(20)
57 ,first_nam CHAR(12)
58 ,middle_initial_nam CHAR(10)
59 ,last_nam CHAR(8)
60 ,full_nam CHAR(40)
61 ,lvl_4_mgr_emp_id CHAR(8)
62 ,rpt_to_comm_id CHAR(40)
63 ,rpt_to_first_nam CHAR(40)
64 ,rpt_to_last_nam CHAR(40)
65 ,emp_status_cd CHAR(1)
66 ,emp_status_desc CHAR(25)
67 ,lvl1_org_unit_cd CHAR(12)
68 ,lvl2_org_unit_cd CHAR(12)
69 ,lvl3_org_unit_cd CHAR(12)
70 ,lvl4_org_unit_cd CHAR(12)
71 ,corp_func_cd CHAR(4)
72 ,dept_id CHAR(12)
73 ,separation_desc CHAR(30)
74 ,locn_cd CHAR(30)
75 ,mail_drop_cd CHAR(30)
76 ,tel_num CHAR(20)
77 ,fax_phone_num CHAR(30)
78 ,res_stat_desc CHAR(30)
79 ,pers_id_auth_nam CHAR(60)
80 ,pager_skytel_num CHAR(30)
81 ,pager_in_house_num CHAR(30)
82 ,add_line_1_txt CHAR(60)
83 ,add_line_2_txt CHAR(40)
84 ,city_nam CHAR(40)
85 ,state_province_cd CHAR(20)
86 ,postal_cd CHAR(10)
87 ,country_cd CHAR(20)
88 ,country_desc CHAR(50)
89 ,city CHAR(50)
90 ,state CHAR(50)
91 ,zip CHAR(20)
92 ,country CHAR(50)
93 ,personal_id CHAR(20)
94 ,nationality_cd CHAR(3)
95 ,nationality2_cd CHAR(3)
96 ,nationality3_cd CHAR(3)
97 )
98 )
99 location ('sample_id.txt')
100 )
101 REJECT LIMIT UNLIMITED
102 /
Table created.
SCOTT@orcl_11g> Select count (*) from sample_ext
2 /
COUNT(*)
----------
2
SCOTT@orcl_11g> select * from sample_ext
2 /
COMMERCE COMP CORE_DIRECTORY_ID FIRST_NAM MIDDLE_INI LAST_NAM
-------- ---- -------------------- ------------ ---------- --------
FULL_NAM LVL_4_MG
---------------------------------------- --------
RPT_TO_COMM_ID
----------------------------------------
RPT_TO_FIRST_NAM
----------------------------------------
RPT_TO_LAST_NAM E EMP_STATUS_DESC
---------------------------------------- - -------------------------
LVL1_ORG_UNI LVL2_ORG_UNI LVL3_ORG_UNI LVL4_ORG_UNI CORP DEPT_ID
------------ ------------ ------------ ------------ ---- ------------
SEPARATION_DESC LOCN_CD
------------------------------ ------------------------------
MAIL_DROP_CD TEL_NUM
------------------------------ --------------------
FAX_PHONE_NUM RES_STAT_DESC
------------------------------ ------------------------------
PERS_ID_AUTH_NAM
------------------------------------------------------------
PAGER_SKYTEL_NUM PAGER_IN_HOUSE_NUM
------------------------------ ------------------------------
ADD_LINE_1_TXT
------------------------------------------------------------
ADD_LINE_2_TXT
----------------------------------------
CITY_NAM STATE_PROVINCE_CD POSTAL_CD
---------------------------------------- -------------------- ----------
COUNTRY_CD COUNTRY_DESC
-------------------- --------------------------------------------------
CITY
--------------------------------------------------
STATE ZIP
-------------------------------------------------- --------------------
COUNTRY PERSONAL_ID NAT NAT
-------------------------------------------------- -------------------- --- ---
NAT
---
12345678 CN02 xxxxxx DAVID LEAN
LEAN DAVID 12345678
A Active
CONDORAM OPS MD LOCALE GTM VW XY605
01 ABC07
ZCH07 12345678
(00) (00) 12345678 N
123 ANYWHERE ST
123 2nd ADDRESS
ANYCITY
HK Hong Kong
BEIJING
123456
IN
09876543 CN02 xxxxxx JOHN LANG
LANG JOHN 98765432
01234567
DOE DAVID
DOE A Active
CONDORAM OPS MD LOCALE GTM VW XY605
01 ABC07
+00-0123456789
00-12-34567890
123 ANYWHERE ST
123 2nd ADDRESS
ANYCITY 010 100088
CN China
BEIJING
123456
CN
SCOTT@orcl_11g>
|
|
|
|
| Re: External Table on tab delimited data not returning any rows [message #280128 is a reply to message #279734] |
Mon, 12 November 2007 10:29 |
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
|
Junior Member |
|
|
That sounds truly encouraging. So you did really pull it off.
However, for some reason, when Am trying at my end after changing from Varchar to Char, the file operation is still not parsing a single record.
Can you please do a me a favour by attaching the sample file that you used to generate the output and upload to this thread?
Am guessing it could be a problem with the field delimiter character at my end.
|
|
|
|
| Re: External Table on tab delimited data not returning any rows [message #280136 is a reply to message #279734] |
Mon, 12 November 2007 10:50 |
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
|
Junior Member |
|
|
That's truly great that you have it working at your end.
However, when I tried to change to CHAR and try again, the file processing still had the same behaviour i.e. no bad file or no error in the log but the query still returning 0 rows
Do me a favour please and attach the sample that you used as an attachment to this thread.
Am guessing this could a problem with the delimiter that's present in my actual file.
Thanks a lot for your time on this one.
Regards,
Piyush
|
|
|
|
| Re: External Table on tab delimited data not returning any rows [message #280146 is a reply to message #279734] |
Mon, 12 November 2007 11:26 |
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
|
Junior Member |
|
|
Looks like the problem is not with the field delimiter but with the Record delimiter.
When I open my Orginial file in a simple editor, i.e. Notepad, I see the records are seperated by a special character, which looks like a square box to the naked eye.
But if i open the same file in a different editor i.e. Wordpad, Editplus, the lines look seperated out evenly
Any clues for this?
|
|
|
|
| Re: External Table on tab delimited data not returning any rows [message #280155 is a reply to message #280146] |
Mon, 12 November 2007 11:42 |
|
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please try copying and pasting the following to a .sql file, then starting that file from SQL*Plus and see what you get. Once we know whether or not that works, it should help narrow down the problem.
store set saved_settings replace
set echo off feedback off heading off pagesize 0 verify off
spool D:\Profiles\piyush\sample_id.txt
prompt Commerce Id Company Cd Core Directory Id First Name Middle Initial Name Last Name Full Name Lvl4 Mgr Emp Id Rpt To Comm Id Rpt To First Name Rpt To Last Name Emp Status Cd Emp Status Desc Lvl1 Org Unit Cd Lvl2 Org Unit Cd Lvl3 Org Unit Cd Lvl4 Org Unit Cd Corp Func Cd Dept Id Separation Desc Locn Cd Mail Drop Cd Tel Num Fax Phone Num Res Stat Desc Pers Id Auth Name Pager Skytel Num Pager In House Num Add Line 1 Txt Add Line 2 Txt City Name State Province Cd Postal Cd Country Cd Country Desc City State Zip Country Personal Id Nationality Cd Nationality2 Cd Nationality3 Cd
prompt 12345678 CN02 xxxxxx DAVID LEAN LEAN DAVID 12345678 A Active CONDORAM OPS MD LOCALE GTM VW XY605 01 ABC07 ZCH07 12345678 (00) (00) 12345678 N 123 ANYWHERE ST 123 2nd ADDRESS ANYCITY HK Hong Kong BEIJING 123456 IN
prompt 09876543 CN02 xxxxxx JOHN LANG LANG JOHN 98765432 01234567 DOE DAVID DOE A Active CONDORAM OPS MD LOCALE GTM VW XY605 01 ABC07 +00-0123456789 00-12-34567890 123 ANYWHERE ST 123 2nd ADDRESS ANYCITY 010 100088 CN China BEIJING 123456 CN
Spool off
START SAVED_SETTINGS
DROP TABLE SAMPLE_EXT
/
CREATE TABLE sample_ext
(commerce_id VARCHAR2(8)
,company_cd VARCHAR2(4)
,core_directory_id VARCHAR2(20)
,first_nam VARCHAR2(12)
,middle_initial_nam VARCHAR2(10)
,last_nam VARCHAR2(8)
,full_nam VARCHAR2(40)
,lvl_4_mgr_emp_id VARCHAR2(8)
,rpt_to_comm_id VARCHAR2(40)
,rpt_to_first_nam VARCHAR2(40)
,rpt_to_last_nam VARCHAR2(40)
,emp_status_cd VARCHAR2(1)
,emp_status_desc VARCHAR2(25)
,lvl1_org_unit_cd VARCHAR2(12)
,lvl2_org_unit_cd VARCHAR2(12)
,lvl3_org_unit_cd VARCHAR2(12)
,lvl4_org_unit_cd VARCHAR2(12)
,corp_func_cd VARCHAR2(4)
,dept_id VARCHAR2(12)
,separation_desc VARCHAR2(30)
,locn_cd VARCHAR2(30)
,mail_drop_cd VARCHAR2(30)
,tel_num VARCHAR2(20)
,fax_phone_num VARCHAR2(30)
,res_stat_desc VARCHAR2(30)
,pers_id_auth_nam VARCHAR2(60)
,pager_skytel_num VARCHAR2(30)
,pager_in_house_num VARCHAR2(30)
,add_line_1_txt VARCHAR2(60)
,add_line_2_txt VARCHAR2(40)
,city_nam VARCHAR2(40)
,state_province_cd VARCHAR2(20)
,postal_cd VARCHAR2(10)
,country_cd VARCHAR2(20)
,country_desc VARCHAR2(50)
,city VARCHAR2(50)
,state VARCHAR2(50)
,zip VARCHAR2(20)
,country VARCHAR2(50)
,personal_id VARCHAR2(20)
,nationality_cd VARCHAR2(3)
,nationality2_cd VARCHAR2(3)
,nationality3_cd VARCHAR2(3)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTFILES
ACCESS PARAMETERS
(records delimited BY newline
skip 1
fields terminated BY 0x'09'
missing field VALUES are NULL
(commerce_id CHAR(8)
,company_cd CHAR(4)
,core_directory_id CHAR(20)
,first_nam CHAR(12)
,middle_initial_nam CHAR(10)
,last_nam CHAR(8)
,full_nam CHAR(40)
,lvl_4_mgr_emp_id CHAR(8)
,rpt_to_comm_id CHAR(40)
,rpt_to_first_nam CHAR(40)
,rpt_to_last_nam CHAR(40)
,emp_status_cd CHAR(1)
,emp_status_desc CHAR(25)
,lvl1_org_unit_cd CHAR(12)
,lvl2_org_unit_cd CHAR(12)
,lvl3_org_unit_cd CHAR(12)
,lvl4_org_unit_cd CHAR(12)
,corp_func_cd CHAR(4)
,dept_id CHAR(12)
,separation_desc CHAR(30)
,locn_cd CHAR(30)
,mail_drop_cd CHAR(30)
,tel_num CHAR(20)
,fax_phone_num CHAR(30)
,res_stat_desc CHAR(30)
,pers_id_auth_nam CHAR(60)
,pager_skytel_num CHAR(30)
,pager_in_house_num CHAR(30)
,add_line_1_txt CHAR(60)
,add_line_2_txt CHAR(40)
,city_nam CHAR(40)
,state_province_cd CHAR(20)
,postal_cd CHAR(10)
,country_cd CHAR(20)
,country_desc CHAR(50)
,city CHAR(50)
,state CHAR(50)
,zip CHAR(20)
,country CHAR(50)
,personal_id CHAR(20)
,nationality_cd CHAR(3)
,nationality2_cd CHAR(3)
,nationality3_cd CHAR(3)
)
)
location ('sample_id.txt')
)
REJECT LIMIT UNLIMITED
/
Select count (*) from sample_ext
/
select * from sample_ext
/
|
|
|
|
| Re: External Table on tab delimited data not returning any rows [message #280158 is a reply to message #280146] |
Mon, 12 November 2007 11:56 |
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
|
Junior Member |
|
|
Hi Barbara
got the fix. I changed the record delimiter to a Hex value and it's working fine.
Earlier I had supplied records delimited BY newline.
based on your thread reply and after studying the file that am receiving, I changed to records delimited BY 0x'0a'.
Once I did that, the file processing is working smoothly.
Please ignore my earlier request of posting the sample file that you had used.
Again, thanks for your time and effort on this one.
Regards,
Piyush
|
|
|
|
|
|
|
|
| Re: External Table on tab delimited data not returning any rows [message #280179 is a reply to message #280159] |
Mon, 12 November 2007 13:13 |
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
|
Junior Member |
|
|
You're absolutely right.
I tried that option of copy-paste the record delimiter as is onto the External Table definition and it worked as well.
you're also right about the ASCII function returning the CHR(10) as well. tried to copy-paste the record delimiter onto the ASCII function and it keeps returning 10.
it's a lesson learnt for me.
Thanks for the tips.
|
|
|
|
Goto Forum:
Current Time: Mon Dec 09 19:52:50 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
