DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: nulls!!

  1. #1
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250

    Cool

    Here is a very peculiar problem I faced while loading a pipe delimited text file.

    I received a file delimited by pipe to load from a mainframe system. First the file has spaces instead of nulls for dates, so it was not accepted, the mainframe person told me that it is not possible for him to generate nulls but he will generate a low hexadecimal value which should be treated as nulls. The file looked perfectly ok to me the data for that field looked :
    ||
    which meant the same -nulls.
    But sql loader wouldn't accept it, it would give the same error. I had to substitute all the || with the same || manually only then it was accepted.
    Now I don't want to do this everytime I receive the file and also it is going to be loaded automatically by shell scripts, is there any way to get over this??

    Thanks for any input on this!!
    KN

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    that must be because of this 'low hex value' ...
    if you can manage to have this file under unix, use vi, do [esc]:se list
    that will show you all character codes, so you'll see if there is an invisible character between your 2 pipes, which I think is the case

    but why does this person try to 'generate nulls' ??? null just means that the value is not filled, so if you have 2 pipes : ||, without anything between them, then it means that this value is null ...

  3. #3
    Join Date
    Jul 2000
    Posts
    521
    Let the main frame guy send you spaces.
    Handle those spaces in you sqlloader control file with if condition.

    This may not be a good idea if the volumes are heavy and regular. because doing this will not allow you to use direct load.
    svk

  4. #4
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250

    Talking

    CAN YOU PLEASE GIVE ME AN EXAMPLE OF SQLLOADER CONTROL FILE WITH IF CONDITION AS I AM IN A HURRY AND CANNOT FIND ANYTHING IN DOCUMENTATION.

    THANX GUYS!!
    KN

  5. #5
    Join Date
    Jul 2000
    Posts
    521
    A sample control file from ORACLE :

    --
    -- $Header: ulcase6.ctl 09-mar-99.22:22:13 mjaeger Exp $ ulcase6.ctl
    --
    -- Copyright (c) Oracle Corporation 1991, 1997, 1999. All Rights Reserved.
    --
    -- NAME
    -- ulcase6.ctl -
    -- DESCRIPTION
    --
    -- RETURNS
    --
    -- NOTES
    --
    -- MODIFIED (MM/DD/YY)
    -- mjaeger 03/08/99 - bug 808870: OCCS: convert tabs, no long lines
    -- msakayed 09/02/97 - remove nullif on empno
    -- jhealy 01/27/95 - merge changes from branch 1.2.720.1
    -- ksudarsh 11/06/92 - infile is ulcase6
    -- cheigham 08/28/91 - Creation
    --

    load data
    infile 'ulcase6.dat'
    replace
    into table emp
    sorted indexes (empix)
    (empno position(1:4),
    ename position(6:15),
    job position(17:25),
    mgr position(27:30) nullif mgr=blanks,
    sal position(32:39) nullif sal=blanks,
    comm position(41:48) nullif comm=blanks,
    deptno position(50:51) nullif empno=blanks
    )
    svk

Posting Permissions

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


Click Here to Expand Forum to Full Width