question Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished in Support Questions

Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

LejlaKM — Thu, 10 Feb 2022 12:47:15 GMT

Hi,

can someone help me to find solution for execute plsql oracle procedure after I finish putin record in my table in database. I have flow like this

and when all job is done I have to execute procedure with max and min ID from this flow...

Thanks in advance

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

steven-matison — Thu, 10 Feb 2022 14:17:11 GMT

@LejlaKM Please check out this solution:

https://community.cloudera.com/t5/Support-Questions/Does-ExecuteSQL-processor-allow-to-execute-stored-procedure/m-p/285372/highlight/true#M211779

Found on this original thread:

https://community.cloudera.com/t5/Support-Questions/Does-ExecuteSQL-processor-allow-to-execute-stored-procedure/td-p/158922/page/2

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

LejlaKM — Fri, 11 Feb 2022 08:17:52 GMT

Hi,

I am new in nifi so I don't know how to create and pass parameter with max and min value of my ID in source table to plsql procedure... Please help. My whole flow now looks like this

I add another processor like this with two parameters

and I pass those parametres to proseccor like this

then I get error

Thank you

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

steven-matison — Fri, 11 Feb 2022 14:01:33 GMT

I would recommend do a test of the procedure without parameters by inserting test values directly to ensure it works. This will do 2 things, first; confirm the procedure works, and second; indicate the issue with min/max is in the QueryRecord. While testing flow, you should be able to see what the values of min/max are by inspecting the flowfile/attributes.

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

LejlaKM — Fri, 11 Feb 2022 14:03:34 GMT

Hi,

I have already tried and everything works when there are no parameters

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

steven-matison — Fri, 11 Feb 2022 14:09:24 GMT

Ok great, then the issue is the QueryRecord. In your flow I can see that min and max are both routes to PutSQ, not attributes of a single flowfile.

Have you inspected these flowfiles to see if they contain the correct values you need?

Does it send 2 flowfiles to PutSQL?

${min} and ${max} is an attribute syntax. I believe you will need to get the min and max into a single flowfile and extracted as attributes to send to PutSQL

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

LejlaKM — Fri, 11 Feb 2022 14:27:17 GMT

Hi,

I made some changes....

Now, I have two separate flow files (one for insert and one for delete) which works fine. And in the end I put Output port.

After that job is done, I need to execute plsql procedure from destination database, so I made another process group with input port and another flow

Processor generate table fetch selecting max and min date from one table like this

And after that I need those two values to pass to plsql procedure and I don't know how.

Query record processor look like this

Last processor putsql look like this

But this also doesn’t work

because in last que i get two separate flow files one with min value and one with max....I think that is problem.... and I need one with two values that i can pass to plsql procedure

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

steven-matison — Fri, 11 Feb 2022 15:54:20 GMT

Excellent progress. Can you try doing a single select to get both values?

Like this: SELECT MAX(ID),MIN(ID) FROM FLOWFILE

If you could do that, then you could extract those values into 2 separate attributes, then feed that flowfile to the next processor in the flow. Another alternative is to extractText for each value to an attribute, then MERGE 2 flowfiles into 1.

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

LejlaKM — Fri, 11 Feb 2022 16:03:02 GMT

Hi,

yes I can do this

And then I get

and when I call procedure it doesn't work

Do I need more steps between query record and executing procedure?

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

LejlaKM — Fri, 11 Feb 2022 16:09:10 GMT

Hi,

As you can see I tried with Id instead date just to be sure that problem is not because format date, but it is not the problem. Problem is how to send those values to procedure.....

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

steven-matison — Fri, 11 Feb 2022 16:24:05 GMT

Right, you need another processor to extract the values to an attribute for it to work like this ${attribute}

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

LejlaKM — Fri, 11 Feb 2022 17:00:43 GMT

Hi,

can You help me how to do it?

Thanks in advance

Re: Can I call oracle plsql procedure after my processor PutDatabaseRecord finished

LejlaKM — Mon, 14 Feb 2022 08:14:01 GMT

Hi,

is there any chance that you can help me with this... I don't know how to pass those values to procedure

Thanks