Talend with SQL function seems not to work

Question

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é

Ibrahim Mezouar · Accepted Answer · 2018-02-12T15:05:48.333

2

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

edited Feb 12 '18 at 15:05

answered Feb 12 '18 at 14:25

Ibrahim Mezouar

3,981
1
18
22

You have a compilation error because you are missing the enclosing quotes around your script : "create or replace function ..etc .. " – Ibrahim Mezouar Feb 12 '18 at 15:06
thank you yes your are right but now there is another error, – Feres.o Feb 12 '18 at 15:14
yes , i select now a connection but , the name of function nownis not regonized on toracle INput – Feres.o Feb 12 '18 at 15:23
the fuction is declared on tOracle Row but it-s not recognized on the oracle input – Feres.o Feb 12 '18 at 15:26
can you please help me , i stuck on it , i done as you miontioned , the declaration of fucntion on toracle Row and the sql statment that use that function on tOracle input, i attached new sceren shoot – Feres.o Feb 12 '18 at 15:41
Could you please check that the function is created by your Talend job ? Delete it from sql developer, run your job, then check again in sql developer if the function exists ? – Ibrahim Mezouar Feb 12 '18 at 15:52

Talend with SQL function seems not to work

1 Answers1