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 <mkb125_at_yahoo.com> 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 <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
>
=== 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: 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 - 16:00:36 CDT
