How to return columns from dynamic SQL in Python console

Question

I'm trying to make a CRUD application that uses Oracle procedures for the operations. For the READ component, I wrote a procedure that returns one or more entire rows depending on the specified attributes. It works correctly on Oracle SQL Developer, but I can't get the data to display once I'm in Python.

This is the READ portion of the Python app:

def verEmpleado():
    opcionCorrecta = False
    while(not opcionCorrecta):
        print('================= CONSULTA =================')
        print('[1] Employee ID')
        print('[2] Employee name')
        print('[3] Job')
        print('[4] Manager')
        print('[5] Hire date')
        print('[6] Salary')
        print('[7] Commission')
        print('[8] Department ID')
        print('============================================')

        command = int(input('Seleccione la columna: '))
        match command:
            case 1:
                p_filter_att = 'empno'
                opcionCorrecta = True
            case 2:
                p_filter_att = 'ename'
                opcionCorrecta = True
            case 3:
                p_filter_att = 'job'
                opcionCorrecta = True
            case 4:
                p_filter_att = 'mgr'
                opcionCorrecta = True
            case 5:
                p_filter_att = 'hiredate'
                opcionCorrecta = True
            case 6:
                p_filter_att = 'sal'
                opcionCorrecta = True
            case 7:
                p_filter_att = 'comm'
                opcionCorrecta = True
            case 8:
                p_filter_att = 'deptno'
                opcionCorrecta = True
            case _:
                print('Comando incorrecto, intente de nuevo...\n')


    p_value_att = input('Ingrese el valor de la columna a seleccionar: ')

    data = [p_filter_att, p_value_att]
    return data

#CONNECT TO DB
def connVerEmpleado(data):
        try:
            conn = cx_Oracle.connect('HR/hr@localhost:1521/xepdb1')
        except Exception as err:
            print('Excepción al crear la conexión:', err)
        else:
            try:
                cursor = conn.cursor()
                cursor.callproc('query_emp', data)

                result = cursor.fetchall()
                print(result)
                cursor.close()

            except Exception as err:
                print('Error al conectar:', err)

This is the procedure

create or replace PROCEDURE query_emp(p_filter_att IN VARCHAR2, p_value_att IN VARCHAR2)
IS
    sql_qry              VARCHAR2(1000);
    TYPE bc_v_empno      IS TABLE OF NUMBER(4);
    v_empno              bc_v_empno;
    TYPE bc_v_ename      IS TABLE OF VARCHAR2(10);
    v_ename              bc_v_ename;
    TYPE bc_v_job        IS TABLE OF VARCHAR2(9);
    v_job                bc_v_job;
    TYPE bc_v_mgr        IS TABLE OF NUMBER(4);
    v_mgr                bc_v_mgr;
    TYPE bc_v_hiredate   IS TABLE OF DATE;
    v_hiredate           bc_v_hiredate;
    TYPE bc_v_sal        IS TABLE OF NUMBER(7,2);
    v_sal                bc_v_sal;
    TYPE bc_v_comm       IS TABLE OF NUMBER(7,2);
    v_comm               bc_v_comm;
    TYPE bc_v_deptno     IS TABLE OF NUMBER(2);
    v_deptno             bc_v_deptno;
BEGIN
    sql_qry := 'SELECT * FROM emp WHERE ' || p_filter_att || ' = UPPER(:p_value_att)';
    EXECUTE IMMEDIATE sql_qry BULK COLLECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
    USING p_value_att;

    FOR i IN 1..v_empno.COUNT
        LOOP
            dbms_output.put_line('ID: ' || v_empno(i) || ' | NAME: ' || v_ename(i) || ' | JOB: ' || v_job(i) || ' | MANAGER: ' || v_mgr(i) || ' | HIRE DATE: ' || v_hiredate(i) || ' | SALARY: ' || v_sal(i) || ' | COMMISSION: ' || v_comm(i) || ' | DEPARTMENT: ' || v_deptno(i));
        END LOOP;

END query_emp;
/

When I use fetchall() it returns the error not a query, other methods don't return anything at all.

Christopher Jones · Accepted Answer · 2024-10-29 22:05:45Z

0

There's an example of using DBMS_OUTPUT in samples/dbms_output.py. It is also discussed in the documentation Using DBMS_OUTPUT.

However, DBMS_OUTPUT is not the best choice. Instead, just return a REF CURSOR similar to samples/ref_cursor.py. The documentation for this is at REF CURSOR Bind Variables.

From the doc, if you create a PL/SQL procedure like:

CREATE OR REPLACE PROCEDURE find_employees (
    p_query IN VARCHAR2,
    p_results OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_results FOR
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE UPPER(first_name || ' ' || last_name || ' ' || email)
            LIKE '%' || UPPER(p_query) || '%';
END;
/

then you can call it in Python like:

ref_cursor = connection.cursor()
cursor.callproc("find_employees", ['Smith', ref_cursor])
for row in ref_cursor:
    print(row)

edited Oct 29, 2024 at 22:05

answered Oct 29, 2024 at 21:21

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

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

2 Comments

sodaCodes Over a year ago

Thank you! The example in the documentation helped, but I'll check out the REF CURSOR information

Christopher Jones Over a year ago

The REF CURSOR solution should be used for various reason. Also you tagged the question "cx-oracle". It's time to upgrade from cx_Oracle to python-oracledb.

MT0 · Accepted Answer · 2024-10-29 22:14:53Z

Don't print the output to a console in the database. Create a cursor and return that.

CREATE PROCEDURE query_emp(
  p_filter_att IN  VARCHAR2,
  p_value_att  IN  VARCHAR2,
  p_results    OUT SYS_REFCURSOR
)
IS
  v_handle PLS_INTEGER;
  v_rows   PLS_INTEGER;
BEGIN
  v_handle := DBMS_SQL.OPEN_CURSOR;
 
  DBMS_SQL.PARSE(
    v_handle,
    'SELECT * FROM emp WHERE ' || p_filter_att || ' = UPPER(:value)',
    DBMS_SQL.NATIVE
  );
  DBMS_SQL.BIND_VARIABLE(v_handle, 'value', p_value_att);
  v_rows := DBMS_SQL.EXECUTE(v_handle);

  p_results := DBMS_SQL.TO_REFCURSOR(v_handle);
END query_emp;
/

Then call it in Python and read from the cursor:

#CONNECT TO DB
def connVerEmpleado(data):
    try:
        conn = cx_Oracle.connect('HR/hr@localhost:1521/xepdb1')
    except Exception as err:
        print('Excepción al crear la conexión:', err)
    else:
        try:
            cursor = conn.cursor()
            result_cursor = conn.cursor()
            cursor.callproc('query_emp', [*data, result_cursor])
            print(result_cursor)
            cursor.close()
        except Exception as err:
            print('Error al conectar:', err)

Note: Python code is untested.

Collectives™ on Stack Overflow

How to return columns from dynamic SQL in Python console

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related