Background: I'm trying to call a stored procedure from an Oracle server using an SQL widget, but this stored procedure has cursors. I did a bit of research into Oracle PL/SQL, when passing parameters to functions and procedures, the parameters can be declared as IN or OUT or IN OUT parameters. The parameter I'm trying to declare is an IN OUT parameter so I'm wondering essential if OutSystems has the ability to do this and if not is there another way I can approach this step. Any and all help would be great.
Hi Akeem,
Unfortunately, this is not possible with OutSystems. A SQL query can only have a list of records as its output. Although, when using Aggregates, the Platform can use cursors in certain circumstances, as far as I know it's not possible to use this in combination with SQL and stored procedures.
If I were to use aggregates, how would I go about using a cursor. Also, what certain circumstances would cursors fall under.
If you do not copy the output of an aggregate via a ListAppendAll, and do not pass the output to an action, and For Each over the output, the Platform likely doesn't fetch the whole list at once, but uses a cursor so each For Each retrieves a single record. But you cannot use your own cursors, it's just that the Platform uses them in that case (you can see this if you debug, only the Current record is then available instead of the whole list).