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!
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
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
Or if you want to stick to your insert statement... tOracleRow (execute Insert Statement) --> OnSubjobOK --> tOracleInput (select field from table) ---> tLogRow
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.
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
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.
