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

Thread: convert data in external table

  1. #1
    Join Date
    Dec 2005
    Posts
    6

    Post convert data in external table

    hi,
    is it possible to use the DECODE function (or any other user function) in EXTERNAL TABLE ACCESS PARAMETER syntax?
    it is in SQL LOADER isn't it?

    in the example below i want to put a default date in case the birthdate is null, and put the city in the address field by using a function i wrote.
    ther's a problem with the "double-quoted-string" (KUP-01005)
    what is wrong in the syntax? and how can i do it otherwise?

    create table ext_tab
    (ID number(10),
    NAME varchar2(60),
    BIRTHDATE date,
    ADDRESS varchar2(100))
    organization external
    (type oracle_loader
    default directory my_dir
    access parameters
    (fields terminated by ','
    missing field values are null
    (id position(1:9) integer external(10),
    name position(10:59) char nullif name=BLANK,
    birthdate position(60:69) date "DD/MM/YYYY" "DECODE(:birthdate,'00/00/0000', '01/01/1900',:birthdate)",
    address position(70:169) char nullif address=BLANK "get_city(:address)"
    )
    )
    location ('source.scv'')
    )

    thanks,
    ganit.

  2. #2
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Yes that is correct. Perhaps you could use a view for the same effect.

  3. #3
    Join Date
    Dec 2005
    Posts
    6
    but what about the errors i get ("double-quoted-string" (KUP-01005))
    what is the right syntax? where can i find syntax definition for using functions in the ACCESS PARAMETERS part?

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    You can't use SQL expressions the way you can in SQL*Loader.

    Access parameters are documented under External Tables, in the Utilities manual.

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