One Star

How to get the values dynamically in tOracleInput query

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
37 REPLIES
Fifteen Stars

Re: How to get the values dynamically in tOracleInput query

SQL queries in all of the Talend database components are simply Java Strings. As such you can build them dynamically. However you must make sure that the built String represents legitimate SQL.
Looking at your example.....
"Select * from table1" +context.filename

....I can immediately seem a possible error in that you have not left a space after "table1". When you concatenate that String with ...
.... you will get....
"Select * from table1where col3 in (val1,val2, val3)"

I also notice that the "vals" are not formatted. For numbers that is OK, but if they are representing Strings (for example), you will need to add quotes, etc. 
The best way to approach this is to build your SQL query, output it to the System.out and then test the String you see in a query analyser. If it works there, it will work in your DB component.
Rilhia Solutions
One Star

Re: How to get the values dynamically in tOracleInput query

Hi Richard,
Thank you for fast response
I have written the query the way you told, but did not mention properly Smiley Sad
It is throwing error 
Could you please suggest and provide a sample method (best) for dynamic query where i have to pass 20000 records dynamically at IN clause
I used the method suggested by you in the below post
But, this is row wise processing(it is consuming more time when I have filter in more 2 or more columns, Suppose if I have 4 filter conditions in Column A and 4 filter conditions in Column B, then I have to give 16 combinations of input)
I need some method where the query is build using the input from file
Hope you get my requirement!!
Fifteen Stars

Re: How to get the values dynamically in tOracleInput query

I'm afraid you will need to give examples of the sort of queries you will need. There is not enough info here. However, the example I gave should be enough to be extrapolated from. 
Just think about building a String in Java.

String select = "Select column1, column2, column3 From table1 ";
String where = "";
if(row1.value.compareToIgnoreCase("test")==0){
where = "Where column1 = '"+row1.data+"'"
}
String query = select+where;
System.out.println(query);

The above will produce a query with a where clause of "column1 = " whatever is held by the data column IF the value column holds "test". Otherwise it will produce a query without a Where clause.
The "System.out.println" code allows you to output the query to the output window. You can use that to see it as a String, copy and paste it into a query analyser and test it on your db.
Rilhia Solutions
One Star

Re: How to get the values dynamically in tOracleInput query

so,Let me take an scenario,
Table - Employee

I need to put the query in tOracleInput as 
Select * from Employee where DESIGNATION in ('Developer', 'Analyst') and SALARY in (3000)
But, here I need to give the where clause dynamically(through a text file)
i.e., Select * from Employee +context.file
so, I can change the filters whenever I need without changing the job
I need to know how to append to query through context(in file)
Hope it helps!!
One Star

Re: How to get the values dynamically in tOracleInput query

Hi,Can anyone help with writing dynamic query with above scenario
Fifteen Stars

Re: How to get the values dynamically in tOracleInput query

Just use your context variable in the SQL query.....
"SELECT 
COLUMN1,
COLUMN2,
COLUMN3
FROM TABLE
WHERE COLUMN1 = " +context.YourContext
Rilhia Solutions
Five Stars

Re: How to get the values dynamically in tOracleInput query

Hi Guys ,
I too have problem while using tOracleInput.
I am taking data from excel into context variable output_Tns (tried all data type available).
My talend job looks like
(tFileInputExcel -- tJavaRow (context.output_Tns = context.output_Tns  + "," + input_row.Unmatched_Tns) -- tOracleInput --tJavaRow  -- tSendMail )
then I am pass the variable data into select statement inside tOracleInput
"select Col1 ,
          col2 ,
          col3 ,
          col4
   from my_table
 where col1 in (select replace(trim(',' from dbms_lob.substr(' "+context.output_Tns +" ', 4000, 1 ) ),' ','')
                       from dual)"
----------------------------------------------------------
While running the job I am getting below error :
                                              Exception in component tOracleInput_2
                                              java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long
I know it is because variable length is more then 4000 , but do we have any work around to run this query . Later I am catching these columns into other variables too.
Please help , I am new in talend and don't know how to continue further. Smiley Sad
Ten Stars

Re: How to get the values dynamically in tOracleInput query

Instead of building a "WHERE col1 IN" clause with your list of unmatched values, why not insert them into a table and join it to your my_table?
One Star

Re: How to get the values dynamically in tOracleInput query

Instead of building a "WHERE col1 IN" clause with your list of unmatched values, why not insert them into a table and join it to your my_table?

Thanks for this work around , but why do  I need to put data physically in any DB.What if we are running same job in parallel with different excel files ? It should be independent of any DB. Pentaho has less palettes and it provides option pass variables directly to the query from any input files. If such option is present in Pentaho , it should be present in Talend too.
Anyways thanks for help Smiley Happy
One Star

Re: How to get the values dynamically in tOracleInput query

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....
Ten Stars

Re: How to get the values dynamically in tOracleInput query

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.
Five Stars

Re: How to get the values dynamically in tOracleInput query

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 ...
Twelve Stars

Re: How to get the values dynamically in tOracleInput query

bulk SELECT? What are You mean?
excellent tool from Pentaho ... if it so good, why You are there? Smiley Happy  
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
-----------
Five Stars

Re: How to get the values dynamically in tOracleInput query

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? Smiley Happy   -- 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 Smiley HappySmiley Happy
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 .
Fifteen Stars

Re: How to get the values dynamically in tOracleInput query

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).
Rilhia Solutions
Five Stars

Re: How to get the values dynamically in tOracleInput query

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 Smiley Happy
Fifteen Stars

Re: How to get the values dynamically in tOracleInput query

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)
Rilhia Solutions
Five Stars

Re: How to get the values dynamically in tOracleInput query

I'm not sure why you are carrying out string manipulation in SQL, but whatever floats your boat.
Because I am a sql developer Smiley Happy Smiley Happy
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. Smiley Happy
Thanks for the help , I will continue on this tomorrow .
Ten Stars

Re: How to get the values dynamically in tOracleInput query

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.
Five Stars

Re: How to get the values dynamically in tOracleInput query

Dear Cterenzi,
Thanks for constantly helping me.
It seems that problem is from my side. I couldn't explain my issue properly.
In this case,we are taking one excel sheet which is having list of TNs. In the list we have around 2000 Tns in average . ( each Telephone Numbers will have 10 letters)
everyday we will get these excels. we are taking all Tns and running them inside a query one by one and get the value based on different condition . this process is very time consuming.
So I first , consolidated all queries in one and get the output in five different columns. once we have all the data , we are sending result of different columns in different mails to different people.
Now as we have more then 2000 Tns in the list , whatever approach I take , I will get a big string.
firstly I tried to distribute the tns in different parts but don't know how to do it( tried Java, Java script inside talend). Also it will take 15-20 variables to store all tns.
second I tried to use clob data but failed again.
then I tried to put it in anonymous block with no success. Then we tried almost all palletes available , again failed.and so on.....
So finally there is only one problem, size of variable is not going to be reduced, then how to use it within the query.
If we manage to do so, it will make our life easy.
Ten Stars

Re: How to get the values dynamically in tOracleInput query

I didn't know the scope of your case.  Even if you construct the query the way I described, it sounds like you'll run into a limitation on the number of items in your IN clause. (The last time I used Oracle, this was 1000 values.)
If there's a way to partition the list of telephone numbers to create smaller queries, using an IN clause might work.  Otherwise, you'll either need to insert the numbers to a table and do a join or pull down the entire table you want to query and do a lookup within Talend.  If your table is very large, pulling it into Talend may not be feasible.
Five Stars

Re: How to get the values dynamically in tOracleInput query

Hi Cterenzi,
You are right we can not process more then 1000 inputs , but if we are getting this data from other table we can .
Here if it is sending such error , we can restrict user from using more then 1000 tns. We can tell them to split file into two or three parts.
We can not tell them to use only 300 Tns at one time, This will rise questions on this job.

I am trying this option, ( this is just POC that's why I introduced tJavaRow in middle).
using only 401 Tns in excel file.
In TFlowToIterate,
I have assign :
Key "Tns"
value "Tns"
In tOracleInput , my query is
"select  ' " + globalMap.get("TNs1")) + " ' as outs from dual" ( Also tried different ways to get some output).
in case if i am trying in wrong direction , please alert me . else guide me how to use it ....
Five Stars

Re: How to get the values dynamically in tOracleInput query

second screen shot for your refrence
Five Stars

Re: How to get the values dynamically in tOracleInput query

Hi Friends,
After checking so many things , I can say that I learn a lot in Talend.
Now my Job looks something like that.

However I am getting null value for the same query.
So still more effort needed to fix this issue.
It seems Either I will be expert in Talend before finishing this job or won't touch it again in y life Smiley Tongue
----------------------------------------------------------------------------------------------------------------
Anyways thanks guys for helping me specially Cterenzi.
Its 3 AM here , so going to sleep . I will try to fix this tomorrow.
If I find any solution. I will post it here so others can get some idea. Smiley Happy
Twelve Stars

Re: How to get the values dynamically in tOracleInput query

Hi Cterenzi,
You are right we can not process more then 1000 inputs , but if we are getting this data from other table we can .
Here if it is sending such error , we can restrict user from using more then 1000 tns. We can tell them to split file into two or three parts.
We can not tell them to use only 300 Tns at one time, This will rise questions on this job.

I am trying this option, ( this is just POC that's why I introduced tJavaRow in middle).
using only 401 Tns in excel file.
In TFlowToIterate,
I have assign :
Key "Tns"
value "Tns"
In tOracleInput , my query is
"select  ' " + globalMap.get("TNs1")) + " ' as outs from dual" ( Also tried different ways to get some output).
in case if i am trying in wrong direction , please alert me . else guide me how to use it ....

not sure, what are You try to do, but You do something wrong 
tOracleInput - MUST have output connection, it MUST send data somewhere (forward them), so - both of Yours screenshots - not finished. Until it not finish - it always will be error
-----------
Fifteen Stars

Re: How to get the values dynamically in tOracleInput query

Hi Cterenzi,
You are right we can not process more then 1000 inputs , but if we are getting this data from other table we can .
Here if it is sending such error , we can restrict user from using more then 1000 tns. We can tell them to split file into two or three parts.
We can not tell them to use only 300 Tns at one time, This will rise questions on this job.

I am trying this option, ( this is just POC that's why I introduced tJavaRow in middle).
using only 401 Tns in excel file.
In TFlowToIterate,
I have assign :
Key "Tns"
value "Tns"
In tOracleInput , my query is
"select  ' " + globalMap.get("TNs1")) + " ' as outs from dual" ( Also tried different ways to get some output).
in case if i am trying in wrong direction , please alert me . else guide me how to use it ....

You may have an issue with your usage of your globalMap variables. When you assign a globalMap variable using the tFlowToIterate, the key is a String (which you have as "Tns") and the value can be hard coded (which is what you appear to have done here with "Tns"), but I suspect you wanted to set it to be a row value. To do this you need to refer to the row and columns without quotes like this....
row1.Tns
....for a row (link between components) called row1 and a column called Tns.
GlobalMap variables are also case sensitive when you use them and MUST be called by the exact key they are set with. In your example this is not the case. In addition to this, you need to cast the type since globalMap variables are stored as Objects. So, assuming your globalMap has a key of "Tns" and you correct it to be set to the correct value and not a hard coded value, your SQL query should be something like below....
"select  ' " + ((String)globalMap.get("Tns")) + " ' as outs from dual"
Try adding a tLogRow to your DB components as well. Otherwise you won't see any output.
Hope this helps
Rilhia Solutions
Five Stars

Re: How to get the values dynamically in tOracleInput query

Hi Vopukov,
Thanks for your input.
as you said " tOracleInput - MUST have output connection" I have added tLogrow for that.
( I guess I can't sleep until This issue comes to any conclusion Smiley Sad )
Ten Stars

Re: How to get the values dynamically in tOracleInput query

In TFlowToIterate,
I have assign :
Key "Tns"
value "Tns"
In tOracleInput , my query is
"select  ' " + globalMap.get("TNs1")) + " ' as outs from dual" ( Also tried different ways to get some output).

I'm not sure about the global variable you listed.  If you look in the Outline section (bottom left corner) and expand tFlowToIterate, it will list the variables you can use.  You can drag the Tns variable into your query and Talend will insert the proper code for you.
Five Stars

Re: How to get the values dynamically in tOracleInput query

Hi Cterenzi,
You are right we can not process more then 1000 inputs , but if we are getting this data from other table we can .
Here if it is sending such error , we can restrict user from using more then 1000 tns. We can tell them to split file into two or three parts.

row1.Tns
....for a row (link between components) called row1 and a column called Tns.
-----------------------------------------------------------------------------------------
GlobalMap variables are also case sensitive when you use them and MUST be called by the exact key they are set with. In your example this is not the case. In addition to this, you need to cast the type since globalMap variables are stored as Objects. So, assuming your globalMap has a key of "Tns" and you correct it to be set to the correct value and not a hard coded value, your SQL query should be something like below....
"select  ' " + ((String)globalMap.get("Tns")) + " ' as outs from dual"
Try adding a tLogRow to your DB components as well. Otherwise you won't see any output.
Hope this helps
Hi Rhall/Friends,
I guess you are right.
First of all I haven't created any global variable named Tns. I just pressed ctrl+space and pick it from the list.
Second I can see that it is passing 407 Tns to tOracleInput but output is null. ( This screen shot is a part of real Job which I am working right now ). I tried row.Tns/Row1.Tns etc but in that case it is giving some compilation errors at tOracleInput.
Is there any tutorial available online which can help me to get this done right.
Rhall -  Could you please to share some screen shots or steps to do this.
I am constantly working on this for last 40 hours without sleep and completely exhausted. It would be better if Talend could share more details in palletes description so we newbie could understand it too.