Geez, after re-reading my post, it seems that it didn't make much sense to me, so to clarify...
I had a cursor in my procedure that took as an IN param a varchar2 variable. The cursor failed to return any rows because in my where clause I was comparing a char field against a varchar2 variable. I then decided to create a local variable of type char and assigned my IN varchar2 variable to the local char variable. Using this in my cursors where clause I was then able to get rows back. There, sounds much better. mkb --- mkb <[EMAIL PROTECTED]> wrote: > 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 > === message truncated === __________________________________________________ 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).
