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:
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:
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.
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.