Oracle Operators¶

The Oracle connection type provides connection to a Oracle database.

Execute SQL in an Oracle database¶

To execute arbitrary SQL in an Oracle database, use the OracleOperator.

An example of executing a simple query is as follows:

airflow/providers/oracle/example_dags/example_oracle.py[source]

    opr_sql = SQLExecuteQueryOperator(
        task_id="task_sql", conn_id="oracle", sql="SELECT 1 FROM DUAL", autocommit=True
    )

Execute a Stored Procedure in an Oracle database¶

To execute a Stored Procedure in an Oracle database, use the OracleStoredProcedureOperator.

Assume a stored procedure exists in the database that looks like this:

CREATE OR REPLACE PROCEDURE
TEST_PROCEDURE (val_in IN INT, val_out OUT INT) AS
BEGIN
val_out := val_in * 2;
END;
/

This stored procedure accepts a single integer argument, val_in, and outputs a single integer argument, val_out. This can be represented with the following call using OracleStoredProcedureOperator with parameters passed positionally as a list:

airflow/providers/oracle/example_dags/example_oracle.py[source]

    opr_stored_procedure_with_list_input_output = OracleStoredProcedureOperator(
        task_id="opr_stored_procedure_with_list_input_output",
        oracle_conn_id="oracle",
        procedure="TEST_PROCEDURE",
        parameters=[3, int],
    )

Alternatively, parameters can be passed as keyword arguments using a dictionary as well.

airflow/providers/oracle/example_dags/example_oracle.py[source]

    opr_stored_procedure_with_dict_input_output = OracleStoredProcedureOperator(
        task_id="opr_stored_procedure_with_dict_input_output",
        oracle_conn_id="oracle",
        procedure="TEST_PROCEDURE",
        parameters={"val_in": 3, "val_out": int},
    )

Both input and output will be passed to xcom provided that xcom push is requested.

More on stored procedure execution can be found in oracledb documentation.