SQL*LOADER and REF COLUMN

[jadexy]

Hallo,

I have already posted and hope somebody can help me.

I have the following problem. I have 2 tables which I created the following way:

CREATE TYPE gemark_schluessel_t AS OBJECT(
gemark_id NUMBER(8),
gemark_schl NUMBER(4),
gemark_name VARCHAR2(45)
);
/

CREATE TABLE gemark_schluessel_tab OF gemark_schluessel_t(
constraint pk_gemark PRIMARY KEY(gemark_id)
);

CREATE TYPE flurstueck_t AS OBJECT(
flst_id NUMBER(8),
flst_nr_zaehler NUMBER(4),
flst_nr_nenner NUMBER(4),
zusatz VARCHAR2(2),
flur_nr NUMBER(2),
gemark_schluessel REF gemark_schluessel_t,
flaeche SDO_GEOMETRY
);
/

CREATE TABLE flurstuecke_tab OF flurstueck_t(
constraint pk_flst PRIMARY KEY(flst_id),
constraint uq_flst UNIQUE(flst_nr_zaehler,flst_nr_nenner,zusatz,flur_nr),
flst_nr_zaehler NOT NULL,
flur_nr NOT NULL,
gemark_schluessel REFERENCES gemark_schluessel_tab
);


Now I have data in the gemark_schluessel_tab which looks like this (a sample):
1 101 Borna
2 102 Draisdorf
...

Now I wanna load data in my flurstuecke_tab with SQL*Loader and there I have problems with my ref column gemark_schluessel.

One data record looks like this in my file (it is without geometry)
1|97|7||1|1|

If I wanna load my data record, it does not work. The reference (I would do this when I would insert in by hand: select ref(s) from gemark_schluessel_tab s (the system generated OID) should be taken from gemark_schluessel_tab.

LOAD DATA
INFILE *
TRUNCATE
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE FLURSTUECKE_TAB
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (
flst_id,
flst_nr_zaehler,
flst_nr_nenner,
zusatz,
flur_nr,
gemark_schluessel REF(CONSTANT 'GEMARK_SCHLUESSEL_TAB',GEMARK_ID),
gemark_id FILLER
)
BEGINDATA
1|97|7||1|1|

Is there a error I made?

Thanks in advance
Tig

 

[oradba101]

Hi, jadexy

What is the error message you receive?

Regards,


William Chadbourne
Oracle DBA

 

[jadexy]

Hallo William,

this is my script I load with SQL*LOADER

LOAD DATA
INFILE *
TRUNCATE
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE FLURSTUECKE_TAB
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (
flst_id,
flst_nr_zaehler,
flst_nr_nenner,
zusatz,
flur_nr,
gemark_schluessel REF(CONSTANT 'GEMARK_SCHLUESSEL_TAB',GEMARK_ID),
GEMARK_ID FILLER CHAR(100)
)
BEGINDATA
1|97|7||1|1|

and this is the message in the log-file

SQL*Loader: Release 10.1.0.4.0 - Production on Thu Aug 4 13:41:41 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Control File: /usr/oracle/import/flurstueck2.ctl
Data File: /usr/oracle/import/flurstueck2.ctl
Bad File: /usr/oracle/import/flurstueck2.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: 1:1 = 0X23(character '#'), in next physical record
Path used: Conventional

Table FLURSTUECKE_TAB, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FLST_ID FIRST * | CHARACTER
FLST_NR_ZAEHLER NEXT * | CHARACTER
FLST_NR_NENNER NEXT * | CHARACTER
ZUSATZ NEXT * | CHARACTER
FLUR_NR NEXT * | CHARACTER
GEMARK_SCHLUESSEL DERIVED REF
Arguments are:
CONSTANT 'GEMARK_SCHLUESSEL_TAB'
GEMARK_ID
GEMARK_ID NEXT 100 | CHARACTER
(FILLER FIELD)

Record 1: Rejected - Error on table FLURSTUECKE_TAB, column GEMARK_ID.
error converting data
ORA-26007: invalid value for SETID or OID column



Table FLURSTUECKE_TAB:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 89344 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 1
Total logical records discarded: 0

I really don't know what is wrong. I tried idffernt ways. Hope somebody can help.

Thanks in advance Tig

 

[Corran007]

so gemark_id is an auto increment number(system increases it)?