Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/Sql question

RE: PL/Sql question

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Thu, 22 Aug 2002 06:38:31 -0800
Message-ID: <F001.004BC8E6.20020822063831@fatcity.com>


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,
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). -- 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).
Received on Thu Aug 22 2002 - 09:38:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US