How to import data into a varray using sqlldr

Question

I am trying to import data into Oracle database using sqlldr.

I am using this documentation, but not successfully: sqlldr and sqlldr2

Decribing what I am doing:

create the varray type and the table:

CREATE OR REPLACE TYPE fv_integer_varray_12 AS VARRAY(12) OF INTEGER NOT NULL;

CREATE OR REPLACE TYPE VC_Array_MInteger_12 AS Object (
  fv_array fv_integer_varray_12,
  MEMBER FUNCTION   typeDimension   RETURN NUMBER ,
  MEMBER FUNCTION typeName RETURN VARCHAR2);
/  

CREATE TABLE fv_varray_12 (
id NUMBER, 
fv fv_integer_varray_12,
PRIMARY KEY (id)
);

Create the control file:

options (errors=9999999, rows=5)
load data
 characterset WE8MSWIN1252
 infile '<path>'
 badfile '<path>'
 discardfile '<path>'
 into table fv_varray_12
 fields terminated by " "
( id, fv VARRAY COUNT(12) (fv))

The data to be load is:

8 18 29 38 9 16 15 14 16 18 16 13 15 
9 22 31 32 8 13 18 10 15 18 16 13 13

When I try to load it through the command:

sqlldr user/pass control=imp_fv_12dim.ctl

I get the error:

SQL*Loader-403: Referenced column not present in table FV_VARRAY_12.

But it is not true as both the id and fv columns are there.

There must be something wrong in my control file. How can I fix it?

Alex Poole Alex Poole · Accepted Answer · 2017-12-04T17:46:14

You are just missing the keyword CONSTANT:

fv VARRAY COUNT(CONSTANT 12) (fv)

That is missing from the example in the documentation (but then their data also look odd); but it is shown as required in the count_spec syntax diagram, which is linked to from the 12c documentaion on loading VARRAYs - but is also available in earlier versions of the documentation too..

With your sample data and just that modification to the control file:

options (errors=9999999, rows=5)
load data
characterset WE8MSWIN1252
infile 'imp_fv_12dim.dat'
badfile 'imp_fv_12dim.bad'
discardfile 'imp_fv_12dim.dis'
into table fv_varray_12
fields terminated by " "
( id,
  fv VARRAY COUNT(CONSTANT 12) (fv)
)

then it works:

> sqlldr user/pass control=imp_fv_12dim.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Mon Dec 4 17:41:04 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2

And querying shows the data:

column fv format a80
select * from fv_varray_12;

        ID FV                                                                              
---------- --------------------------------------------------------------------------------
         8 FV_INTEGER_VARRAY_12(18, 29, 38, 9, 16, 15, 14, 16, 18, 16, 13, 15)             
         9 FV_INTEGER_VARRAY_12(22, 31, 32, 8, 13, 18, 10, 15, 18, 16, 13, 13)

How to import data into a varray using sqlldr

1 Answers