Unlock Mule 4 Secrets: Master Stored Procedures with Oracle ADTs in...

Unlock Mule 4 Secrets: Master Stored Procedures with Oracle ADTs in 5 Easy Steps

Blogs IT, Cloud, Software and Technology

Posted 2024-09-18 21:39:00

394

Unlock the full potential of Mule 4 by mastering the art of invoking stored procedures with complex Abstract Data Types (ADTs) as IN, OUT, and IN OUT parameters.

The lack of comprehensive documentation and technical hurdles in retrieving ADT responses in Mule runtime 4.x.x have discouraged developers from leveraging MuleSoft for this purpose. However, by following the steps outlined below, you can overcome these challenges and successfully integrate DataWeave 2.0 and Mule's built-in connectors.

To illustrate this process, we will use a stored procedure as a reference. This procedure features an IN OUT parameter of Table type (Array) of a custom Abstract Data type, declared as follows:

invoking-stored-procedure-with-abstract-data-type_img_0.png

invoking-stored-procedure-with-abstract-data-type_img_1.png

invoking-stored-procedure-with-abstract-data-type_img_2.png

Important note: If the stored procedure is declared in a different user/schema than the one being used in Anypoint Studio, a public synonym must be created for the custom ADT types.

When setting up database connections, make sure to utilize the latest database driver, as shown below:

invoking-stored-procedure-with-abstract-data-type_img_3.png

The database connector should be at least version 1.5.5, preferably the latest.

invoking-stored-procedure-with-abstract-data-type_img_4.png

Next, populate the table type and object/record type using DataWeave, as demonstrated below:

invoking-stored-procedure-with-abstract-data-type_img_5.png

When populating the type, enter the database configuration name (Database_Config) while creating the struct and array in DataWeave. It is essential to declare the name of the Oracle table and record type in capital letters, along with the schema name.

By following these steps, you can unlock the full potential of Mule 4 and master the art of invoking stored procedures with complex Abstract Data Types. For more information on data engineering and Mule 4, visit carsnewstoday.com.

Execute the stored procedure via the database connector:

invoking-stored-procedure-with-abstract-data-type_img_6.png