How to execute multiple Oracle statements from .sql script in Airflow OracleOperator

Question

I am using OracleOperator to execute sql at remote database source. Below is the simple operator code written in Airflow DAG,

t2 = OracleOperator(
    task_id='task_2',
    oracle_conn_id='ORA_DATABASE_SYSTEM',
    sql='/query.sql',
    dag=dag)

query.sql is the file created , which i want to have multiple sql statements which can be DDL or DML, but while running DAG it throws

cx_Oracle.DatabaseError: ORA-00922: missing or invalid option

.sql file have following sql statement,

create table SP(name varchar(50), age int);
insert into SP (name,age) values('Suraj',24);
insert into SP values('Ashish',27);

AM i doing something wrong ? It would be help if anyone can share trying different or pointing right way of doing it.

AshishPatil AshishPatil · Accepted Answer · 2018-11-02T10:25:10

After some try outs, i am able to use Oracle Operator to execute multiple sql statements on remote Oracle database using .sql script.

# template_searchpath is the path where .sql files are stored
dag = DAG('Test_Oracle_Connection', default_args=default_args,template_searchpath=['/home/Ashish'])

t2 = OracleOperator(
    task_id='task_2',
    oracle_conn_id='Oracle_connection',
    sql='/oracle_query.sql',                             #SQL file name
    dag=dag)

oracle_query.sql file contains multiple sql statements, for example

DECLARE
  sql_smt VARCHAR2 (5000);
sql_smt := q'[<SQL_STATEMENT>]'
EXECUTE IMMEDIATE sql_smt;
EXECUTE IMMEDIATE 'commit';

This might not be the best way but at least working for me at the moment. Please share any other best way if there, to connect to remote Oracle database and execute your queries which can be DDL or DML.

How to execute multiple Oracle statements from .sql script in Airflow OracleOperator

1 Answers