Tom - The developer reports that he tried this but it didn't work. The third
position is still a space value. Thanks to everyone for the good replies.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Wednesday, August 21, 2002 3:13 PM
To: '[EMAIL PROTECTED]'
Cc: DENNIS WILLIAMS
Dennis,
Try changing your insert statement to:
insert into JOBOFFERFACT_LOAD
(LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID,
MARKETINGCODE,
TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME,
PACKAGEPRICE,
PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE,
PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK,
PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG)
VALUES
(LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,'
'),
TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE,
PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE,
PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK,
PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ;
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, August 21, 2002 2:04 PM
To: Multiple recipients of list ORACLE-L
In response to the questions for more details, here are the PL/SQL code and
SQL Loader control file. Everything is varchar2(2), explicitly defined as
such in PL/SQL. Thanks for all the nice replies.
PL/SQL snippets
<...snip...>
marketingcode VARCHAR2(3);
<...snip...>
FILELOCATION := '/usr/users/madmload/text_files';
OPEN_MODE := 'r';
FILENAME := 'prodload.txt';
FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE );
<...snip...>
UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING);
marketingcode := substr(outputstring, 21, 3);
<...snip...>
insert into JOBOFFERFACT_LOAD
(LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID,
MARKETINGCODE,
TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME,
PACKAGEPRICE,
PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE,
PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK,
PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES
(LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE,
TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE,
PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE,
PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK,
PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ;
============================================
Sql*Loader script
LOAD DATA
INFILE '/usr/users/madmload/joblid.txt'
BADFILE '/usr/users/madmload/jobload.bad'
APPEND
INTO TABLE JOBFACT
(
JOBNBR POSITION(1:10) CHAR,
LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL,
MDRPRIMARYID POSITION(21:28) CHAR,
MARKETINGCODE POSITION(29:31) CHAR,
SUBPROGRAMCODE POSITION(32:32) CHAR,
TERRITORYCODE POSITION(33:34) CHAR,
SUBTERRITORYCODE POSITION(33:36) CHAR,
SELLINGMETHODCODE POSITION(37:37) CHAR,
BIDIND POSITION(38:38) CHAR,
PDKIND POSITION(39:39) CHAR,
PDKPARTNBR POSITION(40:44) CHAR,
RETAKEIND POSITION(45:45) CHAR,
PLANTCODE POSITION(46:46) CHAR,
PLANTRECEIPTDATE POSITION(47:56) DATE "YYYY/MM/DD" NULLIF
PLANTRECEIPTDA,
PLANTRECEIPTYEAR POSITION(47:50) INTEGER EXTERNAL,
PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL,
PHOTOGRAPHYDATE POSITION(57:66) DATE "YYYY/MM/DD" NULLIF
PHOTOGRAPHYDATE=BLANKS,
SHIPDATE POSITION(67:76) DATE "YYYY/MM/DD" NULLIF SHIPDATE=BLANKS,
SHOTQTY POSITION(77:80) INTEGER EXTERNAL,
SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL,
PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL,
UNPAIDPACKAGEQTY POSITION(89:92) INTEGER EXTERNAL,
XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL,
CASHRECEIVEDAMT POSITION(97:105) DECIMAL EXTERNAL,
CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL,
ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL,
ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL,
CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL,
SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL,
TERRITORYCMSNAMT POSITION(151:159) DECIMAL EXTERNAL,
TERRITORYEARNINGSAMT POSITION(160:168) DECIMAL EXTERNAL,
EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL,
SOURCEFISCALYEAR CONSTANT '2003',
PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL,
PROOFCOUNT POSITION(179:182) DECIMAL EXTERNAL,
SEASONDESC POSITION(183:183) DECIMAL EXTERNAL,
EXTRACTDATE POSITION(184:193) DATE "YYYY/MM/DD" NULLIF
EXTRACTDATE=BLANKS,
FUNPACKJOB POSITION(194:194) CHAR,
CONNECTJOB POSITION(195:195) CHAR,
STICKYALBUMJOB POSITION(196:196) CHAR,
PAYSTATUS POSITION(197:197) CHAR,
ORIGINALDATERECEIVED POSITION(198:207) DATE "YYYY/MM/DD" NULLIF
ORIGINALDATERE,
CMSNSTATUS POSITION(208:208) CHAR
)
==================================================
All tables have the marketingcode field defined as varchar2(3) (none are
char(3))
Bruce
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
