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

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()

Answer

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.