Freezing/hanging when executing Oracle DB PL/SQL procedure in Python

Question

Hello I created a PLSQL procedure to update or insert quantity in inventory table based on product Id and new inward qty. Below is my PLSQL procedure and it works fine when i try in sql developer. But when i trying to execute this in python it is stopped working and froze Kindly help.

PLSQL Procedure:

create table product_master
(Product_id number(10),
Product_name VARCHAR2(56),
Category_id number(8));

Create table Inventory
(Product_id number(10), location_id VARCHAR2(26), Qty number(5));

Insert into product_master values (101, 'Milk bikis Rs10', 5);
Insert into product_master values (102, 'Milk bikis Rs20', 5); 



CREATE OR REPLACE PROCEDURE InvInsert (p_id IN NUMBER, p_qty  IN NUMBER)
    is
    null_constraint  EXCEPTION;
    PRAGMA EXCEPTION_INIT (null_constraint, -1400);
    BEGIN 
    UPDATE Inventory SET qty = qty + p_qty WHERE product_id = p_id;
    IF SQL%NOTFOUND THEN
       Insert into Inventory values ((Select Product_id from product_master where Product_id = p_id),'A24', 1);
    END IF;
    EXCEPTION
    WHEN null_constraint THEN
    DBMS_OUTPUT.put_line ('The product id is NULL. please update product master with the new item code');
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Others');
    commit;
    END; 
    /

Python program to call above procedure:

import cx_Oracle
import cred as cd
import pandas as pd

user = cd.user
pwd = cd.pawd
crd = (user+'/'+pwd+'@//localhost:1521/xe')
print(crd)

#('Username/password@//hostname:port/SID')
conn = cx_Oracle.connect(crd) 
print('Connection success')
try:    
    cur = conn.cursor()
    cur.callproc('InvInsert',(101,10))
    cur.close()
except Exception as err:
    print('oops! problem')
else:
    print('Hey smile! its executed')
conn.close()

Please edit the question to include the Python error messages. — MT0
– MT0, Commented Sep 16, 2022 at 9:46
If it 'froze', the problem may be that your SQL Developer session has locked some rows. Make sure to commit or rollback in SQL Developer before running in Pythong. — Christopher Jones
– Christopher Jones, Commented Sep 16, 2022 at 14:45

thatjeffsmith · Accepted Answer · 2022-09-16 15:08:38Z

Your COMMIT is in the wrong place.

CREATE OR REPLACE PROCEDURE InvInsert (p_id IN NUMBER, p_qty  IN NUMBER)
    is
    null_constraint  EXCEPTION;
    PRAGMA EXCEPTION_INIT (null_constraint, -1400);
    BEGIN 
    UPDATE Inventory SET qty = qty + p_qty WHERE product_id = p_id;
    IF SQL%NOTFOUND THEN
       Insert into Inventory values ((Select Product_id from product_master where Product_id = p_id),'A24', 1);
    END IF;
    EXCEPTION
    WHEN null_constraint THEN
    DBMS_OUTPUT.put_line ('The product id is NULL. please update product master with the new item code');
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Others');
    commit; -- this only runs when there's an unhandled exception
    END; 
    /

You could put the COMMIT directly after the INSERT, or you could handle the transaction in your Python program.

Here's what I've done -

CREATE OR REPLACE PROCEDURE InvInsert (p_id IN NUMBER, p_qty  IN NUMBER)
    is
    null_constraint  EXCEPTION;
    PRAGMA EXCEPTION_INIT (null_constraint, -1400);
    BEGIN 
    UPDATE Inventory SET qty = qty + p_qty WHERE product_id = p_id;
    IF SQL%NOTFOUND THEN
       Insert into Inventory values ((Select Product_id from product_master where Product_id = p_id),'A24', 1);
       commit;
    END IF;
    EXCEPTION
    WHEN null_constraint THEN
    DBMS_OUTPUT.put_line ('The product id is NULL. please update product master with the new item code');
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Others');
    END; 
/

And the Python - note I'm using the new python-oracledb thin driver, no Oracle Client required.

Then here's my Python code -

# test.py

import oracledb
import os

un = os.environ.get('PYTHON_USERNAME')
pw = os.environ.get('PYTHON_PASSWORD')
cs = os.environ.get('PYTHON_CONNECTSTRING')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        cursor.callproc('InvInsert', [102,10])
print('Hey smile! its executed')

Then running it -

THEN checking the data in the database...do I have a new entry in my INVENTORY table?

YES

Your PL/SQL program has other areas for improvement...for example you could have an OUT parameter to send some kind of message to the Caller.

WHEN OTHERS THEN ... you're basically providing zero help there when you have a problem with your program, you should at least log the core Oracle error to a table.

Thanks for the explanation. This really helps. Sure I will implement your advice.

Collectives™ on Stack Overflow

Freezing/hanging when executing Oracle DB PL/SQL procedure in Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related