Press "Enter" to skip to content

How to Call Oracle Function in Python?

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/[email protected]/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:

Vinish Kapoor

FoxInfotech.in is created, written, and maintained by Vinish Kapoor. It is built on WordPress, and hosted by Bluehost. Connect with Vinish on Facebook, Twitter, and GitHub.

You may also like: