Brief about the Blog:

In this, I will give an example on how to use the Cursor.callfunc() to call a PL/SQL stored function from a Python program.

Creation of Function

This function is wrote for to get the profit in current year to a sales team

CREATE OR REPLACE FUNCTION get_profit(

    salesman_id NUMBER,

    Year NUMBER)

RETURN NUMBER

IS

    l_revenue NUMBER;

BEGIN

    SELECT

        SUM(quantity*unit_price)

    INTO l_revenue

    FROM

        orders

    INNER JOIN

        order_items USING (order_id)

    WHERE

        salesman_id = salesman_id AND

        EXTRACT(YEAR FROM order_date) = YEAR;

    RETURN l_revenue;

END;

Below Anonymous block will be helpful for to test the Function,

SET SERVEROUTPUT ON;

DECLARE

    l_revenue NUMBER;

BEGIN

    l_revenue := get_profit(12, 2020);

    dbms_output.put_line(l_revenue);

END;

————————————

Output:

2500320

To execute a PL/SQL stored function, you use the Cursor.callproc() method.

The following code illustrates how to call the stored function get_profit() and display the revenue of salesman 12 in the year 2020:

import cx_Oracle

import config as cfg

def get_profit(salesman_id, year):

    “””

    Get revenue by salesman in a specific year

    :param salesman_id:

    :param year:

    :return: the revenue

    “””

    revenue = None

    try:

        # create a connection to the Oracle Database

        with cx_Oracle.connect(cfg.username,

                            cfg.password,

                            cfg.dsn,

                            encoding=cfg.encoding) as connection:

            # create a new cursor

            with connection.cursor() as cursor:

                # call the function

                revenue = cursor.callfunc(‘get_profit,

                                        float,

                                        [salesman_id, year])

    except cx_Oracle.Error as error:

        print(error)

    return revenue

if __name__ == ‘__main__’:

    sales_revenue = get_profit(12, 2020)

    print(sales_revenue)  # 2500320

In this example:

First, establish a connection to the Oracle Database by calling the cx_Oracle.connect() method with the parameters provided by the config module:

username = ‘OT’

password = ‘<password>’

dsn = ‘localhost/pdborcl’

port = 1512

encoding = ‘UTF-8’

Second, create a new Cursor object using the Connection.cursor() method.

Third, call the stored function get_profit() using the Cursor.callfunc() method:

cursor.callfunc(‘get_profit’, float, [salesman_id, year])

In the Cursor.callfunc() method: the first argument is the stored function’s name, the second argument is the type of the returned value, and the third argument is a list of arguments passed to the stored function.

The following code tests the get_profit() function with the salesman 12 and the year 2020:

sales_revenue = get_profit(12, 2020)

print(sales_revenue)  # 2500320

In this tutorial, you have learned how to use the Cursor.callfunc() method to call a PL/SQL stored function in Python.