I'm posting this here for everyone who needs this functionality. I needed it and could not find any satisfactory solution so I hope this is of use to someone else.

I've been looking for a long time for a solution on how to deal with oracle procedures with output parameters.

Some background info;
We're running our new frontoffice-screens in OutSystems; and our data is stored in Oracle Databases.
These databases contain 20+years-old functionality and we'd like to call those functions and procedures as they are proven and we don't want to migrate those (yet).

Outsystems version: 11.10.22
Oracle database version: 12.1

So functions were never an issue but procedures, especially with outputs, were.
I've come up with this solution
So to explain a little;

• I declare procedure a here, but that could be any procedure in your database.
• Function b is calling procedure a and creates a JSONstring out of the outputs
• The actual select takes the JSONstring and turns it into "normal" columns that can be mapped to our structure
  • The cross join is unfortunately needed to actually call the database; without it, it won't run

There are currently two downsides and that's because of our database version.

• The JSONstring cannot exceed 32767 characters so we are limited in our output; Oracle 18 (I believe) is able to return CLOBs so that would solve that issue
• I create the JSONstring here manually, but in Oracle 12.2 there is build-in functionality to create the string which would be much easier.

Anyway our outcome of this query isAnd that is what I need.

I hope this will help someone.

with regards,
Rogier Fluitsma