Series: Introduction to cx_Oracle Table of Contents
--Using Oracle Cloud
Anonymous PL / SQL execution, like any other SQL statement, can be done with the execute () method of the Cursor object. Below is a sample. Like the sample, bind variables are also available.
sample11a.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
begin
:out_value := :in_value * 2;
end;
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
outValue = cursor.var(int)
cursor.execute(SQL, [outValue, 111])
print(outValue.getvalue())
There are two types of execution methods for executing stored procedures.
sample11b.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
create or replace procedure sample11b(in_value in number, out_value out number) is
begin
out_value := in_value * 2;
end;
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
outValue = cursor.var(int)
cursor.execute(SQL)
cursor.callproc("sample11b", [222, outValue])
print(outValue.getvalue())
As shown in the second line from the bottom, specify the name of the stored procedure you want to call in the first argument with str type. For the second argument, specify the argument to the stored procedure according to the argument specifications of the procedure.
sample11c.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
create or replace procedure sample11b(in_value in number, out_value out number) is
begin
out_value := in_value * 2;
end;
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
outValue = cursor.var(int)
cursor.execute(SQL)
cursor.execute("call sample11b(:a, :b)", [333, outValue])
print(outValue.getvalue())
You must specify the procedure arguments as bind variables.
Which is better, callproc () or execute () + CALL, in the case of callproc (), callproc () of DB API does not correspond to the specification of the argument with the argument name. However, cx_Oracle extends the DB API to support it. If you want to code strictly corresponding to DB API, use execute () + CALL. However, cx_Oracle has quite a few proprietary extensions that the DB API does not have, so it seems difficult to strictly adhere to them. On the contrary, I think that the clarity of coding is callproc (). Also, it seems that the specifications of stored procedures and stored function calls in the CALL statement may differ slightly compared to callproc (). If you are concerned about these points, we recommend using callproc ().
There are two types of execution methods for executing stored procedures.
sample11d.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
create or replace function sample11d(in_value in number)
return number is
begin
return in_value * 2;
end;
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
cursor.execute(SQL)
returnValue = cursor.callfunc("sample11d", int, [111])
print(returnValue)
As shown in the second line from the bottom, the function returns the operation result, so it is received as a variable (returnValue in the sample case). The first argument of the callfunc () method is the function name, which must be passed in str type. The second specifies the data type of the return value of the function. The third is the function argument.
sample11e.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
create or replace function sample11d(in_value in number)
return number is
begin
return in_value * 2;
end;
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
with connection.cursor() as cursor:
outValue = cursor.var(int)
cursor.execute(SQL)
cursor.execute("call sample11d(:inValue) into :outValue", [222, outValue])
print(outValue.getvalue())
It's basically the same as executing a stored procedure. Note that for stored functions, an INTO clause is required to receive the return value.
Which of the above two execution methods is better is basically the same idea as a stored function. The difference is that there is no callfunc () in the DB API.
Recommended Posts