Re: loading nulls into a varray with SQL Loader

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Mon, 06 Feb 2006 20:43:06 +0000
Message-ID: <91dfu1pr1ijhs9nhmk5so82t8bcmqqapgr@4ax.com>

On 1 Feb 2006 06:51:40 -0800, "Kevin" <kevinherring_at_lycos.co.uk> wrote:

>Right I am trying to load some data using sqlldr into Oracle 9.
>
>I have a table that contains a varray, which is declared thus:
>type CONS_PERIOD_VALUES as VARRAY (50) of NUMBER(13,5)
>
>Now the thing is that I want the varray to be 'padded out' with nulls
>so that it always has 50 elements, i.e. if I put 40 values in, then
>there should be 10 nulls at the end.
>but if I give it (where the first number is the number of elements to
>follow):
>50,.0076,..........0076,.0074,.0075,.0075,.0073,.0078,.0074,.0072,"",""
>it doesnt load it. Same as:
>50,.0076,..........0076,.0074,.0075,.0075,.0073,.0078,.0074,.0072,,
>
>And if I give it this:
>48,.0076,..........0076,.0074,.0075,.0075,.0073,.0078,.0074,.0072
>I only get 48 values.
>
>So how do I put nulls in the varray????

How about:

andyh_at_excession /cygdrive/g/files
$ cat test.ctl
LOAD DATA INFILE *
INTO TABLE t REPLACE
FIELDS TERMINATED BY ","
(
c VARRAY COUNT(CONSTANT 5) ( c NULLIF c.c="null" ) )
BEGINDATA

1,2,3,4,5
1,2,3,4,null
1,2,3,null,null
1,2,null,null,null
1,null,null,null,null

null,null,null,null,null

andyh_at_excession /cygdrive/g/files
$ sqlldr userid=test/test_at_xe2 control=test.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Feb 6 20:38:57 2006

Commit point reached - logical record count 6

andyh_at_excession /cygdrive/g/files
$ sqlplus test/test_at_xe2

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 6 20:39:04 2006

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta

SQL> select * from t;

CONS_PERIOD_VALUES(1, 2, 3, 4, 5)
CONS_PERIOD_VALUES(1, 2, 3, 4, NULL)
CONS_PERIOD_VALUES(1, 2, 3, NULL, NULL)
CONS_PERIOD_VALUES(1, 2, NULL, NULL, NULL)
CONS_PERIOD_VALUES(1, NULL, NULL, NULL, NULL)
CONS_PERIOD_VALUES(NULL, NULL, NULL, NULL, NULL)

6 rows selected.

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Received on Mon Feb 06 2006 - 14:43:06 CST