RE: Sql loader and column defaults.

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Fri, 20 Oct 2000 14:32:05 -0400
Message-Id: <10655.119872@fatcity.com>

Thanks for the response.
Try loading the data with nothing not even a null in the datafile for the = Y/N field and see what gets loaded. Mine did not load anything into the = default valued field.
Ron

>>> BVanderMey_at_Works.com 10/20/00 03:10PM >>>
Ron,

Yes, just yesterday. I was loading a file in a database that had a Y/N = flag
field. The column defaulted to Y, not null, with a check constraint of Y or N. When I loaded the data using SQL Loader, the field had nothing in = it
(a null). It received a "cannot load null field into a not null column" error. I had also thought that the column would default to Y. Got around it by rebuilding the SQL Loader input file and putting a Y in that field.

Bob VanderMey
Oracle DBA
Works.com
847 573-2687
bvandermey_at_works.com=20
565 Lakeview Parkway, Suite 250
Vernon Hills, IL 60061
http://www.works.com=20

-----Original Message-----
Sent: Friday, October 20, 2000 1:35 PM
To: Multiple recipients of list ORACLE-L

List,
  I have a question on the SQL loader and column defaults of a table. = Oracle
7.3.4.0.1 running on a Dell box with Novell os. The default for the = numeric
columns =3D 0 and the data is loaded using SQL loader 7.3.4.0.1. If the = data
source file contains blanks between the comma delimiters, the load fails with invalid expression errors. If the data source does not contain = anything
between the comma delimiters the load succeeds but nothing is entered into the column with the default=3D0.
Using SQL and the insert command, if the column is not called in the = command
then a zero is inserted into the column. If the column is called and no value placed between the commas in the values clause, the command replies with a missing expression response.
I thought that the default clause on a column would work even with SQL Loader in case the source data did not contain data in all of the proper columns defined in the control file.
Anyone else experience this behavior?=20 ROR =AA=BF=AA --=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com=20 --=20
Author: Ron Rogers
  INET: RROGERS_at_galottery.org=20

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
--
Author: Bob VanderMey
  INET: BVanderMey_at_Works.com=20

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Fri Oct 20 2000 - 13:32:05 CDT