Automate Everything with Python and Oracle EBS

Python is a very powerful programming language. Python can be used for automate anything and everything (with Python) for a variety of tasks. You can use it in data analysis, web development, machine learning, and automating different repeatating tasks. Oracle E-Business Suite (EBS) R12 is a enterprise applications to cover various business functions, such as finance, supply chain, human resources, and customer relationship management and many more. Here you will understand how Python script to connect to Oracle database and run SQL query. Subsequently you will understand automating the boring stuff with python.

In this blog post, you will understand how to automate everything with Python and automating common tasks in Oracle EBS.

• Connecting to Oracle Database and executing SQL queries
• Creating and updating records in Oracle EBS using the python-oracledb interface
• Using the Oracle E-Business Suite Adapter to integrate with third-party systems
• Generating reports and dashboards using Python libraries like pandas and matplotlib

Python script to connect to Oracle database

Oracle e-Business suite uses Oracle Database as RDMS system. To connect to Oracle Database using Python, you need to install the python-oracledb library. Oracledb is the new name for Oracle’s popular cx_oracle driver. Cx_Oracle was used to connect Oracle database using Python. It conforms to the Python Database API v2.0 Specification with a considerable number of additions and a couple of exclusions.

To install the python-oracledb module on Windows, you can use the following command in command line:

python -m pip install oracledb

On MacOS or Linux, you can use python3 instead of python in command line:

python3 -m pip install oracledb

Oracle db connection: Python Script to Connect to Oracle Database

Hope you have installed the oracledb module. Now you can import oracledb in your Python script and create a connection object using the oracledb.connect() method. You have to pass the user name, password, and data source name (DSN) as parameters.

For example:

import oracledb

connection = oracledb.connect(
  user="OraUser",
  password="OraPass",
  dsn="remotehost/xepdb1"
)

The DSN is a connection string to identify the database instance that you want to connect. It can be composed of different components, such as host name, port number, service name, or SID. You can also use a tnsnames.ora file to store the DSN information and refer to it by an alias.

Python Script to Connect to Oracle Database & Execute SQL

You created the connection object using the Oracle Apps R12 database connection details. Now you can use the oracledb connection object to create a cursor object by connection.cursor() method. The cursor object allows you to execute SQL statements and fetch the results. For example:

cursor = connection.cursor()

# Execute a SQL query
cursor.execute("SELECT * FROM employees")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print each row
for row in rows:
  print(row)

# Close the cursor
cursor.close()

Now you can provide the SQL statement as an argument of oracledb corsor object. Next, you need to execute the cursor object. You can use placeholders like :1, :2, etc. to bind variables to the statement to extract data from Oracle Database as given in the following example.

# Execute a SQL query with placeholders
cursor.execute("SELECT * FROM employees WHERE department_id = :1", [10])

# Fetch one row from the result set
row = cursor.fetchone()

# Print the row
print(row)

The cursor.fetchall() method returns all rows from the result set as a list of tuples. The cursor.fetchone() method returns one row from the result set as a tuple. You can also use the cursor.fetchmany() method to return a specified number of rows as a list of tuples.

Close the Database Connection

You need to use the connection.close() method to close the connection to the database. For example:

# Close the connection
connection.close()

Creating and updating records in Oracle EBS using the python-oracledb interface

Another task that you can automate with Python is creating and updating records in Oracle EBS tables using the python-oracledb interface. This can be useful for inserting or modifying data in bulk or based on some logic by the python automation scripts.

To create or update records in Oracle EBS tables using Python, you need to use the cursor.executemany() method, which takes a SQL statement and a list of values as arguments and executes it multiple times on the database in the python automation scripts. For example:

# Create a list of values to insert into the todoitem table
rows = [
  ("Task 1", 0),
  ("Task 2", 0),
  ("Task 3", 1),
  ("Task 4", 0),
  ("Task 5", 1)
]

# Execute a SQL statement to insert multiple rows into the todoitem table
cursor.executemany("INSERT INTO todoitem (description, done) VALUES (:1, :2)", rows)

# Print the number of rows inserted
print(cursor.rowcount, "Rows Inserted")

# Commit the changes to the database
connection.commit()

The cursor.executemany() method executes the SQL statement for each tuple in the list of values and binds the variables to the placeholders. You can use this method to insert, update, or delete multiple rows in a single operation.

