tOracleRow: how to get the number of rows deleted

[Anonymous]

I want to use tOracleRow to delete rows from table, and here is what I have within tOracleRow:

"
begin
delete from myTable;
commit;
end;

"

Then I connected the tOracleRow with tJava (using OnComponentOk) and tried to print out the total row number:
System.out.println("Num of rows deleted = " +
((Integer)globalMap.get("tDBRow_1_NB_LINE_DELETED")));

But I only got : Num of rows deleted = null

How could I use the variable to get the number of rows? (There were rows in myTable and rows were deleted when running this job)

Thanks!

[Anonymous]

Hi,

     You are using a PLSQL block instead of direct delete statement and that is the reason why its not displaying the data. Please refer the description below for using the Delete rows parameter.

If you are using PLSQL, you will have to use SQL%ROWCOUNT parameter from PLSQL block to check the DML processed records. Please refer the Oracle document below for sample scenario.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm

You can pass this value back to Talend wrapper program from PLSQL block and from there Talend can take control for further validations.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[lennelei]

@nthampi : could you please elaborate on this statement please?

"You can pass this value back to Talend wrapper program from PLSQL block and from there Talend can take control for further validations."

How do you pass the value back to Talend? Do you simply do a select on it and read the value through an output row or is there some other option?

[Anonymous]

Thanks for the reply!

Could you please give an Talend example for :

"use SQL%ROWCOUNT parameter from PLSQL block to check the DML processed records. " ?

Again, I appreciate your help!

[Anonymous]

Hi,

    You will have to use OUT parameter to propagate the data from Oracle DB back to Talend wrapper job. I would suggest to check Oracle forums also to do the same for anonymous blocks with Oracle. Below is a sample link which is showing sub program data transfer.

https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/08_subs.htm

    Please refer the below link for sample scenario to pass data from tDBRow.

https://help.talend.com/reader/mjoDghHoMPI0yuyZ83a13Q/kIvdIycGZDSuSD~dX8RTfA

    At the same time, why don't you try tDBOutput component with Delete option to do the same task?

You can then use the below parameters in the next subjob to check the number of records processed. I would say this is much easier method.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Anonymous]

How to achieve this functionality.Please someone help me on this. My requirement is to use only tDBRow.

Thanks in advance.