Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    5

    Question Unanswered: Problem with bcp tool

    I use bcp for create flat file and it works good instead of putting data which contain 0 (zero). If there is 0 number in table I don't have 0 in my bcpout file, but .000 :/ How can I change it to put simple 0 in dmp file??

    Best,
    Tytoos

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When producing character output, BCP uses the format file if one is provided and the column data type if no format file is provided.

    Since .000 provides a lot more formatting information than just 0 and the two representations are equivalent, I would prefer the use of .000 myself.

    In order to help you, I'd have to understand more about your problem. Why does it matter how zero values get formatted? What other values might be present in these columns? Is performance important when creating the text file? What tools to you envision using to process the text files you produce?

    While it is certainly possible to do what you want and there are many ways to do it, I see considerable risk and work but don't see much value in doing it because I don't understand why it is necessary for you.

    -PatP

  3. #3
    Join Date
    Nov 2008
    Posts
    5
    OK, so I can explain you.
    This precision is not important for me, because this field have always .000 !!
    But this is important to omit this value, because then I need to import this data to Oracle database and there I have table with table with type without precision!! And I can't alter this table. Please tell want I can do with this problem.

    Best.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Solutions in order of increasing difficulty:

    1) Use sed or gres to post-process your existing text file
    2) Use perl, awk, or php to post-process your existing text file
    3) Create a view that includes a literal 0 in place of the column(s) then BCP that view instead of the table.
    4) Create a BCP format file that includes a literal 0 in place of the column(s), then BCP using that format file.
    5) Create a bulk loader format file to allow Oracle to import the existing text file.
    6) Create a staging table in the Oracle database that can directly bulk load the existing text file, then a PL/SQL script to manage the process.

    Any of these ideas ought to work nicely. Each has strong and weak points, so you'll have to pick the one that works best for your situation. If you decide to post-process your existing text file, be sure to include a leading space character in your pattern... Failure to do so is fun to watch, but not very productive!

    Whatever solution you decide to use, TEST IT FIRST. Don't try these ideas for the first time in production!

    -PatP

Posting Permissions

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