To commit the changes to the database, you need to use the connection.commit() method. This ensures that the changes are permanent and visible to other sessions. You can also use the connection.rollback() method to undo the changes if something goes wrong.

Using the Oracle E-Business Suite Adapter to integrate with third-party systems

Another task that you can automate with Python is using the Oracle E-Business Suite Adapter to integrate with third-party systems. The Oracle E-Business Suite Adapter is one of many predefined adapters included with Oracle Integration that allows you to securely connect and use Oracle E-Business Suite services in integrations in Oracle Integration.

The Oracle E-Business Suite Adapter supports both inbound and outbound integrations. Inbound integrations allow you to invoke Oracle E-Business Suite services from external applications or systems. Outbound integrations allow you to subscribe to Oracle E-Business Suite events and send them to external applications or systems.

How to invoke a REST API from Oracle E-Business Suite using Python

To use the Oracle E-Business Suite Adapter with Python, you need to create an integration in Oracle Integration and configure the adapter as a source or target connection. You also need to specify the Oracle E-Business Suite service that you want to invoke or subscribe to, such as a REST API, a SOAP web service, a PL/SQL API, or a business event.

For example, if you want to invoke a REST API from Oracle E-Business Suite to create an invoice from Python, you can follow these steps:

• Create an integration in Oracle Integration Cloud (OIC) and configure the Oracle E-Business Suite Adapter as a target connection.
• Specify the REST API endpoint, authentication method, and request parameters for creating an invoice.
• Map the data elements from the source application (Python) to the target application (Oracle E-Business Suite).
• Activate and test the integration.

How to subscribe to a business event from Oracle E-Business Suite using Python

Alternatively, if you want to subscribe to a business event from Oracle E-Business Suite and send it to Python, you can follow these steps:

• Create an integration in Oracle Integration and configure the Oracle E-Business Suite Adapter as a source connection.
• Specify the business event name, category, and payload schema for subscribing to an invoice creation event.
• Map the data elements from the source application (Oracle E-Business Suite) to the target application (Python).
• Activate and test the integration.

You can use any Python library or framework that supports HTTP requests and responses, such as requests, urllib, or Flask, to communicate with the integration endpoint. For example:

import requests

# Define the integration endpoint URL
url = "https://enodeas.com/integration/REST/createInvoice"

# Define the request headers
headers = {
  "Content-Type": "application/json",
  "Authorization": "Basic dXNlcm5hbWU6cGFzc3dvcmQ="
}

# Define the request body
body = {
  "invoice_number": "INV-001",
  "invoice_date": "2023-12-31",
  "invoice_amount": 1000,
  "customer_id": 123
}

# Send a POST request to the integration endpoint
response = requests.post(url, headers=headers, json=body)

# Print the response status code and content
print(response.status_code)
print(response.content)

Generating reports and dashboards using Python libraries like pandas and matplotlib

You can also automate generate reports and dashboards using Python libraries like pandas and matplotlib. Pandas is a popular library for data analysis and manipulation. Pandas provides high-performance data structures and operations. Matplotlib is a powerful library for data visualization that allows you to create various types of plots and charts.

To generate reports and dashboards using Python, you need to import pandas and matplotlib in your Python script and use their methods and functions to load, process, analyze, and visualize data from Oracle EBS tables or services. For example:

import pandas as pd
import matplotlib.pyplot as plt

# Load data from an Oracle EBS table into a pandas DataFrame
df = pd.read_sql("SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id", connection)

# Print the DataFrame
print(df)

# Plot a bar chart of average salary by department id using matplotlib
df.plot.bar(x="department_id", y="avg_salary", title="Average Salary by Department")
plt.show()

You can use pd.read_sql() method to execute a SQL query on an existing database connection and return the dataframe. Pandas dataframe can be used to analyze the extracted data from Oracle EBS.

Conclusion

You’ve just learned how to automate everything with Python and Oracle EBS. By following these simple and effective methods, you can save time, improve efficiency, and enhance your productivity. Whether you want to retrieve or manipulate data from Oracle EBS tables, invoke or subscribe to Oracle EBS services, or create stunning reports and dashboards, Python has you covered. Now it’s time to put these skills into practice. If you have any questions or feedback, feel free to leave a comment below. And don’t forget to share this post with your friends and colleagues who might benefit from it. Happy coding!