Thanks for your instructions. Unfortunately I didn't get it working yet.
1) Firstly I want to avoid extra steps like evaluate xpath and
replacetext. Would be easier if ${message} or ${file-content} or
whatever variable in the expression language is available
out-of-the-box (Camel provides this for example) and use the
expression language variable in the SQL directly.
Using the extra steps didn't work either. The evaluation of /* or
/Rootname gave only the values and not the element names (and
attributes and namespaces) of the XML. However when using another
xpath parser (for example online) it returned the complete XML.
2) Executing the stored procedure (which doesn't return a dataset) returned:
"Failed to update database due to a failed batch update. There were a
total of 1 FlowFiles that failed, 0 that succeeded, and 0 that were
not execute and will be routed to retry; "
This was done with the literal SQL that worked with another client. I
don't know what exactly the above message means (I also didn't get the
direct failure messages from Oracle, for example "ORA-0001 failure"
and had to debug the SQL in DBeaver. Maybe the failure message has
something to do with https://issues.apache.org/jira/browse/NIFI-1791
(where the patch seem to be provided after the last release).
Raymond
2016-06-10 19:25 GMT+02:00 Keith Lim <keith....@ds-iq.com>:
> Hi Raymond,
>
>
> Let's say you have an input flowfile that contains an xml message with a
> root node <RootMessage> xml message </RootMessage>
>
>
> You can use EvaluateXPath to extract out the content of the flowfile into a
> custom property
>
> Destination = flowfile-attribute
>
> New Property message = /RootMessage
>
>
> Next create an instance of ReplaceText processor to replace all the content
> of the flowfile
>
> Replacement Value = EXEC SP.INSERT_XML(XMLTYPE(${message})
>
>
> Next create an instance of PutSQL processor and set the JDBC Connection Pool
> accordingly. (Note: make sure the stored proc body does not return a data
> set via select, currently PutSql does not support that.)
>
>
> That should do it.
>
>
> HTH,
>
> Keith
>
> ________________________________
> From: ski n <raymondmees...@gmail.com>
> Sent: Friday, June 10, 2016 7:05 AM
> To: users@nifi.apache.org
> Subject: How to call a stored procedure
>
> I want to call a stored procedure as follows:
>
> BEGIN SP.INSERT_XML(XMLTYPE(${message}); END;
>
> I have two questions:
>
> 1) I want to use the flowfile content as input parameter. How is this
> done with the expression language?
> 2) Are stored procedures supported by a processor (PutSQL or
> ExecuteSQL) without the use of external tools?
