| sql loader using function returing a varray [message #227254] |
Tue, 27 March 2007 13:28  |
acxiomdba
Messages: 2
Registered: March 2007
|
Junior Member |
|
|
I have a ascii file of fix record format, 31 bytes long including new line charater at the end, like this:
010012481001000000000001000000
010012482000000000100000000000
I want to load the data into a table defined like this:
CREATE OR REPLACE
TYPE Adult_ageGroup_arr AS VARRAY(21) OF NUMBER;
CREATE TABLE ADULTAGE
(
ZIPOUT CHAR(9 BYTE),
AGEGROUP ADULT_AGEGROUP_ARR
)
The AGEGROUP column is a varray whose value is obtained by parsing the last 21 characters. For example, for the first line "001000000000001000000" will be translated into a varray with element of (3,15).
My control file is:
LOAD DATA
INFILE 'test3.dat' "fix 31"
BADFILE 'BAD.txt'
APPEND INTO TABLE adultAge
(
ZIPOUT POSITION(1-9) char,
AGEGROUP POSITION(10-30) "convert_adult_agegroup(:AGEGROUP)"
)
the function is defined as:
CREATE OR REPLACE FUNCTION convert_adult_agegroup (agegroupbytes IN VARCHAR2)
RETURN Adult_ageGroup_arr
AS
varr Adult_ageGroup_arr := Adult_ageGroup_arr();
ageGroupIndicator CHAR(1);
ageGroup NUMBER := 1;
BEGIN
loop
ageGroupIndicator := SUBSTR(agegroupbytes, ageGroup, 1);
if ageGroupIndicator = '1'
then
varr.extend;
varr(varr.count) := ageGroup;
end if;
if ageGroup >= 21 then
exit;
end if;
ageGroup := ageGroup+1;
end loop;
return varr;
--end if;
END convert_adult_agegroup;
/
if I run this:
DECLARE
result adult_agegroup_arr;
BEGIN
result := convert_adult_agegroup('100010000000000000000');
IF result.COUNT <1 THEN
dbms_output.put_line('IS EMPTY!');
ELSE
FOR i IN result.FIRST .. result.LAST
loop
dbms_output.put_line(result(i));
end loop;
insert into adultage values('000000000', result);
commit;
END IF;
end;
/
I get correct result in table adultage. And if I modify the convert_adult_agegroup function to just return a NUMBER and modify the AGEGROUP column type to NUMBER, I can sql load witout problem.
But if I use sql loader, i got this message: SQL*Loader-418: Bad datafile datatype for column AGEGROUP.
Does SQL loader support what I am trying to achieve? If yes, what did I do wrong?
Thanks.
Jie
[Updated on: Tue, 27 March 2007 13:37] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: sql loader using function returing a varray [message #227633 is a reply to message #227259] |
Wed, 28 March 2007 19:10  |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- test3.dat:
010012481001000000000001000000
010012482000000000100000000000
-- test.ctl:
LOAD DATA
INFILE 'test3.dat'
BADFILE 'bad.txt'
APPEND INTO TABLE adultAge
(zipout POSITION (1:9),
agegroupchar BOUNDFILLER POSITION (10:30),
agegroup EXPRESSION "convert_adult_agegroup (:agegroupchar)")
SCOTT@10gXE> CREATE OR REPLACE TYPE adult_agegroup_arr AS VARRAY (21) OF NUMBER;
2 /
Type created.
SCOTT@10gXE> CREATE TABLE adultage
2 (zipout CHAR (9 BYTE),
3 agegroup adult_agegroup_arr)
4 /
Table created.
SCOTT@10gXE> CREATE OR REPLACE FUNCTION convert_adult_agegroup
2 (agegroupbytes IN VARCHAR2)
3 RETURN adult_agegroup_arr
4 AS
5 varr adult_ageGroup_arr := adult_agegroup_arr();
6 BEGIN
7 FOR ageGroup IN 1 .. 21 LOOP
8 IF SUBSTR (agegroupbytes, ageGroup, 1) = '1'
9 THEN
10 varr.EXTEND;
11 varr(varr.COUNT) := ageGroup;
12 END IF;
13 END LOOP;
14 RETURN varr;
15 END convert_adult_agegroup;
16 /
Function created.
SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@10gXE> COLUMN agegroup FORMAT A40
SCOTT@10gXE> SELECT * FROM adultage
2 /
ZIPOUT AGEGROUP
--------- ----------------------------------------
010012481 ADULT_AGEGROUP_ARR(3, 15)
010012482 ADULT_AGEGROUP_ARR(10)
2 rows selected.
SCOTT@10gXE>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
