Home » RDBMS Server » Server Utilities » sql loader using function returing a varray
sql loader using function returing a varray [message #227254] Tue, 27 March 2007 13:28 Go to next message
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 #227257 is a reply to message #227254] Tue, 27 March 2007 13:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96652/ch07.htm#1007181
Re: sql loader using function returing a varray [message #227259 is a reply to message #227254] Tue, 27 March 2007 13:38 Go to previous messageGo to next message
acxiomdba
Messages: 2
Registered: March 2007
Junior Member
Thanks. But I have read that. What I am trying to do is different: using sql loader EXPRESSION with a function that returns a Varray. The link provides an example that constructs a varray directly from the source data, not exactly the same.
Re: sql loader using function returing a varray [message #227633 is a reply to message #227259] Wed, 28 March 2007 19:10 Go to previous message
Barbara Boehmer
Messages: 8636
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> 



Previous Topic: SQL Load..char string problem with 10g
Next Topic: loading data into multiple tables , help
Goto Forum:
  


Current Time: Fri Dec 09 15:30:35 CST 2016

Total time taken to generate the page: 0.10751 seconds