Oracle-L: FW: PL/Sql question

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Wed, 21 Aug 2002 10:03:43 -0800
Message-ID: <F001.004BB6B7.20020821100343@fatcity.com>

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).

Received on Wed Aug 21 2002 - 13:03:43 CDT