SQLExecuteQueryOperator to connect to Oracle¶

Use the SQLExecuteQueryOperator to execute Oracle commands in a Oracle database.

Note

Previously, OracleStoredProcedureOperator was used to perform this kind of operation. After deprecation this has been removed. Please use SQLExecuteQueryOperator instead.

Using the Operator¶

Use the conn_id argument to connect to your Oracle instance where the connection metadata is structured as follows:

Oracle Airflow Connection Metadata¶
Parameter	Input
Host: string	Oracle database hostname
Schema: string	Schema to execute SQL operations on by default
Login: string	Oracle database user
Password: string	Oracle database user password
Port: int	Oracle database port (default: 1521)
Extra: JSON	Additional connection configuration, such as DSN string: `{"dsn": "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service_name>)))"}`

An example usage of the SQLExecuteQueryOperator to connect to Oracle is as follows:

oracle/tests/system/oracle/example_oracle.py[source]

    # Example of creating a task that calls a common CREATE TABLE sql command.
    create_table_oracle_task = SQLExecuteQueryOperator(
        task_id="create_table_oracle",
        sql=r"""
            BEGIN
                EXECUTE IMMEDIATE '
                CREATE TABLE employees (
                    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                    name VARCHAR2(50),
                    salary NUMBER(10, 2),
                    hire_date DATE DEFAULT SYSDATE
                )';
            END;
        """,
    )

Reference¶

For further information, look at:

Oracle Documentation

Note

Parameters given via SQLExecuteQueryOperator() are given first-place priority relative to parameters set via Airflow connection metadata (such as schema, login, password etc).