Re: Field truncation?

"E Storey" <estorey_at_mekainc.com> wrote in message news:<Sie87.838$6A.11506_at_nnrp1.sbc.net>...
> We have a script that runs on a scheduled basis. It creates a pipe
> delimited file that is fed into a web app. Occasionally, the jc_alttitle
> field will truncate. I can check the table via sql*plus and the value is
> "Administrative Assistant II" but the ".out" file contains "Administrative
> Ass" (I wonder why the problem ticket was opened?). I can re-execute the
> script and I get the same truncation in the same place. If I wait 5 or 10
> minutes, I can re-execute the script and it works.
>
> I don't have a clue.
>
> The script looks a little like this. I have removed some of the code and
> replaced it with ... for brevity.
>
> set linesize 32000
> set maxd 60000
> set arraysize 1
> set long 200000
> set trimspool on
> set pagesize 0
> set feedback off
> set verify off
> set termout off
> set btitle off
> set heading off
> set escape off
> set echo off
> set space 0
> spool E:\inetpub\ftproot\job_postings.out
> ...
> select
> mj.jc_idch||'|'||
> mj.jc_dtpost||'|'||
> mj.jc_dten||'|'||
> 'ESSENTIAL FUNCTIONS: '||nvl(substr(epc.desc_1,1,1450),'NA.')||
> 'ESSENTIAL REQUIREMENTS: '||nvl(substr(epc.desc_2,1,980),'NA. ')
> ||'PREFERRED SKILLS: '||nvl(substr(epc.desc_3,1,480),'NA. ')
> ||'SPECIAL CHARACTERISTICS: '||nvl(substr(epc.desc_4,1,480),'NA. ')
> ||'CONTACT: '||nvl(substr(epc.desc_5,1,280),'NA. ')
> ||
> '|'||
> decode
> (mj.jc_post,'INTERNAL/EXTERNAL','BOTH','INTERNAL','INTERNAL','EXTERNAL','EXT
> ERNAL') ||'|'||
> nvl(lj.joty_desc,'NA')||'|'||
> nvl(mj.jc_alttitle,lp.tt_desc)||'|||'||
> ...
> from
> mjobo mj, epc_job_desc_temp epc, ljobtype lj, mcmp mc, lpost lp, mnme
> nm, lloc ll, lflsa fl, lsalgrade sl, ljobcode joc
> where ...
>
>
> This is 8i 8.1.6.3.0 on NT 4.0/SP6

Hi,

try nvl on mj.jc_alttitle to something other than lp.tt_desc, then right behind it dump lp.tt_desc and nvl it to some ovbious string.
Are they different lengths on the db? Just see what's in there.

I had to do 9 reports for the state (ASCII dumps) and I ended up using nested combintations of lpad, rpad, nvl, decode and substr before I could get every field exactly the right length with the right format, etc. The state
format was non-negotiable (probably some COBOL legacy). Source was a data warehouse (Ora 8i) with 3 different sources so we had to take what we had
and make it work.

Just a thought.

Eischeid Received on Sat Jul 28 2001 - 00:21:38 CEST