How to get the values dynamically in tOracleInput query

[Anonymous]

I wanted to apply filter to source query with different filter condition at every run
so, I need to take the values in where condition using a file( through context variable)
Example:
I have written the below in
toracleInput
"Select * from table1" +context.filename
I have given the filter in the file
"where col3 in (val1,val2, val3)"
It is not taking the filter condition from file
If i try one other ways it is throwing error.
Please let me know the appropriate way to write dynamic queries in talend

[_AnonymousUser]

Hi Team,
Is there any option to get the desire output without creating a table or file and then joining using tMap etc ...
So far all I can see that there is so much buzzz on internet about Talend but it is not even as half good as Pentaho , Pentaho has very less components but it does its job( almost all stuff we can do on Talend) very easy and fast and efficiently , while in Talend we have more then two hundred Palettes but to perform each action we have to use many of them ...
I am sorry if I said something wrong or hurt any Talend lover but after few months of study in Talend and still getting such SILLY issues , I am really pissed off ....
If any option is available for this please let me know....

[cterenzi]

Instead of asking the database to manipulate your string, do that work in a Talend component and construct the IN clause for the database.   The tAggregateRow component can turn many rows of strings into a comma-separated list for you.   Barring that, your tJavaFlex probably gets you very close to where you want to be.  A second tJavaFlex or a tMap to take off the extra comma may be all you need to eliminate the SELECT 'long literal' FROM DUAL from your query.

[Anonymous]

Hi Cterenzi/others,
Is there any option for bulk select ?
I already lost my hope , so don't want to waste more on same concept unless until someone tells me its possible to pass variable in such scenario ...

[vapukov]

bulk SELECT? What are You mean?
excellent tool from Pentaho ... if it so good, why You are there?   
each tools have it own drawbacks (and Pentaho very far from be named Best of The Best)
If You do not want save data to DB - save it to buffer (memory) and then use as input flow on feature steps

[Anonymous]

bulk SELECT? What are You mean?
-- I mean do we have any option to send in parameter as bulk/clob data to avoid such issues?
excellent tool from Pentaho ... if it so good, why You are there?    -- What are you , 7!!!!
each tools have it own drawbacks (and Pentaho very far from be named Best of The Best)
-- Its not drawback , it is elementary for any ETL to send send data from source to target and based on inputs source will perform some other operation.
-- In other words you are accepting , it can't do this
If You do not want save data to DB - save it to buffer (memory) and then use as input flow on feature steps
-- Now that is waht I call solution.
-- I will try that .
-- You should shut me down by giving some solutions like this.
-----------------------------------------------
I will search and try to input data to buffer and to run this in select statement .
I will let you know the output .

[Anonymous]

Amit, your problems come from not understanding the tool I'm afraid. I'm sure if I were to switch to Pentaho and expect it to work like Talend or Informatica, I would be cross because it doesn't work as I expect it to. 
It should also be pointed out that " java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long" is an issue with your SQL. You would get the same in Pentaho. Since the data is not already in your database, you cannot inject into your SQL query unless you break it down (the query) or load the data to the db. This is NOT a Talend problem, it is a problem with how you are trying to solve this.
To get round this you can do your filtering in Talend, but as an experienced DI developer I am sure you know that would not be efficient. Your database is designed to do this work. So if you want an efficient solution (in ANY DI tool) you will load the filter data into your database and get the database to earn its place in your data center and do the filtering there. If there is a reason why you cannot do that (and I understand there may well be) then you will have to take a performance hit and filter in Talend, in memory. This is pretty easy to be honest. Take a look at the tHash components ( https://help.talend.com/search/all?query=tHashInput&content-lang=en).

[Anonymous]

Hi cterenzi/Rhall,

Thanks for your help.
Now I am trying to do some work around. ( Already tried that before , but couldn't succeed )
What if I run this query into tOracleRow ?
-------------------------------------------------------------------------------------------------------------------
( please ignore errors if there is any)
-------------------------------------------------------------------------------------------------------------------
declare
var_input clob := trim(',' from regexp_replace(   ' " + context.output_unmatched  + " ' ,']',null)) ;
var1 varchar2(4000);
vqr2 varchar2(4000);
var3 varchar2(4000);
var4 varchar2(4000);
vqr5 varchar2(4000);
var6 varchar2(4000);
begin
for i in (select tns from (
select null as tns from dual union all
(select regexp_substr(var_input,'+', 1, level) from dual
connect by regexp_substr(var_input, '+', 1, level) is not null)
) where tns is not null)
select col1 ,col2 ,col3
into var1,vqr2,var3
from my_table
where col1 in (i.tns);
var4 := var4 ||','|| var1;
var5 := var5 ||','|| var2;
var6 := var6 ||','|| var3;
end loop;
end;
-------------------------------------------------------------------------------------------------------------------
Is there any way , I can catch the values of var4 and var5 and var6 into context variables???
can we use output and send this to next step.
!!!!!!!!!!!
I am testing the possibilities for this. Hope this thing works

[Anonymous]

I'm not sure why you are carrying out string manipulation in SQL, but whatever floats your boat. Why not wrap this into a stored proc and call that ( https://help.talend.com/search/all?query=Calling+a+stored+procedure+or+function&content-lang=en)

[Anonymous]

I'm not sure why you are carrying out string manipulation in SQL, but whatever floats your boat.
Because I am a sql developer
Why not wrap this into a stored proc and call that ()

I think I am your FAN now.
I will try this. I was hoping to not put anything in DB.But whatever works right...
I have requested a DB link so I can change this to SP.
Thanks for the help , I will continue on this tomorrow .

[cterenzi]

I'm still convinced that your problem is being caused by passing a very long string literal instead of constructing a well-formatted list for your IN clause.  The only reason you're passing a string seems to be that you need to remove an extra comma from your list.  This is not difficult to solve in Talend.  Once you have your list of values nicely formatted as:
val1, val2, val3, val4...
...in a context or global variable, you can drop it right into a SQL statement:
"select Col1,  col2,  col3,  col4 
 from my_table 
 where col1 in ("+context.output_Tns +"); "

If your input data is typed as String, passing it through a tAggregateRow component using the List operator can do this for you.  Otherwise, once you've finished reading in all your data and building your variable, a triggered tJava component can clean up the string before triggering your tOracleInput.