Talend with SQL function seems not to work

I'm using Oracle SQL Developer to test my database request just before I inject this request on my Talend project.

Here is an exampl. I have a request that is working fine on SQL Developer tools but not on my Talend project.

My sql statment has a function declaration and then an select like this:

create or replace function updateDate(p_date varchar2) return date as
  l_date date;
  e_bad_day exception;
  pragma exception_init (e_bad_day, -1847);
begin
  begin
    -- try to convert
    l_date := to_date(p_date,'yyyymmdd');
  exception
    when e_bad_day then
      -- ignore the supplied day value and get last day of month
      l_date := last_day(to_date(substr(p_date, 1, 6), 'yyyymm'));
  end;
  return l_date;
end;
/
Select ASRF_NUMASR NIR,
ASSUR_NOASSURE NOASSURE,
ASRF_CODSEX sexe,
updateDate(ASSUR_DATNAIS) as DATE_REAL
from NORMAL_ASSUR 
UNION
Select ASRF_NUMASR NIR,
ASSUR_NOASSURE NOASSURE,

When i put the same text on my Talend project it's not working. It seems that it only executes my function declaration.

The exception is:

ORA-01003 aucune instruction analysé

Solution

Although I wouldn't recommend using Talend to create/replace the updateDate function at each execution of the job, as it's better to create it beforehand in your sql developer, you can try by separating your sql script using 2 components :

The DDL part of the script goes in a tOracleRow :

"create or replace function updateDate(p_date varchar2) return date as
  l_date date;
  e_bad_day exception;
  pragma exception_init (e_bad_day, -1847);
begin
  begin
    -- try to convert
    l_date := to_date(p_date,'yyyymmdd');
  exception
    when e_bad_day then
      -- ignore the supplied day value and get last day of month
      l_date := last_day(to_date(substr(p_date, 1, 6), 'yyyymm'));
  end;
  return l_date;
end;"

The DML part goes in a tOracleInput (and setting the corresponding schema on the component)

"Select ASRF_NUMASR NIR,
ASSUR_NOASSURE NOASSURE,
ASRF_CODSEX sexe,
updateDate(ASSUR_DATNAIS) as DATE_REAL
from NORMAL_ASSUR 
UNION
Select ASRF_NUMASR NIR,
ASSUR_NOASSURE NOASSURE,
..."

And call them like this :

tOracleRow
   |
OnSubjobOk
   |
tOracleInput -- Main -- target