Hello,

I want to create one Engagement designer application. In this application , I want to call Some 'Database procedure'.
So is that possible to call 'Database procedure' from engagement designer 'DB Task'

Thanks in advance.

Hi Rangari,
For calling your stored procedures, it might be best to write a simple Java dynamic task. The SDK is available to do the same.
Thanks
Vishal

Hi Vishal,

Thanks for replying.
Is there any other way instead of writing a simple Java dynamic task.If not, can you assist me how to write dynamic task or you have any example of procedure call dynamic task.


Thanks

We can execute a procedure using our read DB task ,


Created a sample function which would just count the number of rows in employee table and ran this on postgres :

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
total integer;
BEGIN
SELECT count(*) into total FROM employee;
RETURN total;
END;
$total$ LANGUAGE plpgsql;


So this function was created in DB :
\df

-[ RECORD 1 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | public
Name | totalrecords
Result data type | integer
Argument data types |
Type | normal


had a simple wfd, start-->readDB-->end
Sql query for read DB task ,
select totalrecords()


created an instance of this wfd the result below:
{'resultSet':[{'totalrecords':298}]}

Let us know if you still face issues.

For writing dynamic task , you could refer
https://support.avaya.com/css/P8/documents/101041265

Hello Vishal,

Thanks for replying.

I will follow your step and let you know my results.

Thanks

I think Rajesh describes the easiest way to do this and does not require writing a Dynamic Task.

You can find more info if you google for: Table-Valued Function.

Good Example: http://www.sommarskog.se/share_data.html#UDF

You can call your stored procedure from within the TVF and trigger it by quering the TVF from the "Read from DB" task.

Hello Rajesh and Jack,

I am using oracle DB. I have try to execute simple function from Read DB task but I am getting the below exception when retrieve Output Schema

"Error getting output schema for Read DB task:
java.lang.IllegalStateException: Failed to get resultSet metaData from SQL: Select TotalRecords()"

FYI , I have successfully test database connection with validation query.

Please let me know your thoughts.

Thanks

Oops, I was thinking about MS SQL.

Don't know how this works in Oracle. Perhaps Rajesh can help you.

Did you test your function/procedure in Oracle directly and does it return row data?
I think the metadata parser in Breeze needs a returned row of data to be able to parse it.

Perhaps this will help you to translate the MS T-SQL example into Oracle SQL:
https://stackoverflow.com/questions/2059299/table-valued-functions-in-oracle-11g-parameterized-views

Hi Jack,

Yes, I have successfully tested function in Oracle directly and it return row data

You returned data is probably just plain text, and not table output.

Search for "Oracle Pipelined Table Functions" to get an idea on how to generate table data.

One more question guys.

In Read From DB task, I am getting below error

" Error getting Input schema for Read DB task:
java.sql.SQLFeatureNotSupportedException:Unsuported Feature"

For some table I am getting above exception and other are working fine.