Results 1 to 6 of 6

Thread: external table

  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unhappy Unanswered: external table

    Hi,
    I must import file TXT in one external table oracle.
    A field of the table must be decimal.
    In the import the records come discard all to you that in that field have values with the comma, insert alone values with zero or null ones.
    Can be indicated in the external table the format of the field? If yes in that way? Thanks thousands!

    FILE.txt:
    CAMPO1 CAMPO2
    val1 10,33
    val2 9,25
    val3
    val4 0


    RESULT
    INSERT IN TABLE "PROVA_XT" ONLY 2 RECORDS
    val3 AND val4
    and discard val1 and val2



    SCRIPT:

    CREATE TABLE PROVA_XT
    (CAMPO1 VARCHAR2(20),
    CAMPO2 NUMBER(20,5))

    ORGANIZATION EXTERNAL

    ( TYPE ORACLE_LOADER
    DEFAULT DIRECTORY DIR_FILE
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE
    NOBADFILE
    NOLOGFILE
    SKIP 1
    FIELDS TERMINATED BY ';'
    MISSING FIELD VALUES ARE NULL
    (CAMPO1,
    CAMPO2 ?????)
    LOCATION (DIR_FILE:'PROVA.TXT')
    )
    REJECT LIMIT UNLIMITED

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: external table

    Originally posted by PC_HELP
    SKIP 1
    FIELDS TERMINATED BY ';'
    As you said that firlds terminated by ';' but in your Example of text file no fieldss are terminated by ';'.

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: external table

    Originally posted by PC_HELP
    FILE.txt:
    CAMPO1 CAMPO2
    val1 10,33
    val2 9,25
    val3
    val4 0


    In the CAMP02 field, value of 10,32 is NOT a number as it has a , (coma) charactor in this field. So that is why it is not considering val1 and val2.

    try to clean Your text file and replace , (coma) charactor by . (dot) charactor. Then your above statement will work.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    Dec 2003
    Posts
    4

    external table

    The file arrive with the comma in the field campo2 and I cannot change it.

  5. #5
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Then follow the procedure below:

    (1) Change the datatype of COMPO2 ro VARCHAR2 in your CREATE table statement.

    (2) then USE REPLACE fUNCTION in SELECT statement to see the COMPO2 in decimal form.

    SELECT compo1, REPLACE (compo2, ',', '.') compo2
    FROM prova_xt;

    As the external Tables are READONLY so the above query will help you.

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  6. #6
    Join Date
    Dec 2003
    Posts
    4

    Talking external table

    Thanks thousands!
    Thanks thousands!
    Thanks thousands!

Posting Permissions

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