Insert into with returning into :array

Question

i want to add multiple rows to table in oracle and get back added values to python list

the returned column is the primary key and is generated on the oracle side

the code I wrote doesn't work, which is understandable, but I don't know how to write otherwise. help me please

out_id = cursor.var(cx_Oracle.NUMBER)

batch = []
for i in range(3):
    batch.append({'val': i})

ins = 'INSERT INTO table (col1) VALUES (:val) RETURNING table.col2 into :out_id'

cursor.executemany(ins, batch)

print(list_out_id.getvalue())

I get an error

ORA-01036: illegal variable name/number

Answer 1

@e.burenina A few observations here:

• I am assuming col2 is an AUTO INCREMENT Number field
• I am assuming you meant to use out_id instead of list_out_id in the last line
• arraysize parameter should be included when you define the cursor variable out-id. i.e., out_id = cursor.var(cx_Oracle.NUMBER,arraysize=3).
• cx_Oracle.NUMBER will return any number in Python float format(e.g., 0.0, 1.0 etc.). So if the returned data is an integer, please use out_id = cursor.var(int,arraysize=3)
• The batch list should include both the input 'val' and the output variable out_id batch.append({'val':i,'out_id':out_id})

The following code should return the required values:

import cx_Oracle
# Establish the database connection - Add your DB details here
connection = cx_Oracle.connect(user="hr", password="hr", dsn="localhost/orclpdb")

# Obtain a cursor
cursor = connection.cursor()

# set the input and output variables
out_id = cursor.var(int, arraysize=3)
batch = []
for i in range(3):
    batch.append({'val': i, 'out_id': out_id})

# set the SQL statement
ins = 'INSERT INTO table(col1) VALUES (:val) RETURNING table.col2 into :out_id'

# run the query and fetch the output into 'out_id' variable
cursor.executemany(ins, batch)

# print the out_id in a list
print(out_id.values)

Note: Please ensure that you do a connection.commit() at the end, in case you want to commit your code changes to the DB

Also make changes to the arraysize parameter based on the number of rows returned by your INSERT statement.

Answer 2

For printing output in the format

[{'val': 1, 'out_id': id_1}, {'val': 2, 'out_id': id_2}, {...}]

the following code will work

import cx_Oracle
# Establish the database connection - Add your DB details here 
connection = cx_Oracle.connect(
    user="hr", password="hr", dsn="localhost/orclpdb")

# Obtain a cursor cursor = connection.cursor()

# set the input and output variables
out_id = cursor.var(int, arraysize=3)
batch = []
for i in range(3):
    batch.append({'val': i, 'out_id': out_id})    

# set the SQL statement
ins = 'INSERT INTO table(col1) VALUES (:val) RETURNING table.col2 into :out_id'

# run the query and fetch the output into 'out_id' variable 
cursor.executemany(ins, batch)

#print the output in the required format
for i in range(3):
    batch[i]['out_id'] = batch[i]['out_id'].getvalue(i)
print(batch) 
print(out_id.values)
# Do a commit to the table if required
# connection.commit()