Oracle Procedure with OUT Parameters Example

Questions
1

Does anyone here has a working example for getting an oracle procedure with IN and OUT parameters running and to have the OUT parameters passed to the next node?

I tried the build in oracle node and the rempel oracle node, but i did not succeed with either version.

Kind regards

2

Hello @holgerdeha ,

The easiest way to force a Procedure to return data to n8n is to wrap it in a temporary function using the WITH FUNCTION clause (available in Oracle 12c and later). This allows you to run a standard SELECT statement that calls your procedure and returns the value as a row.

Try this:

  1. Use the standard Oracle node.
  2. Set the Operation to Execute SQL.
  3. Paste the following code into the Query field.

Assumption you have a procedure named MY_PROC(p_in IN VARCHAR2, p_out OUT VARCHAR2).

WITH
  FUNCTION run_wrapper(p_input_val IN VARCHAR2) RETURN VARCHAR2 IS
    v_result VARCHAR2(4000); -- Adjust type to match your OUT param
  BEGIN
    -- Call your actual procedure here
    MY_PROC(p_input_val, v_result);
    RETURN v_result;
  END;
SELECT run_wrapper('YourInputData') AS output_value FROM DUAL

If my answer helped solve your question, would you mind marking it as the solution? It’ll help others find it more easily—and I’d really appreciate it! Thanks!

3

How would i do this with multiple IN and multiple OUT Params? In my case, we’re trying to replace another Workflow System with n8n and we’re having many Procedures where one OUT parameter give the succes/error code back to the workflow and another OUT parameter describes the actual error to the user (or to a logfile).

I wonder why the standard oracle node has the option to define IN, IN-OUT and OUT parameters if the values aren’t available afterwards. Strange behavior of n8n in my opinion.

4

I completely agree with you—it is very confusing UX. The node settings imply that OUT parameters are first-class citizens, but because the node is architected to return “Table Rows” (Result Sets) rather than “Bind Variables,” those outputs just vanish into the void upon success.

For replacing a legacy system with multiple OUT params (Status + Error Message), the Code Node is definitely the robust way to go. It gives you direct access to the outBinds object so you can map them exactly how you want.

5

Thank you very much for the hint with using the WITH Statement function wrapper. I did a little research and i came up with this solution, that helped here.

This is my Query in standard oracle node:

WITH
FUNCTION run_wrapper(
pParam1 IN VARCHAR2,
pParam2 IN NUMBER,
pParam3 IN VARCHAR2,
pParam4 IN VARCHAR2,
pParam5 IN VARCHAR2
) RETURN VARCHAR2
IS
vParam1 VARCHAR2(4000) := pParam1;
vParam4 VARCHAR2(4000) := pParam4;

vOut1   NUMBER;
vOut2   NUMBER;
vOut3   VARCHAR2(4000);

BEGIN
my_procedure(
vParam1, – IN OUT / optional
pParam2, – IN
pParam3, – IN
vParam4, – IN OUT / optional
pParam5, – IN
vOut1, – OUT
vOut2, – OUT
vOut3 – OUT
);

RETURN json_object(
  'param1' VALUE vParam1,
  'param4' VALUE vParam4,
  'out1'   VALUE vOut1,
  'out2'   VALUE vOut2,
  'out3'   VALUE vOut3
);

END;
SELECT run_wrapper(
:pParam1,
:pParam2,
:pParam3,
:pParam4,
:pParam5
) AS result_json
FROM dual

Do NOT put a semikolon in the end after “dual”! Then define the Parameters (in my case pParam1 … pParam5) as IN-Parameters with the values from the node before the oracle node. (please be aware, that i anonymized my actual query and have not really tested this solution. Important is, that you declare IN-OUT params in the query in case you’re having any of them.

After that, define a Code node to beautify the Result.

I hope this could help someone.

Thank you very much.

2 Likes
6

This is only working when the SP does not execute DML Statements.

We definitely need a working solution with IN/OUT Bindings in oracle n8n nodes.