um...just a thought but how about setting
marketingcode to char(3) in the PL/SQL code snippet.
I ran into this similar problem a couple days ago.
Had a var as varchar2 in PL/SQL but in the table it
was char. Changed my PL/SQL var to char, cursor in my
code worked with ltrim and rtrim functions whereas
before it wasn't.
hth
mkb
- DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> wrote:
> 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: DWILLIAMS_at_LIFETOUCH.COM
>
> 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: ListGuru_at_fatcity.com (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).
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: mkb
INET: mkb125_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Wed Aug 21 2002 - 14:58:33 CDT
