[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

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.

Recommended Posts

[Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution
[Introduction to cx_Oracle] (Part 3) Basics of Table Reference
[Introduction to cx_Oracle] (Part 2) Basics of connecting and disconnecting to Oracle Database
[Introduction to cx_Oracle] (Part 7) Handling of bind variables
[Introduction to cx_Oracle] Overview of cx_Oracle
[Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set
Introduction to Scapy ① (From installation to execution of Scapy)
[Introduction to Data Scientists] Basics of Python ♬
[Introduction to cx_Oracle] (16th) Handling of LOB types
[Introduction to cx_Oracle] (5th) Handling of Japanese data
From the introduction of pyethapp to the execution of contract
Introduction to PyQt4 Part 1
[Python3] Connection to Oracle Database and SQL execution [cx_Oracle]
[Introduction to cx_Oracle] (Part 13) Connection using connection pool (client side)
Introduction to Anomaly Detection 1 Basics
[Introduction to cx_Oracle] (8th) cx_Oracle 8.0 release
Introduction to Ansible Part ③'Inventory'
Series: Introduction to cx_Oracle Contents
Basics of Python × GIS (Part 1)
Introduction to Ansible Part ④'Variable'
[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping
[Introduction to Data Scientists] Basics of Python ♬ Functions and classes
Basics of Python x GIS (Part 3)
Introduction to Ansible Part 2'Basic Grammar'
Introduction to Python Hands On Part 1
Introduction to Python Basics of Machine Learning (Unsupervised Learning / Principal Component Analysis)
Basics of Python x GIS (Part 2)
[Introduction to Data Scientists] Basics of Python ♬ Conditional branching and loops
Introduction to Ansible Part 1'Hello World !!'
[Introduction to Data Scientists] Basics of Python ♬ Functions and anonymous functions, etc.
[Introduction to Data Scientists] Basics of Probability and Statistics ♬ Probability / Random Variables and Probability Distribution
Web-WF Python Tornado Part 3 (Introduction to Openpyexcel)
[Introduction to cx_Oracle] (12th) DB Exception Handling
[Introduction to cx_Oracle] (17th) Date type handling
Jupyter Notebook Basics of how to use
Basics of PyTorch (1) -How to use Tensor-
Kaggle: Introduction to Manual Feature Engineering Part 1
[Introduction to cx_Oracle] (Part 9) DB and Python data type mapping (version 8 or later)
[Introduction to Udemy Python 3 + Application] 26. Copy of dictionary
[Ansible installation procedure] From installation to execution of playbook
Setting to output the log of cron execution
[Introduction to Udemy Python 3 + Application] 19. Copy of list
How to measure execution time with Python Part 1
Introduction to Python "Re" 1 Building an execution environment
Basics of Supervised Learning Part 3-Multiple Regression (Implementation)-(Notes)-
How to measure execution time with Python Part 2
[Introduction to Python] Basic usage of lambda expressions