There’s a couple of days I didn’t write here, and specially in the Python for Oracle series.

In this blog post, I will write a bit on how calling a plsql procedure or function from a Python script using the cx_Oracle package.

There’s nothing very difficult to do that, you just have to consider 3 cases :

• Calling a stored procedure with IN arguments
• Calling a stored procedure with IN and OUT arguments
• Calling a function and getting the result.

For all these cases, there are two common operations to do:

1. Connect to Oracle database
2. Create a cursor from this connection

Once you get the cursor … it depends from what you want to do:

Calling a stored procedure with IN arguments

In the case you have a simple call to do with only IN arguments, you have to call the callproc function from the cx_Oracle cursor object, and give an array of parameters … and that’s it.

In real, and in the case you call a stored procedure which have this declaration :

create or replace procedure t(a in number, b in varchar2)

.../...

it gives something like that:

c=cx_Oracle.Connection

try:
    c=connectToOracle("192.168.99.3:1521/orcl","laurent","laurent")
    curs=c.cursor();
    curs.callproc("T", [2,"parameter1"])
    curs.close()
except cx_Oracle.DatabaseError as ex:
    err, =ex.args
    print("Error code    = ",err.code)
    print("Error Message = ",err.message)
    os._exit(1)

connectToOracle is a function I coded previously and which is not detailed here, but its code is the one below:

def connectToOracle(url, username, password, mode=None):
    if mode is not None:
        connection = cx.Connection (user=username, password=password, dsn=url, mode=mode)
    else:
        connection = cx.Connection (user=username, password=password, dsn=url)
    return connection

Calling a stored procedure with IN and OUT parameters

In that case, it’s not really different from the previous example because you will give the same array as procedure parameter. The only difference is one (or many parameters) will be modified by the procedure execution.

But let’s see an example.

Consider this procedure definition (very simple ok 😉 )

create or replace procedure t(a in number, b out number) as
begin
  b := a*a;
end;
/

Calling this procedure in a python script will look like this

c=cx_Oracle.Connection
try:
    c=connectToOracle("192.168.99.3:1521/orcl","laurent","laurent")
    curs=c.cursor();

    r=curs.var(cx_Oracle.NUMBER)
    curs.callproc("T", [10,r])
    print("Result = ",r.getvalue(pos=0))

    curs.close()
except cx_Oracle.DatabaseError as ex:
    err, =ex.args
    print("Error code    = ",err.code)
    print("Error Message = ",err.message)
    os._exit(1)

You have remark that, you have to create a variable with the same cx_Oracle internal data type as you plsql out parameter, and then convert it into the python type with getvalue function. (Every function related to cx_Oracle variables are documented here: https://cx-oracle.readthedocs.io/en/latest/variable.html)

Calling a function and get the result into a python script

The principle is basically the same, once you get a connection, you create a cursor from it, and then you will call the “callfunc” function with these parameters:

• Name of the plsql function
• Result type, explained with cx_Oracle types (cx_Oracle.NUMBER, or other types declared here https://cx-oracle.readthedocs.io/en/latest/module.html#types)
• plsql function parameter list

And this call will return the result of this function.

For example, if we consider a PLSQL function declared like this, and that return the salary of an employee:

CREATE OR REPLACE EDITIONABLE FUNCTION "LAURENT"."GET_SAL" (fname in varchar2, lname in varchar2) return number
is
 sal number;
 l_ln varchar2(128) := lower(lname);
 l_fn varchar2(128) := lower(fname);
begin
 select salary into sal from emp where lower(first_name) like '%'||l_fn||'%' and lower(last_name) like '%'||l_ln||'%';
 return sal;
exception
   when NO_DATA_FOUND then raise_application_error(-20000,'No employee with this last_name and first_name');
end;
/

It will be called like this in a python script:

c=cx_Oracle.Connection

try:
    c=connectToOracle("192.168.99.3:1521/orcl","laurent","laurent")
    curs=c.cursor();
    
    janette_king_sal = curs.callfunc("GET_SAL", cx_Oracle.NUMBER, ["Janette", "King"]);
    print("janette king sal", janette_king_sal)
    
curs.close()
except cx_Oracle.DatabaseError as ex:
    err, =ex.args
    print("Error code    = ",err.code)
    print("Error Message = ",err.message)
    os._exit(1)

As you can see, dealing with PLSQL and Python is not very difficult, and let you keeping the code close to your data.

That’s it for today 🙂