topic Re: get the value that is just inserted by tOracleRow in Talend Studio

get the value that is just inserted by tOracleRow

_AnonymousUser — Tue, 15 Apr 2014 21:32:22 GMT

I used tOracleRow to insert one row and tried to get the value of that row I just inserted. But it seems I'm not able to do it.
tOracleRow - row - tJavaRow - row - tLogRow
in tOracleRow I used insert statement as insert into test (ID, NAME) values (seq.nextval,'TOM')
Then I'm trying to get the value I just inserted and used output_row.ID = input_row.ID; in tJavaRow. But it gave the error message like "cannot be solved."
So I cannot use tOracleRow like this? Is there anyway that I can get the value I just inserted? I need to get the value and pass it to a subjob.
Thanks!

Re: get the value that is just inserted by tOracleRow

Anonymous — Wed, 16 Apr 2014 05:47:47 GMT

Hi,
Is "Insert" operation working for you?
Usually, tXXXRow returns a record set, and you are required to use a tParseRecordSet component after tXXXRow to parse the record sets.
Please have a look at TalendHelpCenter:tParseRecordSet and related scenario.
Best regards
Sabrina

Re: get the value that is just inserted by tOracleRow

Anonymous — Wed, 16 Apr 2014 05:49:17 GMT

Hi iask,
Use tFixedFlow --> tOracleOut-->tLogrow
- Define metadata Id and Name in tFixedFlow
- Use value seq.nextval for Id and "TOM" for Name in tFixedFlow
- Sync metadata with toracleout - set the table name and default action
You will have output displayed on console using tlogrow
Thanks
Vaibhav

Re: get the value that is just inserted by tOracleRow

_AnonymousUser — Wed, 16 Apr 2014 16:51:02 GMT

Thank you for the help. I will have a try on both way.

Re: get the value that is just inserted by tOracleRow

willm1 — Wed, 16 Apr 2014 18:34:02 GMT

Or if you want to stick to your insert statement...
tOracleRow (execute Insert Statement) --> OnSubjobOK --> tOracleInput (select field from table) ---> tLogRow

Re: get the value that is just inserted by tOracleRow

Anonymous — Thu, 17 Apr 2014 08:48:45 GMT

Hi iask,
Is there any update for your issue? Feel free post your issue on forum.
Best regards
Sabrina

Re: get the value that is just inserted by tOracleRow

_AnonymousUser — Sun, 12 Mar 2017 10:23:42 GMT

Hi Team,
I have similar issue, I searched everywhere but don't know how to get the output into context variable using tOracleRow.
( Have done this with tOracleinput many times (tOracleinput -- tLogRow -- ... --) )
I have created a pl sql block in which I get some data .
"declare
var_main clob:= trim(',' from dbms_lob.substr(regexp_replace(   ' " + context.output_unmatched + " ' ,']',null)));
var_main_1 clob;
var_1 varchar2(400);
a number:= 11*30; -- length of tn
b number := 1 ; -- pointer
DIRECTORY_NUMBER varchar2(4000);
STATUS_CODE varchar2(4000);
REASON varchar2(4000);
DIRECTORY_NUMBER1 varchar2(4000);
STATUS_CODE1 varchar2(4000);
REASON1 varchar2(4000);
begin
   var_1 := replace(trim(',' from dbms_lob.substr(var_main,a,b)),' ' ,'');
   var_1 := trim(',' from var_1);
   b:= b + a;
dbms_output.put_line ('var_1 = ' || var_1);
execute immediate 'SELECT DIRECTORY_NUMBER, STATUS_CODE, REASON -- , OUTPUT
          FROM directory_number
         WHERE rownum =1 and
         directory_number IN(select regexp_substr(''' || var_1 || ''',''+'', 1, level) from dual
connect by regexp_substr(''' || var_1 || ''', ''+'', 1, level) is not null)
'
         into   DIRECTORY_NUMBER , STATUS_CODE,REASON   ;
-- into "+ context.output_directory_number +" ,"+ context.STATUS_CODE +" "+ context.REASON +" ;
DIRECTORY_NUMBER1 := DIRECTORY_NUMBER ;
STATUS_CODE1      :=STATUS_CODE;
REASON1           :=REASON;
--"+ context.output_directory_number +"     := DIRECTORY_NUMBER ;
--"+ context.STATUS_CODE +"               :=STATUS_CODE;
--"+ context.REASON +"                    :=REASON;

-- dbms_output.put_line( DIRECTORY_NUMBER1 );
-- dbms_output.put_line( STATUS_CODE1      );
-- dbms_output.put_line( REASON1           );
   dbms_output.put_line ('var_main_1 '   || var_main_1     );
end;"
from there I want to get the data in the context variable. I tried many many things but I can't get this done .
My talend flow will be like (tFileInputExecel -- tJavaRow -- tOracleRow)
This is just a POC , We will get number output from this query and I will use bulk collect in my original query if this POC is successful.
Any advice will be appriciated.

Re: get the value that is just inserted by tOracleRow

vapukov — Sun, 12 Mar 2017 11:02:48 GMT

Hi Team,
I have similar issue, I searched everywhere but don't know how to get the output into context variable using tOracleRow.
( Have done this with tOracleinput many times (tOracleinput -- tLogRow -- ... --) )
I have created a pl sql block in which I get some data .
--"+ context.REASON +"                    :=REASON;

-- dbms_output.put_line( DIRECTORY_NUMBER1 );
-- dbms_output.put_line( STATUS_CODE1      );
-- dbms_output.put_line( REASON1           );
   dbms_output.put_line ('var_main_1 '   || var_main_1     );
end;"
from there I want to get the data in the context variable. I tried many many things but I can't get this done .
My talend flow will be like (tFileInputExecel -- tJavaRow -- tOracleRow)
This is just a POC , We will get number output from this query and I will use bulk collect in my original query if this POC is successful.
Any advice will be appriciated.

do not try set context variable inside SQL code, this is different
Use Your code for generate output flow, and than use tFlowToIterate for store values to Global Variables, or tJavaFlex for set context variables, like:
context.reason = row1.REASON;
I can not test exactly Your code (for do this need have structure and data), but similar construction I use - it work

Re: get the value that is just inserted by tOracleRow

_AnonymousUser — Sun, 12 Mar 2017 12:20:49 GMT

Thanks Vapukov ,
Please share some screenshots if possible , I am new in talend and it will be very helpful.

Re: get the value that is just inserted by tOracleRow

_AnonymousUser — Sun, 12 Mar 2017 13:19:23 GMT

Hi Friends,
As suggested I am trying to get the output values of DIRECTORY_NUMBER, STATUS_CODE, REASON ( which is going to be like DIRECTORY_NUMBER = '99XXXXXXXX,99XXXXXXXX, ....., 99XXXXXXXX'
STATUS_CODE= '1,5,8,3,7 ...., 6 ,2,5'
REASON = 'Ported, non ported, ported , .... ported')
but let talk general scenario ,
What if some one is running a anonymous block(it has to be ananymous block) and wants some output to be captured and passed to next step of the job ( let say tJavaRow or tSendMail or tExceloutput etc) .
Please share some details so I can do that , I have tried all the possible options available to me in last three days with no luck.