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 <[EMAIL PROTECTED]> 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: [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). __________________________________________________ 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: [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).
