Is it possible to call either Oracle or SQL server package from Boomi?
Yes you can. You can call SQL functions from Boomi to set properties. You can also call store procedures using the database connector. Here are a couple of examples.
Example 1 - Set properties using a SQL statement calling an Oracle PL/SQL package
Example 2 - Upserting records using an Oracle PL/SQL package called by the database connector
Is is possible to call below pl/sql statement using Set Property like Example 1?
DECLAREv_account_num NUMBER := '522751';var_dtl_tab adm.abc_pkg.dtl_tab := adm.abc_pkg.dtl_tab(); -- RowType b VARCHAR2(500) := NULL;
BEGIN adm.mgmt_intf_pkg.get_delr_details(v_account_num, var_dtl_tab); b:='Value at ' || 1 || ' IS ==>' || vardtl_tab(1).dler_name; END;
note : I want 'b' as an out parameter.
If is not possible with Set Property, Can you please tell us what's the other way around to achieve such calls?
FYI, We have tried the above snippets of code in Map using SqlLookup, but no luck, statement is executing but we are not able to get any output from that.
This looks like a PL/SQL script you run directly using Oracle SqlPlus or SQL Developer. I think the Boomi Database connector does not support that. The same counts for the Program Command shape. But I never tried it myself, to be honest.
But check out this document Database Integration Guide a search on Oracle. You will find many useful tips and tricks, including info about what you can do with JDBC and Oracle databases.
Retrieving data ...