• Oracle External Table Nullif Example

Can you please post the DATA FILE And CONTROL FILE.How the hell we are going to know what is the problem. Report message to a moderator Re: problem with sqlldr message #310131 is a reply to message #309983.

Oracle External Table Nullif Example

I've had a similar problem with dates (combination of todate and nullif) here:And it was solved nicely.My problem is that a numeric field in my CSV file can have these formats: 999,999,999.99 or just a dot '.' For null values.This is working: MINQUANTITY 'TONUMBER(:MINQUANTITY, 'D999999', 'NLSNUMERICCHARACTERS='.' ')'or MINQUANTITY NULLIF MINQUANTITY = '.'

But it is not working when I'm trying to combine both: MINQUANTITY 'TONUMBER(:MINQUANTITY, 'D999999', 'NLSNUMERICCHARACTERS='.' ') NULLIF:MINQUANTITY= '.' 'Here is the error log: Record 1: Rejected - Error on table MYTABLE, column MINQUANTITY.ORA-00917: missing commaHow can I combine these? Blade trinity fr rapidshare downloader. Your NULLIF condition should not be inside the double-quotes for the SQL string; and it needs to come first.

From:.The SQL string appears after any other specifications for a given column.The SQL string must be enclosed in double quotation marks. The SQL string is evaluated after any NULLIF or DEFAULTIF clauses, but before a date mask.So it should be: MINQUANTITY NULLIF MINQUANTITY = '.' 'TONUMBER(:MINQUANTITY, 'D999999', 'NLSNUMERICCHARACTERS='.' ')'(You can split into two lines for readability, both parts still apply to the `MINQUANTITY field).In the log that's reported as: MINQUANTITY NEXT., CHARACTERNULL if MINQUANTITY = 0X2e(character '.'

)SQL string for column: 'TONUMBER(:MINQUANTITY, 'D999999', 'NLSNUMERICCHARACTERS='.' ')'(Have you got your NLS chars the right way round?

This is treating, as the decimal separator, while your question suggests you're using. Either will work as long as the value is enclosed in double-quotes, of course).

Witold Iwaniec Try using in the control file: NULLIF MYCOLUMN=BLANKS eg: MYCOLUMN POSITION(24:535) CHAR NULLIF MYCOLUMN=BLANKS HTH Witold Witold Iwaniec Senior Software Developer NovaLIS Technologies wiwaniecatnovalistech.com - Please see the official ORACLE-L FAQ: - Author: Witold Iwaniec INET: wiwaniecatnovalistech.com Fat City Network Services - (858) 538-5051 FAX: (858) 538-5051 San Diego, California - Public Internet access / Mailing Lists. On 2 Apr 2001, at 10:50, gregory.t.norrisatmail.sprint. Wrote:When using direct path loads, is it possible to have sql.loader insert adefault value in place of NULL for blank entries? Please see the official ORACLE-L FAQ:-Author: Witold IwaniecINET: wiwaniecatnovalistech.comFat City Network Services - (858) 538-5051 FAX: (858) 538-5051San Diego, California - Public Internet access / Mailing Lists-To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuruatfatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Bala, Prakash Greg, look into the 'defaultif' option.

Prakash -Original Message- Sent: Monday, April 02, 2001 2:50 PM To: Multiple recipients of list ORACLE-L When using direct path loads, is it possible to have sql.loader insert a default value in place of NULL for blank entries? For conventional path loads I can use the 'nvl' function in my control file, but that unfortunately doesn't work with direct path. My employers like me, but not enough to let me speak for them.

Original Message-Sent: Monday, April 02, 2001 2:50 PMTo: Multiple recipients of list ORACLE-LWhen using direct path loads, is it possible to have sql.loader insert adefault value in place of NULL for blank entries? Gregory.t.norrisatmail.sprint.com Thanx to Witold and Prakash for suggesting NULLIF and DEFAULTIF. Unfortunately, it looks like both of those do basically the opposite of what I'm trying to accomplish. Essentially, I want to prevent sql.loader from ever inserting NULL in a column (which may not be numeric), substituting whatever value is appropriate for the given application instead. Something like using 'nvl(:field,'unknown')' in the field specification of the control file for conventional path loads.

As far as I can see. Thanx to Witold and Prakash for suggesting NULLIF and DEFAULTIF.Unfortunately, it looks like both of those do basically the opposite ofwhat I'm trying to accomplish. Essentially, I want to preventsql.loader from ever inserting NULL in a column (which may not benumeric), substituting whatever value is appropriate for the givenapplication instead. Something like using 'nvl(:field,'unknown')' inthe field specification of the control file for conventional path loads.As far as I can see, there's no way to do this within sql.loaderitself. So the choices would be to run a cleanup just after the load(which may or may not negate the speed benefits of using direct path),or to somehow pre-process the data file to fill in the missing values(perhaps an awk or perl script). Of course, if I'm missing somethingobvious please let me know. The sql.loader documentation seemsdesigned to confuse.

Bala, Prakash Gregory, On doing some research, found that a number column can be populated with a 0 using the direct load path, if the input value is null by having the following line in the control file: field1 integer external defaultif (field1 = 'NULL') But the same thing doesn't work for char columns. So you are right!

Prakash -Original Message- Sent: Tuesday, April 03, 2001 11:20 AM To: Multiple recipients of list ORACLE-L Thanx to Witold and Prakash for suggesting NULLIF and DEFAULTIF. Original Message-Sent: Tuesday, April 03, 2001 11:20 AMTo: Multiple recipients of list ORACLE-LThanx to Witold and Prakash for suggesting NULLIF and DEFAULTIF.Unfortunately, it looks like both of those do basically the opposite ofwhat I'm trying to accomplish. Essentially, I want to preventsql.loader from ever inserting NULL in a column (which may not benumeric), substituting whatever value is appropriate for the givenapplication instead. Something like using 'nvl(:field,'unknown')' inthe field specification of the control file for conventional path loads.As far as I can see, there's no way to do this within sql.loaderitself. So the choices would be to run a cleanup just after the load(which may or may not negate the speed benefits of using direct path),or to somehow pre-process the data file to fill in the missing values(perhaps an awk or perl script). Of course, if I'm missing somethingobvious please let me know.

The sql.loader documentation seemsdesigned to confuse. (-:Cheers!-Please see the official ORACLE-L FAQ:-Author: Bala, PrakashINET: prakash.balaatcingular.comFat City Network Services - (858) 538-5051 FAX: (858) 538-5051San Diego, California - Public Internet access / Mailing Lists-To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuruatfatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). Witold Iwaniec Sorry I misunderstood your request. Try something like this: THEVAL POSITION(111:120) DECIMAL EXTERNAL 'NVL(:THEVAL, 33)' It works for me - inserts 33 into numeric column when the datafile contains spaces or nothing (if it is the last field) HTH Witold Witold Iwaniec Senior Software Developer NovaLIS Technologies wiwaniecatnovalistech.com - Please see the official ORACLE-L FAQ: - Author: Witold Iwaniec INET: wiwaniecatnovalistech.com Fat.

On 3 Apr 2001, at 7:20, gregory.t.norrisatmail.sprint. Wrote:Thanx to Witold and Prakash for suggesting NULLIF and DEFAULTIF.Unfortunately, it looks like both of those do basically the opposite ofwhat I'm trying to accomplish. Essentially, I want to preventsql.loader from ever inserting NULL in a column (which may not benumeric), substituting whatever value is appropriate for the givenapplication instead.

Something like using 'nvl(:field,'unknown')' inthe field specification of the control file for conventional path loads.As far as I can see, there's no way to do this within sql.loaderitself. So the choices would be to run a cleanup just after the load(which may or may not negate the speed benefits of using direct path),or to somehow pre-process the data file to fill in the missing values(perhaps an awk or perl script). Of course, if I'm missing somethingobvious please let me know. The sql.loader documentation seemsdesigned to confuse. (-:Cheers!Witold IwaniecSenior Software DeveloperNovaLIS Technologieswiwaniecatnovalistech.com. Please see the official ORACLE-L FAQ:-Author: Witold IwaniecINET: wiwaniecatnovalistech.comFat City Network Services - (858) 538-5051 FAX: (858) 538-5051San Diego, California - Public Internet access / Mailing Lists-To REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuruatfatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).