How to Call Oracle Function in Python?

How to Call Oracle Function in Python?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

In this tutorial, you will learn how to call Oracle function in Python. I am giving an example by using cx_Oracle library callfunc function in the Python program. The following are the syntax details and the sample programs.

CX_Oracle callfunc Function Syntax

cursor.callfunc('oracle_function_name', variable_to_hold_return_value, [argument_1, argument_2, ...])

Oracle Database Function Example

The following Oracle function will take two number arguments to calculate the percentage of first number by second number.

CREATE OR REPLACE FUNCTION calc_percentage (p_1 IN NUMBER, p_2 IN NUMBER)
RETURN NUMBER
IS
n_pct NUMBER := 0;
BEGIN
IF p_1 IS NOT NULL
THEN
n_pct := (p_1 * p_2) / 100;
END IF;

RETURN n_pct;
END calc_percentage;
/

Example to Call Oracle Function in Python

The below Python program will call the above Oracle function by passing n_1 as first parameter and n_2 as the second parameter. Also, n_pct Number variable is used to hold the return value by function.

import cx_Oracle

con = cx_Oracle.connect('scott/tiger@localhost/orcl')

cur = con.cursor()

n_pct = cur.var(cx_Oracle.NUMBER)

n_1 = 80
n_2 = 20

cur.callfunc('calc_percentage', n_pct, [n_1, n_2])

print (str(n_2)+"%", " Percent of ", n_1, " is: ", n_pct.getvalue())

cur.close()
con.close()

Output

20% Percent of 80 is: 16.0

See also: