I'm trying to make a CRUD application that uses Oracle procedures for the operations. Each procedure has exceptions and one has a trigger associated to it, but when using the Python code none of them are handled. For example, if I try to enter a duplicated ID, it should handle an exception since they should be unique. It works fine when executing on Oracle, but when executed from Python it acts as if the record was inserted despite that not being the case.
This is the inserting portion of the python code
import cx_Oracle
#INSERT EMPLOYEE
def crearEmpleado():
validEmpno = False
while(not validEmpno):
p_empno = input('Ingrese el número de empleado: ')
if p_empno.isnumeric() and int(p_empno) > 0 and int(p_empno) <= 999999:
validEmpno = True
p_empno = int(p_empno)
else:
print('Error')
validEname = False
while(not validEname):
p_ename = input('Ingrese el nombre del empleado: ')
if len(p_ename) > 0 and len(p_ename) <= 10:
validEname = True
else:
print('Error')
validJob = False
while(not validJob):
p_job = input('Ingrese el puesto del empleado: ')
if len(p_job) > 0 and len(p_job) <= 9:
validJob = True
else:
print('Error')
validMgr = False
while(not validMgr):
p_mgr = input('Ingrese el número de manager: ')
if p_mgr.isnumeric() and int(p_mgr) > 0 and int(p_mgr) <= 9999:
validMgr = True
p_mgr = int(p_mgr)
else:
print('Error')
validHiredate = False
while(not validHiredate):
p_hiredate = input('Ingrese la fecha de contratación en formato DD/MM/YYYY: ')
if len(p_hiredate) > 0 and len(p_hiredate) <= 10:
validHiredate = True
else:
print('Error')
validSal = False
while(not validSal):
p_sal = input('Ingrese el salario: ')
if float(p_sal) > 0 and float(p_sal) <= 99999.99:
validSal = True
p_sal = float(p_sal)
else:
print('Error')
validComm = False
while(not validComm):
p_comm = input('Ingrese el porcentaje de comisión: ')
if float(p_comm) <= 99999.99:
validComm = True
p_comm = float(p_comm)
else:
print('Error')
validDeptno = False
while(not validDeptno):
p_deptno = input('Ingrese el número de departamento: ')
if p_deptno.isnumeric() and int(p_deptno) > 0 and int(p_deptno) <= 99:
validDeptno = True
p_deptno = int(p_deptno)
else:
print('Error')
data = [p_empno, p_ename, p_job, p_mgr, p_hiredate, p_sal, p_comm, p_deptno]
return data
#CONECT
def connCrearEmpleado(data):
conn = cx_Oracle.connect('HR/hr@localhost:1521/xepdb1')
try:
cursor = conn.cursor()
cursor.callproc('add_emp', data)
print('\n¡Empleado registrado!\n')
except cx_Oracle.DatabaseError as err:
print('Database connection error: {}'.format(err))
print(err.code)
print(err.message)
print(err.context)
raise
This is the table being used:
CREATE TABLE emp (
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10) NOT NULL,
job VARCHAR2(9) NOT NULL,
mgr NUMBER(4) NOT NULL,
hiredate DATE NOT NULL,
sal NUMBER(7,2) NOT NULL,
comm NUMBER(7,2),
deptno NUMBER(2) NOT NULL
)
And this is the PL/SQL procedure:
create or replace PROCEDURE add_emp
(p_empno in NUMBER, p_ename in VARCHAR2, p_job in VARCHAR2, p_mgr in NUMBER, p_hiredate in VARCHAR2, p_sal in NUMBER, p_comm in NUMBER, p_deptno in NUMBER)
IS
v_empno NUMBER;
e_unique_empno EXCEPTION;
e_notnull_empno EXCEPTION;
e_notnull_ename EXCEPTION;
e_notnull_job EXCEPTION;
e_notnull_mgr EXCEPTION;
e_notnull_hiredate EXCEPTION;
e_valid_sal EXCEPTION;
e_notnull_sal EXCEPTION;
v_deptno NUMBER;
e_notnull_deptno EXCEPTION;
BEGIN
IF p_empno IS NULL THEN
RAISE e_notnull_empno;
ELSIF p_ename IS NULL THEN
RAISE e_notnull_ename;
ELSIF p_job IS NULL THEN
RAISE e_notnull_job;
ELSIF p_mgr IS NULL THEN
RAISE e_notnull_mgr;
ELSIF p_hiredate IS NULL THEN
RAISE e_notnull_hiredate;
ELSIF p_sal IS NULL THEN
RAISE e_notnull_sal;
ELSIF p_sal <= 0 THEN
RAISE e_valid_sal;
ELSIF p_deptno IS NULL THEN
RAISE e_notnull_deptno;
END IF;
SELECT COUNT(*) INTO v_empno FROM emp WHERE p_empno = empno;
IF v_empno > 0 THEN
RAISE e_unique_empno;
END IF;
INSERT INTO emp
VALUES(p_empno, UPPER(p_ename), UPPER(p_job), p_mgr, TO_DATE(p_hiredate, 'DD-MM-YYYY'), p_sal, p_comm, p_deptno);
COMMIT;
EXCEPTION
WHEN e_unique_empno THEN
dbms_output.put_line('El ID ya existe.');
WHEN e_notnull_empno THEN
dbms_output.put_line('El campo EMPNO no puede ser nulo.');
WHEN e_notnull_ename THEN
dbms_output.put_line('El campo ENAME no puede ser nulo.');
WHEN e_notnull_job THEN
dbms_output.put_line('El campo JOB no puede ser nulo.');
WHEN e_notnull_mgr THEN
dbms_output.put_line('El campo MGR no puede ser nulo.');
WHEN e_notnull_hiredate THEN
dbms_output.put_line('El campo HIRE_DATE no puede ser nulo.');
WHEN e_valid_sal THEN
dbms_output.put_line('El salario no puede ser menor o igual a cero.');
WHEN e_notnull_sal THEN
dbms_output.put_line('El campo SALARY no puede ser nulo.');
WHEN e_notnull_deptno THEN
dbms_output.put_line('El campo DEPTNO no puede ser nulo.');
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END add_emp;
/
None of the PL/SQL exceptions are raised when executing from Python
Add a primary key constraint to the table:
ALTER TABLE emp ADD CONSTRAINT emp__empno__pk PRIMARY KEY(empno);
Then don't catch the exception and print an error to the console in Oracle (because your Python code won't see the Oracle console). Let the exceptions be raised and let the PRIMARY KEY and NOT NULL constraints do their job rather than trying to manually do exactly the same thing.
CREATE PROCEDURE add_emp(
p_empno in EMP.EMPNO%TYPE,
p_ename in EMP.ENAME%TYPE,
p_job in EMP.JOB%TYPE,
p_mgr in EMP.MGR%TYPE,
p_hiredate in VARCHAR2,
p_sal in EMP.SAL%TYPE,
p_comm in EMP.COMM%TYPE,
p_deptno in EMP.DEPTNO%TYPE
)
IS
BEGIN
INSERT INTO emp(
empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno
) VALUES(
p_empno,
UPPER(p_ename),
UPPER(p_job),
p_mgr,
TO_DATE(p_hiredate, 'DD-MM-YYYY'),
p_sal,
p_comm,
p_deptno
);
END add_emp;
/
Also, don't COMMIT in procedures as it prevents you from chaining multiple procedures together within a transaction and then using ROLLBACK on them all. Instead, COMMIT from the code calling the procedure (i.e. Python).
If you want, you can convert hiredate to a datetime object in Python and then pass that rather than passing strings.