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 Go to next message
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 #279699 is a reply to message #279693] Fri, 09 November 2007 12:15 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
What does
select count(*) from sample_ext
produce?
Re: External Table on tab delimited data not returning any rows [message #279721 is a reply to message #279699] Fri, 09 November 2007 13:31 Go to previous messageGo to next message
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
Junior Member
the count query returns 0
Got any ideas for this behaviour?
Re: External Table on tab delimited data not returning any rows [message #279722 is a reply to message #279693] Fri, 09 November 2007 13:41 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
I suggest you visit http:/asktom.oracle.com & do a keyword search against "external table".
Then follow the examples presented by Tom Kyte.
You have either an error of commission or error of omission; but I have neither the time nor inclination to troubleshoot this for you.
I've made it work and many others have made it work; so what ever situation you have; it is of your own making.
Re: External Table on tab delimited data not returning any rows [message #279729 is a reply to message #279693] Fri, 09 November 2007 15:52 Go to previous messageGo to next message
ora_balan
Messages: 21
Registered: January 2007
Location: Mumbai, India
Junior Member
Anybody else out there with an idea of what might be going wrong?
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
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 Go to previous messageGo to next message
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 #280159 is a reply to message #280146] Mon, 12 November 2007 12:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
Since you did not use code tags on your original post, the tabs are converted to spaces. However, as a moderator, if I edit your post and copy and paste, it has the tab delimiters and I assume that the newline characters are preserved. It may be that the newline character on the system that you are running it on is different from that in the text file. When I copy and paste it into an ascii function, it returns chr(10), but some systems use a combination of chr(10) and chr(12) or chr(13). Instead of using "records delimited by newline", you could use:

records delimited BY '
'


where you copy and paste one of the newline characters from your text file between the single quotes. I tested it with your file and it worked. That way you don't have to guess at what the proper combination might be. You will need to copy and paste from one of the editors that does not display it as a box.
Re: External Table on tab delimited data not returning any rows [message #280160 is a reply to message #280158] Mon, 12 November 2007 12:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
Glad you got it fixed. I posted my last response before I saw that you had fixed it.
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 Go to previous message
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.
Previous Topic: Duplicate Column error on MERGE statement
Next Topic: Re:Triggers for Ms acess database
Goto Forum:
  


Current Time: Fri Dec 02 14:25:02 CST 2016

Total time taken to generate the page: 0.81568 seconds