[PYTHON] [Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution

Serial table of contents

Series: Introduction to cx_Oracle Table of Contents

Verification environment

--Using Oracle Cloud

• Oracle Linux 7.7 (VM.Standard2.1)
• Python 3.6
• cx_Oracle 8.0
• Oracle Database 19.5 (ATP, 1OCPU)
• Oracle Instant Client 18.5

Anonymous PL / SQL execution

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())

Execution of stored procedure

There are two types of execution methods for executing stored procedures.

Use the Cursor.callproc () method

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.

Execute SQL CALL statement using Cursor.execute () method

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 ().

Execution of stored function

There are two types of execution methods for executing stored procedures.

Use the Cursor.callfunc () method

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.

Execute SQL CALL statement using Cursor.execute () method

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.