Home » RDBMS Server » Server Utilities » SQL*Loader: Loading fixed-length file to table with VARRAY
| SQL*Loader: Loading fixed-length file to table with VARRAY [message #381045] |
Wed, 14 January 2009 23:55  |
mldsantos
Messages: 2
Registered: January 2009
|
Junior Member |
|
|
I want to load an ASCII file of fixed-record format with 77 bytes (including trailer 'X' and new line char at the end) to a table with a VARRAY field.
I would like to know if it's possible to load a fixed-length file to a table with a VARRAY. With my current setup as shown below, I get the error:
"SQL*Loader-403: Referenced column not present in table "LTR-UW-10-2"."
The table has a VARRAY field and the VARRAY field is defined as:
CREATE OR REPLACE TYPE "OPT-CHOICE-2" AS OBJECT
(
"OPT-TYP" VARCHAR2(01),
"OPT-TYP-CDE" VARCHAR2(04),
"CPY-MRK-LTR" VARCHAR2(5),
"CPY-MRK-ATT" VARCHAR2(5),
"RPL-DTE" NUMBER(07)
);
CREATE OR REPLACE TYPE "OPT-CHOICE-2-ARR" AS VARRAY(2) OF "OPT-CHOICE-2";
The table, with the VARRAY field, is defined as:
CREATE TABLE "LTR-UW-10-2"
(
"POL-ID" VARCHAR2(03),
"CLT-NBR" NUMBER(03),
"LTR-CDE" VARCHAR2(04),
"SYS-DTE" NUMBER(07),
"SYS-TME" NUMBER(07),
"OPT1-PS-TXT" VARCHAR2(03),
"OPT2-PS-TXT" VARCHAR2(03),
"SIGN-OFF-IND" VARCHAR2(01),
"OPT-CHOICES" "OPT-CHOICE-2-ARR"
);
The first record of the data file looks like this:
052037UZ1419991480000145EXCDISYG01 UQG01UQG011999160G02 AQG01AQG011999160X
Finally, my control file is:
LOAD DATA
INFILE '../data/LIPS.ARCM010.LTR.UW10-v'
INTO TABLE "LTR-UW-10-2"
TRUNCATE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
"POL-ID" POSITION(1:3)
,"CLT-NBR" POSITION(4:6)
,"LTR-CDE" POSITION(7:10)
,"SYS-DTE" POSITION(11:17)
,"SYS-TME" POSITION(18:24)
,"OPT1-PS-TXT" POSITION(25:27)
,"OPT2-PS-TXT" POSITION(28:30)
,"SIGN-OFF-IND" POSITION(31:31)
,"OPT-CHOICES" VARRAY Count(2)
(
"OPT-CHOICES" COLUMN OBJECT
( "OPT-TYP" CHAR(1)
,"OPT-TYP-CDE" CHAR(4)
,"CPY-MRK-LTR" CHAR(5)
,"CPY-MRK-ATT" CHAR(5)
,"RPL-DTE" INTEGER(7)
)
)
,X FILLER
)
If I do a normal INSERT INTO SQL, the row is loaded successfully
INSERT INTO "LTR-UW-10-2"
VALUES('052', 037, 'UZ14',1999148,0000145,'EXC','DIS','Y',
"OPT-CHOICE-2-ARR" ("OPT-CHOICE-2"('G','01 ','UQG01','UQG01',1999160),
"OPT-CHOICE-2"('G','02 ','AQG01','AQG01',1999160)));
SELECT * FROM "LTR-UW-10-2";
POL-ID CLT-NBR LTR-CDE SYS-DTE SYS-TME OPT1-PS-TXT OPT2-PS-TXT SIGN-OFF-IND OPT-CHOICES
052 37 UZ14 1999148 145 EXC DIS Y ((G, 01 , UQG01, UQG01, 1999160), (G, 02 , AQG01, AQG01, 1999160))
Unfortunately, I can't use INSERT to load the data since I'll be dealing with thousands of records.
I hope someone can shed light to my enquiry.
Thanks!
|
|
|
|
| Re: SQL*Loader: Loading fixed-length file to table with VARRAY [message #381203 is a reply to message #381045] |
Thu, 15 January 2009 14:13  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Get rid of: FIELDS TERMINATED BY ','
Use CONSTANT with your varray count: VARRAY Count(CONSTANT 2)
Change NUMBER(07) to CHAR(7)
Please see the demo below.
-- data you provided in test.dat:
052037UZ1419991480000145EXCDISYG01 UQG01UQG011999160G02 AQG01AQG011999160X
-- test.ctl:
LOAD DATA
INFILE 'test.dat'
INTO TABLE "LTR-UW-10-2"
TRUNCATE
TRAILING NULLCOLS
(
"POL-ID" POSITION(1:3)
,"CLT-NBR" POSITION(4:6)
,"LTR-CDE" POSITION(7:10)
,"SYS-DTE" POSITION(11:17)
,"SYS-TME" POSITION(18:24)
,"OPT1-PS-TXT" POSITION(25:27)
,"OPT2-PS-TXT" POSITION(28:30)
,"SIGN-OFF-IND" POSITION(31:31)
,"OPT-CHOICES" VARRAY Count(CONSTANT 2)
(
"OPT-CHOICES" COLUMN OBJECT
( "OPT-TYP" CHAR(1)
,"OPT-TYP-CDE" CHAR(4)
,"CPY-MRK-LTR" CHAR(5)
,"CPY-MRK-ATT" CHAR(5)
,"RPL-DTE" CHAR(7)
)
)
,X FILLER
)
-- types and tables you provided:
SCOTT@orcl_11g> CREATE OR REPLACE TYPE "OPT-CHOICE-2" AS OBJECT
2 (
3 "OPT-TYP" VARCHAR2(01),
4 "OPT-TYP-CDE" VARCHAR2(04),
5 "CPY-MRK-LTR" VARCHAR2(5),
6 "CPY-MRK-ATT" VARCHAR2(5),
7 "RPL-DTE" NUMBER(07)
8 );
9 /
Type created.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE "OPT-CHOICE-2-ARR" AS VARRAY(2) OF "OPT-CHOICE-2";
2 /
Type created.
SCOTT@orcl_11g> CREATE TABLE "LTR-UW-10-2"
2 (
3 "POL-ID" VARCHAR2(03),
4 "CLT-NBR" NUMBER(03),
5 "LTR-CDE" VARCHAR2(04),
6 "SYS-DTE" NUMBER(07),
7 "SYS-TME" NUMBER(07),
8 "OPT1-PS-TXT" VARCHAR2(03),
9 "OPT2-PS-TXT" VARCHAR2(03),
10 "SIGN-OFF-IND" VARCHAR2(01),
11 "OPT-CHOICES" "OPT-CHOICE-2-ARR"
12 );
Table created.
-- load:
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
-- results:
SCOTT@orcl_11g> SELECT * FROM "LTR-UW-10-2"
2 /
POL CLT-NBR LTR- SYS-DTE SYS-TME OPT OPT S
--- ---------- ---- ---------- ---------- --- --- -
OPT-CHOICES(OPT-TYP, OPT-TYP-CDE, CPY-MRK-LTR, CPY-MRK-ATT, RPL-DTE)
--------------------------------------------------------------------------------
052 37 UZ14 1999148 145 EXC DIS Y
OPT-CHOICE-2-ARR(OPT-CHOICE-2('G', '01', 'UQG01', 'UQG01', 1999160), OPT-CHOICE-
2('G', '02', 'AQG01', 'AQG01', 1999160))
SCOTT@orcl_11g>
|
|
|
|
| Re: SQL*Loader: Loading fixed-length file to table with VARRAY [message #381228 is a reply to message #381045] |
Thu, 15 January 2009 22:03 |
mldsantos
Messages: 2
Registered: January 2009
|
Junior Member |
|
|
Thanks for your feedback, Barbara.
" FIELDS TERMINATED BY ','" was due to a copy-paste mistake by me as I also tried a comma-delimited data file version for my load problem.
Anyway, I tried doing your suggestions and they seem not to work with my current version of SQL*Loader/Oracle and gives me this error message:
SQL*Loader-702: Internal error - ulpvdpo: OCIObjectGetAttr()
OCI-22305: attribute/method/parameter ""OPT-TYP"" not found
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
Details of my software versions are:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL*Loader version on an HP-UX host
SQL*Loader: Release 9.2.0.3.0
Would anyone know of a workaround to achieve fixed-format file loading to a VARRAY field with the versions mentioned above?
I hope I didn't hit a dead-end on this problem....
Thanks!
|
|
|
|
| Re: SQL*Loader: Loading fixed-length file to table with VARRAY [message #381230 is a reply to message #381228] |
Thu, 15 January 2009 22:50 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I don't see any significant difference in the 9i documentation:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch07.htm#1007181
Your Oracle version and SQL*Loader version don't match. If you have a 10g database, you should be able to get the 10g version of SQL*Loader. Otherwise, you may have some compatibility issues. There could also be other problems, like memory issues, which are common with varray loading, as mentioned in the documentation. Did you try to load the whole thing or just the row you posted? It is best to test with a small amount of simple data first. There could also be some other mistake that might be detectable if you were to post a copy and paste of your new control file and your resulting SQL*Loader log file. There are always other ways. One method would be to use either SQL*Loader or external tables to load the whole varray into one column in a staging table, then use SQL to parse it and insert it into the target table.
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 13:05:31 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
