Home » SQL & PL/SQL » SQL & PL/SQL » external tables (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production)
external tables [message #390336] Fri, 06 March 2009 00:52 Go to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member

I have a tab delimited flat file emp.dat as

empid first_name last_name sal hire_date
1 jon hall 12345 NULL
2 jon NULL 12345 02-02-2009 00:00:00
3 jon hall NULL 02-02-2009 00:00:00
4 jon hall 12345 NULL

above specified is string 'NULL'

when I load into table emp using external tables the 'NULL' string should be populated as NULL

select * from emp;

1 jon hall 12345
2 jon 12345 02-02-2009 00:00:00
3 jon hall 02-02-2009 00:00:00
4 jon hall 12345
Re: external tables [message #390337 is a reply to message #390336] Fri, 06 March 2009 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I load into table emp using external tables the 'NULL' string should be populated as NULL

You load from external table using SQL, so you can use NVL.

Regards
Michel
Re: external tables [message #390363 is a reply to message #390337] Fri, 06 March 2009 02:18 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
THis is not the answer to question.

I want NULL when string 'NULL' is encountered
Re: external tables [message #390364 is a reply to message #390363] Fri, 06 March 2009 02:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Then use DECODE
Re: external tables [message #390367 is a reply to message #390363] Fri, 06 March 2009 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
subusona wrote on Fri, 06 March 2009 09:18
THis is not the answer to question.

I want NULL when string 'NULL' is encountered

This ANSWERS the question, if you only check it.
SQL> select nvl(null,'NULL') from dual;
NVL(
----
NULL

1 row selected.

Regards
Michel

Re: external tables [message #390368 is a reply to message #390367] Fri, 06 March 2009 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK this is the opposite you want, just check SQL functions.
SQL> select nullif('NULL','NULL') from dual;
NULL
----


1 row selected.

Regards
Michel
Re: external tables [message #390373 is a reply to message #390367] Fri, 06 March 2009 02:52 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
Mitchell,

I know this but do we have a condition or any trick that can be done while we are populating the external tables itself.

Thanks
Re: external tables [message #390402 is a reply to message #390373] Fri, 06 March 2009 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You DO NOT populate an external table.
An external table is just a VIEW on your file.
You populate a target table from an external table using SQL.

Regards
Michel
Re: external tables [message #390467 is a reply to message #390402] Fri, 06 March 2009 08:21 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
agreed.
DO we have any access parameter of external tables to do this job. eg. nullif ,default_if....

Not sure how to implement it
Re: external tables [message #390469 is a reply to message #390467] Fri, 06 March 2009 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which job?

Regards
Michel
Re: external tables [message #390488 is a reply to message #390467] Fri, 06 March 2009 10:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I believe that you can use the DEFAULTIF syntax to achieve what you want
Re: external tables [message #390519 is a reply to message #390488] Fri, 06 March 2009 23:01 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
can you pls how to fit into this

CREATE TABLE emp_ext
(
empid NUMBER(5),
first_name VARCHAR2(255),
last_name VARCHAR2(255),
sal VARCHAR2(255),
hire_date VARCHAR2(255)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY emp_dir
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE SKIP 1
BADFILE ST1_CONVERSION_IP:'emp_ext%a_%p.bad'
LOGFILE ST1_CONVERSION_IP:'emp_ext%a_%p.log'
FIELDS TERMINATED BY 0X'09' MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION (emp_dir:'emp.dat')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
Re: external tables [message #390529 is a reply to message #390519] Sat, 07 March 2009 00:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
CREATE TABLE emp_ext
(
empid NUMBER(5),
first_name VARCHAR2(255),
last_name VARCHAR2(255),
sal VARCHAR2(255),
hire_date VARCHAR2(255)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY emp_dir
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE SKIP 1
BADFILE ST1_CONVERSION_IP:'emp_ext%a_%p.bad'
LOGFILE ST1_CONVERSION_IP:'emp_ext%a_%p.log'
FIELDS TERMINATED BY 0X'09' MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPID",
"FIRST_NAME" NULLIF ("FIRST_NAME" = "NULL"),
"LAST_NAME" NULLIF ("LAST_NAME" = "NULL"),
"SAL" NULLIF ("SAL" = "NULL"),
"HIRE_DATE" NULLIF ("HIRE_DATE" = "NULL")
)

)
LOCATION (emp_dir:'emp.dat')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
Re: external tables [message #390556 is a reply to message #390529] Sat, 07 March 2009 06:56 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
Thanks a lot. You are simply great
Re: external tables [message #390631 is a reply to message #390556] Mon, 09 March 2009 00:58 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
i have another issue. last field of each line that is read has a special character appended
which is extra DOS-style carriage return(Typ=1 Len=1: 13). How to avoid this
Re: external tables [message #390639 is a reply to message #390631] Mon, 09 March 2009 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Change "RECORDS DELIMITED BY" clause to fit your actual end of line.

Regards
Michel
Re: external tables [message #390657 is a reply to message #390639] Mon, 09 March 2009 03:46 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
I am not sure what you mean. Example please.

I want each line to trimmed by CHR(13) before it reads.
eg. rtrim(line,CHR(13)) this way the special character at eand of each line will be removed
Re: external tables [message #390661 is a reply to message #390657] Mon, 09 March 2009 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You execute your load on Unix so "NEWLINE" in "RECORDS DELIMITED BY" is chr(10) but your file is a Windows one so end-of-line is chr(13)||chr(10).
So either change your end-of-line at OS level (or better transfer it in binary mode) either try (but I'm not sure it works) "RECORDS DELIMITED BY x'0C0A'".

Regards
Michel
Re: external tables [message #390662 is a reply to message #390657] Mon, 09 March 2009 04:01 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@subusona,

External Tables Access Parameters might help you understand what Michel pointed out.

[Edit : Didn't see Michel's last reply.]
Regards,
Jo

[Updated on: Mon, 09 March 2009 04:04]

Report message to a moderator

Re: external tables [message #395269 is a reply to message #390336] Wed, 01 April 2009 00:08 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

What if my column has comma (,) as one of the character in it?

employee_report.csv:

001,"Hutt",Jabba,896743856,jabba@thecompany.com,18
002,"Simpson,A",Homer,382947382,homer@thecompany.com,20
003,"Kent",Clark,082736194,superman@thecompany.com,5
004,"Kid,K",Billy,928743627,billythkid@thecompany.com,9
005,"Stranger",Perfect,389209831,nobody@thecompany.com,23
006,"Zoidberg,S",Dr,094510283,crustacean@thecompany.com,1


Watch for "Simpson,A" in Row 2.

In this case the column gets broke as Simpson and A. I want it to be together in Column 2.





Re: external tables [message #395277 is a reply to message #395269] Wed, 01 April 2009 00:30 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Use optionally enclosed by '"' clause in your access parameters
Re: external tables [message #395279 is a reply to message #395277] Wed, 01 April 2009 00:43 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Worked! thanks!
Re: external tables [message #395289 is a reply to message #390336] Wed, 01 April 2009 01:34 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Got another question..below is my data in CSV file


8496,"Minteer, Donald Wesley, Jr.ACMH2009",ACMH,1,ACMH,Armstrong County Memorial Hospital dba ACMH,233,2009,Mature,03,OS002992L,"Minteer, Donald Wesley, Jr.",Donald,Wesley,"Minteer, Jr.",,,7/1/2003,7/1/2003,,,FALSE,Employed,,Domestic,1,Scope of Duty,40,,200,N,,,1,0,0,0,Abate App rec'd 1/8/06,Abate App rec'd 1/8/06,,,22,,1/29/2009,,,0,0,Preferred
8497,"Cippel, Jr., Joseph A.ACMH2009",ACMH,1,ACMH,Armstrong County Memorial Hospital dba ACMH,233,2009,Mature,03,MD057764L,"Cippel, Jr., Joseph A.",Joseph, A.,"Cippel, Jr.",,,8/1/1996,8/1/1996,,,FALSE,Employed,,Domestic,1,24 Hour,40,,200,N,,,1,0,0,0,Abate App rec'd 1/8/06,Abate App rec'd 1/8/06,,,22,,1/29/2009,,,0,0,Preferred

8498,McCann Karla DianeACMH2009,ACMH,1,ACMH,Armstrong County Memorial Hospital dba ACMH,233,2009,2,03,OS009819L,McCann Karla Diane,Karla,Diane,,,,4/24/2008,4/24/2008,,,FALSE,Employed,,Domestic,1,Scope of Duty,36,,200,N,,,1,0,0,0,"Per App signed 4-4, recd from J. Wrignt 4-8 and approved by CHART's U/W Committee 4-14. (RAL)
Per Joyce's 4-30 call IED chg to 4-24-8; iscommunication by credentialing. (RAL); Std to Pref eff 1/1/09
","Per App signed 4-4, recd from J. Wrignt 4-8 and approved by CHART's U/W Committee 4-14. (RAL)
Per Joyce's ",4-30 call IED chg to 4-24-8; iscommunication by credentialing. (RAL); Std to Pref eff 1/1/09,,199,,1/29/2009,,,0,0,Preferred

18,"Ally, SyedBRMC2006",BRMC,2,BRMC,Bradford Hospital d/b/a Bradford Regional Medical Center,2,2006,Mature,42,MD423253,"Ally, Syed",Syed,,Ally,,,12/1/2003,12/1/2003,,,FALSE,Employed,,Domestic,1,Scope of Duty,40,,43,N,,,1,0,0,0,,,,,0,,6/9/2006,,,0,0,Standard
19,"Breznik, John D.BRMC2006",BRMC,2,BRMC,Bradford Hospital d/b/a Bradford Regional Medical Center,2,2006,Mature,42,MD039253E,"Breznik, John D.",John,D.,Breznik,,,8/1/1989,8/1/1989,,,FALSE,Employed,,Domestic,1,Scope of Duty,52,,118,N,,,1,0,0,0,,,,,0,,6/9/2006,,,0,0,Preferred
20,"Datta, Bhupinder S.BRMC2006",BRMC,2,BRMC,Bradford Hospital d/b/a Bradford Regional Medical Center,2,2006,2,42,MD038778L,"Datta, Bhupinder S.",Bhupinder,S.,Datta,,,4/9/2005,4/9/2005,,,FALSE,Employed,,Domestic,1,Scope of Duty,40,,118,Y,I,1,0.333,0,0,0,,,,,0,,6/9/2006,,,0,0,Standard



You can see in row 8498 there is a new line. Due to this single record break into multiple and fetching improper result.

How to solve this?

Below is the access parameters
ORGANIZATION EXTERNAL (
  TYPE              oracle_loader
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
        records delimited  by newline  
        fields  terminated by ','
        optionally enclosed by '"'
        missing field values are null 
  )
  LOCATION ('empphy_withcustnum_final.csv')
)

[Updated on: Wed, 01 April 2009 01:45]

Report message to a moderator

Re: external tables [message #395319 is a reply to message #395289] Wed, 01 April 2009 02:30 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
I believe you have to manually correct the file and remove the incorrect newline in between fields.
Previous Topic: how to generate one id for 'x' number of records in a single sql query
Next Topic: How to process every line of a table
Goto Forum:
  


Current Time: Thu Dec 08 14:10:25 CST 2016

Total time taken to generate the page: 0.05404 seconds