convert data in external table
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
default directory my_dir
(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)"
Yes that is correct. Perhaps you could use a view for the same effect.
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?
You can't use SQL expressions the way you can in SQL*Loader.
Access parameters are documented under External Tables, in the Utilities manual.
Click Here to Expand Forum to Full Width