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 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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 Go to previous messageGo to next message
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 Go to previous message
Barbara Boehmer
Messages: 8625
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.
Previous Topic: Import error
Next Topic: EXP-00030: Unexpected End-Of-File encountered while reading input
Goto Forum:
  


Current Time: Sun Dec 04 04:56:52 CST 2016

Total time taken to generate the page: 0.08441 seconds