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
– hotfix, Commented Jun 6, 2019 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
– joaquin castelari, Commented Jun 6, 2019 at 14:50
We need insert many values because we have documents xml whit size 3gb — joaquin castelari
– joaquin castelari, Commented Jun 6, 2019 at 14:59
Possible duplicate of How can I do a batch insert into an Oracle database using Python? — Kaushik Nayak
– Kaushik Nayak, Commented Jun 6, 2019 at 16:08
No @KaushikNayak , because i have my code with the solution gave in the other problem and doesn't work — joaquin castelari
– joaquin castelari, Commented Jun 6, 2019 at 16:31

Christopher Jones · Accepted Answer · 2022-03-10 21:56:33Z

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, 2022 at 21:56

answered Jun 6, 2019 at 21:22

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

joaquin castelari Over a year ago

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 Over a year ago

it's array list , no array

joaquin castelari Over a year ago

thanks, i could undestend an changed my array , thanks for all

1991DBA · Accepted Answer · 2019-06-06 17:01:46Z

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 6, 2019 at 17:01

1991DBA

8051 gold badge9 silver badges18 bronze badges

1 Comment

joaquin castelari Over a year ago

I could see and i change "bulk insert" for "insert all" and looking that 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

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related