Getting back a variable from contrib-oracledb-mod

General
1

Hi,
I am using node-red-contrib-oracledb-mod and it works very well.
In one of my use-cases, I insert to a table which auto-generates a transaction Id, and returns it into a variable which is then used in an additional insert to another table.
I do both inserts as one transaction, with commit/rollback in the end, hence I send a PL/SQL block:

msg.query =
"DECLARE new_id NUMBER;
BEGIN
INSERT INTO <table 1> (TRX_ID,...) VALUES(BR_SEQ.nextval,...)
returning TRX_ID into new_id;

INSERT INTO <table 2> (TRX_ID,...) VALUES (new_id,...);
<commit/rollback etc.>;
END;"

When running it, I get no output (response payload is undefined).
Any idea how I can get back the new_id variable to the flow?
I could re-query my insert, but afraid of race condition with other concurrent dashboard clients.

2

Solved.
I can select the sequence from DUAL before my Insert, on a separate query (which allocates & reserves the id for me). Then specify it in the insert.

select DBWR.BREACH_SEQ.nextval from dual
3

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.