I'm trying to create an External table and assigning a function return value to a column. But I'm getting error when trying to retrieve data from the table. Below are the scripts and error message. Can someone point me the correction.
create or replace
function v_sum ( v_num1 number, v_num2 number) return number as
v_res number;
begin
v_res := v_num1 + v_num2;
return v_res;
end;
create table tab_xyz
(
col1 varchar2(10),
col2 varchar2(10),
col3 number
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER DEFAULT DIRECTORY "MF_DIR" ACCESS PARAMETERS (
records delimited BY newline
load when (col1 != blanks)
badfile mf_dir:'xyz.csv.bad'
logfile mf_dir:'xzy.csv.log'
discardfile mf_dir:'xyz.csv.dsc'
fields terminated BY ',' optionally enclosed BY '"' MISSING FIELD VALUES ARE NULL
(col1,
col2,
col3 v_sum(3,4))
)
LOCATION ('xyz.csv' ))
REJECT LIMIT 100;
/
Error starting at line 4 in command:
select * from tab_xyz
Error report:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01008: the bad identifier was: v_sum
KUP-01007: at line 9 column 11
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
Thanks