Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: loading nulls into a varray with SQL Loader
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
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta
SQL> select * from t;
C
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 toolReceived on Mon Feb 06 2006 - 14:43:06 CST
![]() |
![]() |