Inserting multiple values in PL/SQL in Python with a single call

Question

I need to insert multiple values in PL/SQL with a single call in Python because we have a 3Gb xml file.

Here is my code:


y = 0
for x in range(0,len(rows)):
    x = x + 1
    if x == y + 500 :
      cur.prepare("BULK INSERT INTO cm_raw (fecha,distname,clase,parametro,valor) VALUES (:1,:2,:3,:4,:5)")
      datos = [(str(date.today().strftime("%d/%m/%Y")),rows[y:x])]
      print (datos)
      cur.executemany(None,rows)
      con.commit()
      con.close
      y = x

And this is a screenshot of my error log

`BULK INSERT ` is not available in oracle. maybe is just `insert` what you are looking for — hotfix, Jun 06 '19 at 14:48
yes, i try without BULK but not work. error is : cx_Oracle.NotSupportedError: Python value of type tuple not supported — joaquin castelari, Jun 06 '19 at 14:50
We need insert many values because we have documents xml whit size 3gb — joaquin castelari, Jun 06 '19 at 14:59
Possible duplicate of [How can I do a batch insert into an Oracle database using Python?](https://stackoverflow.com/questions/14904033/how-can-i-do-a-batch-insert-into-an-oracle-database-using-python) — Kaushik Nayak, Jun 06 '19 at 16:08
No @KaushikNayak , because i have my code with the solution gave in the other problem and doesn't work — joaquin castelari, Jun 06 '19 at 16:31

Christopher Jones · Accepted Answer · 2022-03-10T21:56:33.350

2

To call PL/SQL with multiple data value look at the cx_Oracle examples bind_insert.py, batch_errors.py, and array_dml_rowcounts.py from the cx_Oracle samples directory which all insert multiple rows with one single executemany() call.

The examples show DML statements (INSERT etc) but you can also call a PL/SQL block multiple times with different parameters with executemany():

data = [
    (10, 'Parent 10'),
    (20, 'Parent 20'),
    (30, 'Parent 30'),
    (40, 'Parent 40'),
    (50, 'Parent 50')
]
cursor.executemany("begin mypkg.create_parent(:1, :2); end;", data)

Using executemany() with SQL is going to be a lot faster than repeated calls to execute(). It is also faster with PL/SQL unless you have OUT binds.

There is more information and more examples (including a PL/SQL one) on executemany() in Batch Statement Execution and Bulk Loading

edited Mar 10 '22 at 21:56

answered Jun 06 '19 at 21:22

Christopher Jones

9,449
3
24
48

ROWS IS ROWS=""[[('07/06/2019', 'PLMN-PLMN', 'PLMN', '', '')], [('07/06/2019', 'PLMN-PLMN/OMC-103042', 'OMC', '$maintenanceRegionId', '2')], [('07/06/2019', 'PLMN-PLMN/BSC-224514','BSC', 'name', 'OMC')], [('07/06/2019', 'PLMN-PLMN/BSC-232892', 'BSC', '$maintenanceRegionId', '1400')], [('07/06/2019', 'PLMN-PLMN/MRBTS-1807', 'MRBTS', 'wcdmaRanSaiPenalty', '127')], [('07/06/2019', 'PLMN-PLMN/MRBTS-2115', 'MRBTS', 'sharedRfTechnologies', '0')]] – joaquin castelari Jun 07 '19 at 11:31
it's array list , no array – joaquin castelari Jun 07 '19 at 11:32
thanks, i could undestend an changed my array , thanks for all – joaquin castelari Jun 07 '19 at 13:58

score 0 · Answer 2 · answered Jun 06 '19 at 17:01

0

I think you may be looking for INSERT ALL instead of BULK INSERT. As hotfix mentioned, BULK INSERT is not available in Oracle. INSERT ALL is mentioned in Oracle's documentation at https://docs.oracle.com/database/121/SQLRF/statements_9015.htm#SQLRF01604

As for the Python value of type tuple not supported error you're receiving, please try reviewing this github thread. I am not familiar with Python but I think it may point you in the right direction: https://github.com/oracle/python-cx_Oracle/issues/171

answered Jun 06 '19 at 17:01

1991DBA

805
1
9
18

I could see and i change "bulk insert" for "insert all" and looking that https://github.com/oracle/python-cx_Oracle/issues/171 i can understend he problem but in he's problem he want insert many values and one array list, but i need insert many values, not many values with array list. Sry for my english – joaquin castelari Jun 06 '19 at 17:23

Inserting multiple values in PL/SQL in Python with a single call

2 Answers2