| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/Sql question
Tom, mkb, Stephen, Kevin
Thanks to everyone for your ideas on this one. Tom was entirely correct when
he said that I needed to sit with the developer and gut it out. I wrote a
number of test cases that demonstrated that the rtrim() should resolve the
problem. He kept saying that he had tried that. Finally I suggested that he
repeat his test with rtrim(). He did and discovered that previously he had
experienced a PL/SQL compilation error but because he was using SQL
Worksheet the error was hidden from him. Therefore he hadn't tested rtrim()
at all. This time he corrected the compilation error and the program worked.
Problem solved! But it sure was helpful when the developer felt that he was
experiencing some weird PL/SQL bug to have some real experts dispute that
assumption.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Wednesday, August 21, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L
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,
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,
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,
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-LReceived on Fri Aug 23 2002 - 15:23:34 CDT
(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: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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). -- 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).
 |
 |