# Getting back a variable from contrib-oracledb-mod

**URL:** <https://discourse.nodered.org/t/getting-back-a-variable-from-contrib-oracledb-mod/93930>\
**Category:** General\
**Created:** [16 December 2024 09:32 UTC](https://discourse.nodered.org/t/getting-back-a-variable-from-contrib-oracledb-mod/93930 "2024-12-16T09:32:03Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![omrid](https://avatars.discourse-cdn.com/v4/letter/o/77aa72/32.png) [@omrid](https://discourse.nodered.org/u/omrid)\
**Post date:** [16 December 2024 09:32 UTC](https://discourse.nodered.org/t/getting-back-a-variable-from-contrib-oracledb-mod/93930/1 "2024-12-16T09:32:03Z")

</div>

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:

```auto
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.

> [@omrid](#):
>
> 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.
> 
> ```auto
> select BREACH_SEQ.nextval from dual
> 
> ```

---

<div class="post-metadata">

**Author:** ![omrid](https://avatars.discourse-cdn.com/v4/letter/o/77aa72/32.png) [@omrid](https://discourse.nodered.org/u/omrid)\
**Post date:** [16 December 2024 09:47 UTC](https://discourse.nodered.org/t/getting-back-a-variable-from-contrib-oracledb-mod/93930/2 "2024-12-16T09:47:30Z")

</div>

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.

```auto
select DBWR.BREACH_SEQ.nextval from dual

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [30 December 2024 09:48 UTC](https://discourse.nodered.org/t/getting-back-a-variable-from-contrib-oracledb-mod/93930/3 "2024-12-30T09:48:15Z")

</div>

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