| SQL Loader Problem [message #124808] |
Tue, 21 June 2005 11:20  |
abc123
Messages: 24
Registered: March 2005
Location: sa
|
Junior Member |
|
|
Hi all,
I am uploading data through SQL *Loader into a table.
One of the columns is declared as INTEGER EXTERNAL in the control file of sqlloader,while the column in which data will be inserted is VARCHAR2.
This field contains numeric data accepted as varchar.
But somehow sqlloader does not reject the record in the input file which has character(aphabetic) data correspoing to this column.
Does INTERGER EXTERNAL allow alphabetic data to be inserted into column declared as varchar2
but expecting a numeric value?
IF yes,is there any way i could check that a chracter string contains only numeric data through coontrol file.
Also,what to do if field order changes in datafile for a record such that a numeric data appears in place of another numeric data,then wrong data will get inserted into table whithout any error.
Please suggest solution.
|
|
|
|
| Re: SQL Loader Problem [message #124960 is a reply to message #124808] |
Wed, 22 June 2005 09:26  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
If you have numeric data, and you want to keep numeric data, and you want to make sure that only numeric data goes in, why not make your column be numeric? Why make it varchar2?
Here is what the docs say about integer external:
| Quote: |
Numeric EXTERNAL
The numeric EXTERNAL datatypes are the numeric datatypes (INTEGER, FLOAT, DECIMAL, and ZONED) specified as EXTERNAL, with optional length and delimiter specifications. The length is in bytes unless character-length semantics are used for the datafile. In that case, the length is in characters. See Character-Length Semantics.
These datatypes are the human-readable, character form of numeric data. The same rules that apply to CHAR data with regard to length, position, and delimiters apply to numeric EXTERNAL data. See CHAR for a complete description of these rules.
The syntax for the numeric EXTERNAL datatypes is shown as part of datatype_spec.
Note:
The data is a number in character form, not binary representation. Therefore, these datatypes are identical to CHAR and are treated identically, except for the use of DEFAULTIF. If you want the default to be null, use CHAR; if you want it to be zero, use EXTERNAL. See Using the WHEN, NULLIF, and DEFAULTIF Clauses.
FLOAT EXTERNAL data can be given in either scientific or regular notation. Both "5.33" and "533E-2" are valid representations of the same value.
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
