-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|