Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Posts
    21

    Unanswered: Spool Pipe Delimiter Does Not Work Sometimes

    Hi
    I'm spooling a straight select from a table to a pipe delimited text file. However, sometimes instead of using the pipe as the delimiter, it uses small 'L'. Any suggestions as to what the problem could be ? If I rerun it, it usually doesn't have this problem.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Maybe the alignment of the moon with Venus and Mars is not propitious to the generation of pipe delimited files.

    It's hard to tell, your black karma is preventing my crystal ball from looking into your code.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What he is hinting at (LOL) is that you need to parse the select into this forum so that we can check it out. We can't troubleshoot a query that we never see.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    After all, l looks like | (first one is small L, another is a pipe), so - maybe the author pressed a wrong key?

  5. #5
    Join Date
    Feb 2005
    Posts
    21

    Smile

    No I didn't press the wrong key and I hope this clears things up . Out of probably 10,000 records, only one would show this problem. The sql is run from a file from the command line. Usually if I re-extract the problem is gone. The problem however, is that this is run through a script for many tables, so re-extracting causes loss of time.




    set linesize 3000
    set head off
    set newpage 0
    set pagesize 0
    set space 0
    SET FEEDBACK OFF
    SET VERIFY OFF
    SET ECHO OFF
    SET termout OFF
    set colsep '|'
    spool C:/btldw/extract/reference/tamapnames1101070946.ul
    .
    select ID,APINSTANCE_ID,ACCESSCITEM_ID,APTYPE_ID,APTYPECO NFIGITEM_ID,APTYPENAME_ID,ADDRESSCLASS_ID,ADDRESS, ADDRESSTYPE_ID,ENCODEDADDRESS,FULLADDRESS,DIRECTIO N,SECRETLEVEL,ISACTIVE,to_char(ACTIVEFROM,'dd/mm/yyyy'),to_char(ACTIVETO,'dd/mm/yyyy'),to_char(SPACTIVEFROM,'dd/mm/yyyy'),to_char(SPACTIVETO,'dd/mm/yyyy'),RESITEM_ID,RESITEMIDENT_ID,ISDIRECTORYNAME, ISBILLINGNUMBER,CPARTY_ID,CPARTYACCOUNT_ID,LOCATIO NADDRESS_ID,SUPPLYCITEM_ID,TRUNK_ID,CTX_ID,MTX_ID, APNAME_ID,APNAMEDAPNAME_ID,to_char(APNAMEACTIVEFRO M,'dd/mm/yyyy'),to_char(APNAMEACTIVETO,'dd/mm/yyyy') from tytan.tamapnames where addresstype_id in (1305294,511191);
    spool off
    exit 2
    .

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    From the look of it, it appears someone close to you practices voodoo and has cast a spell on your data. You need to counter this by aquiring a lucky charm.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    please post example of problem record with some non-problem ones around it.

    have you tried hard-coding the delimeters to test if this works better?
    ie:
    PHP Code:
    select ID||'|'||APINSTANCE_ID||'|'||ACCESSCITEM_ID||'|'||
    APTYPE_ID||'|'||APTYPECO 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Feb 2005
    Posts
    21
    Yes I did try concat and still produced the error. I deleted the error file so I don't have any samples.

    I noticed that this problem started to occur after upgrading Oracle 9i to 10g. Before this I had no problems.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •