topic Re: ORA-00933 in tDBRow when using context variable in Talend Studio

ORA-00933 in tDBRow when using context variable

Anonymous — Thu, 18 Oct 2018 08:52:46 GMT

I've read some questions about this problem but I can't get it right...

I'm using a tDBRow component to execute this query:

" 
insert into test.wk_sf_l_srv_cshistory_to_load select
ID, 
CREATEDDATE
from test.CASE_HISTORY_FULL
where createddate >=  " + context.builtIn_lastRunDate

but everytime it gives me error ORA-00933.

I've tried different combinations, adding +"" at the end, or + ";", or working with date format, but that is not the problem.
What am I missing?

Re: ORA-00933 in tDBRow when using context variable

Anonymous — Thu, 18 Oct 2018 11:48:39 GMT

Hi,

Can you try in the below format?

" 
insert into test.wk_sf_l_srv_cshistory_to_load select
ID, 
CREATEDDATE
from test.CASE_HISTORY_FULL
where createddate >=  TO_DATE('"+context.builtIn_lastRunDate+"', 'YYYY/MM/DD')"

where context.builtIn_lastRunDate is string data type and it is having the input date in YYYY/MM/DD?

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

Re: ORA-00933 in tDBRow when using context variable

Anonymous — Thu, 18 Oct 2018 11:53:54 GMT

Hi,
lastRunDate is a date data type and I can't change it, since it will have to come from an external system.
What I've also tried is:

" 
insert into test.wk_sf_l_srv_cshistory_to_load select
a.ID, 
a.CREATEDDATE
from test.CASE_HISTORY_FULL a
where a.CREATEDDATE >=  to_date(to_char("+context.builtIn_lastRunDate+", 'yyyy-MM-dd'),'yyyy-MM-dd')
"

Re: ORA-00933 in tDBRow when using context variable

Anonymous — Thu, 18 Oct 2018 12:13:12 GMT

Hi,

Even though the context variable is a date field, when it gets converted to Oracle insert statement, it will become a string. You can print the entire output (specified in double quotes) using a tjava and try to see the results.

So as a first step, try to pass a string as parameter and see whether insert statement is working in that case. If the result is positive, you can do a String conversion of context variable in date format before passing to your insert statement.

Warm Regards,

Nikhil Thampi

Re: ORA-00933 in tDBRow when using context variable

Anonymous — Thu, 18 Oct 2018 13:29:01 GMT

Hi, I've tried your method but it gives error "Invalid character constant"

Re: ORA-00933 in tDBRow when using context variable

Anonymous — Thu, 18 Oct 2018 13:47:32 GMT

Hi,

Did you convert the data in context variable to string before submitting to insert statement? Could you please print the details of both context variable and the full insert statement in a tjava and share the screen shots of the components? Ideally the output should be the exact insert statement as the output.

Warm Regards,

Nikhil Thampi

Re: ORA-00933 in tDBRow when using context variable

Anonymous — Fri, 19 Oct 2018 10:20:27 GMT

Hi, I'm sorry but I didn't understand.

You said that even if it's a date, it is converted to a String in the Oracle statement, so you suggested me this format:

TO_DATE('"+context.builtIn_lastRunDate+"', 'YYYY/MM/DD')"

This for me didn't work.
If I use this method with a String it works, but not with my date.

Then you said I had to convert it to a String before submitting it to the insert statement, but wasn't it converted automatically to String in Oracle statement, as you said earlier? Or did I get it wrong?

Also, if I try to print my insert statement in a tJava, it gives me errors of quotes inside System.out, so I'm not able to print.

I only have one component by the way: tDbRow, where I wrote my query

Re: ORA-00933 in tDBRow when using context variable

Anonymous — Fri, 19 Oct 2018 13:04:05 GMT

Hi,

Please refer the below picture for the components you need to use to achieve the resolution.

You will be able to execute the INSERT statement if the input data is coming as String. So we need to convert the input data from date to String format. You need to add the below code snippet to tjava component.

/* context.val is the value in date format
context.date_to_string converts the data to String in dd/MM/yyyy format */

SimpleDateFormat formatDateJava = new SimpleDateFormat("dd/MM/yyyy");
context.date_to_string = formatDateJava.format(context.val);

In tOracleRow, you can use the below format.

" 
insert into test.wk_sf_l_srv_cshistory_to_load select
a.ID, 
a.CREATEDDATE
from test.CASE_HISTORY_FULL a
where a.CREATEDDATE >=  to_date('"+context.date_to_string+ "','DD/MM/YYYY')
"

Could you please try it and let me know the results.

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi