SQL*Loader & UNICODE(?) & ORA-01008: no all variables bound

From: Steven Whatley <swhatley_at_blkbox.com>
Date: 21 May 1999 15:38:00 GMT
Message-ID: <927301080.844021@news.blkbox.com>

We are running Oracle v7.3.4.3.0 and have a development, integration, and production database instances. delevopment is on one Solaris (2.6) box. integration and production are on another box. We have converted development and integration to UNICODE to support cyrillic. We will soon convert production. They all share the same Oracle executables.

We had a problem come up with SQL*Loader since we converted to UNICODE. Take the following tab delimited file: (the actual file has tabs and no header.)

Part_ID Qty weight val1 val2 val3

beam       5    190.5   255  128  255
bolt       100  5.9     207  128  192
nuts       99   1.1     64   24   32
lock rings 101  0.5     97   23   75

In this case the weight in the data file is the total weight of the entire quantity. But the requirement is to store in the database the weight per part and not store the total weight.

So we have a SQL*Loader control file as such:

load data
into table tt_test
insert
fields terminated by X'09'
trailing nullcols

( part_id	"LTRIM(RTRIM(:part_id))",
  qty		DECIMAL EXTERNAL,
  weight	":weight / :qty",
  val1		DECIMAL EXTERNAL,
  val2		DECIMAL EXTERNAL,
  val3		DECIMAL EXTERNAL

)

This worked ok for 3 years, until we converted to UNICODE. We found this out over a month later. The problem is with :qty in the weight field. We keep getting "ORA-01008: Not all variables bound". It seems that now all bind varibles on the right must be the same as the column name on the left except at the end. The following does work:

load data
into table tt_test
insert
fields terminated by X'09'
trailing nullcols

( part_id	"LTRIM(RTRIM(:part_id))",
  qty		DECIMAL EXTERNAL,
  tot_weight	DECIMAL EXTERNAL,
  val1		DECIMAL EXTERNAL,
  val2		DECIMAL EXTERNAL,
  val3		DECIMAL EXTERNAL,
  weight	":tot_weight / :qty"

)

We worked around the problem but are not sure what happened and why the rule changed. This appears to be related to the UNICODE conversion. In production (not UNICDE yet), the first control file works fine. but in delevopment and intergration (both UNICODE converted) we have to use the second control file (and a corresponding temp table). What changed the rules? Any info will be appreciated.

Thanks,
Steven
--

                 _|_  |  _|_   "I am the way and the truth and the life.
                  | --|-- |     No one comes to the Father except through
Steven Whatley    |   |   |      me.  If you really knew me, you would
Houston, Texas        |           know my Father as well.  From now on,
swhatley_at_blkbox.com   |            you do know him and have seen him."
http://www.blkbox.com/~swhatley/        -- Jesus Christ (John 14:6-7 NIV)

Received on Fri May 21 1999 - 10:38:00 CDT