| 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 - Thanks to you and everyone else for the great suggestions. He and I
are sitting down tomorrow to straighten this out. I was concerned that there
might be some PL/SQL oddity that I wasn't aware of (he is a pretty good
PL/SQL programmer). I appreciate your ruling that out.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Thursday, August 22, 2002 7:53 AM
To: Multiple recipients of list ORACLE-L
Dennis,
I'd guess that the developer did not try it correctly. Ask to see the code.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, August 21, 2002 5:29 PM
To: Multiple recipients of list ORACLE-L
Tom - The developer reports that he tried this but it didn't work. The third
position is still a space value. Thanks to everyone for the good replies.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Wednesday, August 21, 2002 3:13 PM
To: 'ORACLE-L_at_fatcity.com'
Cc: DENNIS WILLIAMS
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 Thu Aug 22 2002 - 09:38:31 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: 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). -- 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).
 |
 |