| Two challenges with the SQL*Loader tool [message #71411] |
Wed, 30 October 2002 09:31  |
Mark Grimshaw
Messages: 73
Registered: June 2002
|
Member |
|
|
I have searched a number of newsgroups and have yet to find the answer to a problem I have. I bet that no one can provide an answer to this!
I have some data that has been off loaded from an old
mainframe database - an illustration of the format is below: -
1Test11000Element1Element2Element3
2Test21001Element1Element2 * see below
* Element is missing but spaces are used to pad the field/record to the required length.
Note the lack of delimiters between the fields.
The table definition and Control file for which I am trying to load the above data into will reveal the problem.
TABLE TEST
(
TestNum NUMBER(1),
Testname VARCHAR2(5),
Value NUMBER(4),
Strings STR_ARRAY_T
)
STR_ARRAY_T is defined as a VARRAY(3) OF VARCHAR2(7)
The problem is that no matter how hard I try I cannot seem to be able to get my Control file correct so that I can separate the 3 strings into 7 char strings.
Here is one of the Control files that I have tried without success: -
INSERT INTO TABLE TEST APPEND
(
TestNum POSITION(01:01) INTEGER EXTERNAL,
Testname POSITION(02:06) CHAR(5),
Value POSITION(07:10) INTEGER EXTERNAL,
Strings VARRAY COUNT(CONSTANT 3) (STRINGS(CHAR(7))
)
The error that I get goes something like:
"...Rejected - Error on table TEST, column Strings,
Field in data file exceeds maximum length".
I thought that the answer lay in trying to use the POSITION specifier with the VARRAY but you don't seem to be able to do that. How can you get the Loader to split the 21 characters up into 3 separate strings.
The real problem of course is that I will have a major problem in getting the data offloaded from the old mainframe in a format more malleable.
I challenge someone to be able to parse the data file above.
TYIA
Mark Grimshaw
|
|
|
|
| Re: Two challenges with the SQL*Loader tool [message #71412 is a reply to message #71411] |
Wed, 30 October 2002 11:22  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
there should be no problem at all!,
did you create str_type as object?????
create type str_type as object (c1 varchar2(8));
/
create type str_array_t as varray(3) of str_type;
/
create TABLE TEST22
(
TestNum NUMBER(1),
Testname VARCHAR2(5),
Value NUMBER(4),
Strings STR_ARRAY_T
)
/
C:>type test.ctl
load data
infile 'test.dat'
into table test22
replace
(
TestNum POSITION(01:01) INTEGER EXTERNAL,
Testname POSITION(02:06) CHAR(5),
Value POSITION(07:10) INTEGER EXTERNAL,
Strings VARRAY COUNT(CONSTANT 3) ( Strings column object (c1 char(8))))
C:>type test.dat
1test11000Exxment1Element2Element3
2Test21001Element1Element2
C:>sqlldr userid=mag/mag control=test.ctl
SQL*Loader: Release 8.1.6.0.0 - Production on Wed Oct 30 14:05:10 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 3
C:>sqlplus mag/mag
SQL*Plus: Release 8.1.6.0.0 - Production on Wed Oct 30 14:05:21 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL> desc test22
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TESTNUM NUMBER(1)
TESTNAME VARCHAR2(5)
VALUE NUMBER(4)
STRINGS STR_ARRAY_T
SQL> select * from test22;
TESTNUM TESTN VALUE
---------- ----- ----------
STRINGS(C1)
----------------------------------------------------------------------------------------------------
1 test1 1000
STR_ARRAY_T(STR_TYPE('Exxment1'), STR_TYPE('Element2'), STR_TYPE('Element3'))
2 Test2 1001
STR_ARRAY_T(STR_TYPE('Element1'), STR_TYPE('Element2'), STR_TYPE(NULL)) |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
