Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Calling functions in External tables

vpolasaMay 18 2011 — edited May 18 2011
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 15 2011
Added on May 18 2011
3 comments
522 views