Count number of records before and after flowfile and dynamic attribute usage

[#1]

Hello,

Could someone please help me with this case

I want to copy data from an Oracle table to another table that has the same schema. I want to count number of record read in and number of records succesfully inserted to the target, and put those counts into a tracking table. My idea is as below

In which, the left branch I count the records in, the right branch I insert data to target table and then count records.

-QueryRecord: count number of record. Output from left and right branch are the same and as below

-ExtractText: to extract just the count to record_in and record_out. The output attribute is as below

-MergeContent: Merge these 2 attributes before put SQL. Result and output attributes are as below

 

-UpdateAttribute: to escape user-based input

My query is something like:

INSERT INTO TABLE_1 (TABLE_NAME, RECORD_IN, RECORD_OUT) VALUES ('manually_input', ?, ?)

I got the error: ORA-00911: invalid character SQL = INSERT INTO TABLE_1 (TABLE_NAME, RECORD_IN, RECORD_OUT) VALUES ('manually_input', :1, :2)

How could I fix this error? Also is this a proper approach or would you suggest another way to perform this task? Thank you so much

[#2 · Accepted solution]

@xtd 
---
Based on your requirements, it looks like there is some unneeded processors that you have. 

Here is what my flow currently looks like - 

1. ExecuteSQL

This will return all the rows in the source table in avro format. If you look at the attributes, by default it will return an attribute called executesql.row.count which you can use as your source count for rows. So part of the left side on your flow can be removed.

2. PutDatabaseRecord

This will populate the target table with all the records. Only on success will go to the next processor.

3. CalculateRecordStats

This will create a new attribute called record.count that counts the number of rows in the full record - 

4. UpdateAttribute - 
This prepares for the PutSQL an creates the corresponding attributes - 

5. PutSQL 

Inserts new row into the table with input_count and output_count - 

Using the record-oriented processors, getting the counts paradigm is somewhat not needed because populating the target table either succeeds or fails. If it succeeds you can assume the row count will be same, if it fails then you could assume nothing was populated. However, above is approach to get the counts either way 🙂

Please accept this solution if it helped!

[#3]

Hi @drewski7 thank so much for helping. It works perfectly! :'D