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

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

CodePudding user response：

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

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.