PL/SQL statement stop working after upgrade common-sql to 1.1.0

[hewerthomn]

Apache Airflow Provider(s)

common-sql, oracle

Versions of Apache Airflow Providers

apache-airflow-providers-common-sql==1.1.0
apache-airflow-providers-oracle==3.3.0

Apache Airflow version

2.3.3

Operating System

Debian GNU/Linux 11 (bullseye)

Deployment

Docker-Compose

Deployment details

No response

What happened

After upgrade provider common-sql==1.0.0 to 1.1.0 version, SQL with DECLARE stop working.

Using OracleProvider 3.2.0 with common-sql 1.0.0:

[2022-08-19, 13:16:46 -04] {oracle.py:66} INFO - Executing: DECLARE
    v_sql LONG;
BEGIN
    v_sql := '
create table usr_bi_cgj.dim_tarefa
(
    id_tarefa   NUMBER(22) not null primary key,
    ds_tarefa   VARCHAR2(4000) not NULL
);
';
    EXECUTE IMMEDIATE v_sql;
    COMMIT;
    EXCEPTION
        WHEN OTHERS
        THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE usr_bi_cgj.dim_tarefa';
    COMMIT;
END;
[2022-08-19, 13:16:46 -04] {base.py:68} INFO - Using connection ID 'bitjro' for task execution.
[2022-08-19, 13:16:46 -04] {sql.py:255} INFO - Running statement: DECLARE
    v_sql LONG;
BEGIN
    v_sql := '
create table usr_bi_cgj.dim_tarefa
(
    id_tarefa   NUMBER(22) not null primary key,
    ds_tarefa   VARCHAR2(4000) not NULL
);
';
    EXECUTE IMMEDIATE v_sql;
    COMMIT;
    EXCEPTION
        WHEN OTHERS
        THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE usr_bi_cgj.dim_tarefa';
    COMMIT;
END;, parameters: None
[2022-08-19, 13:16:46 -04] {sql.py:264} INFO - Rows affected: 0
[2022-08-19, 13:16:46 -04] {taskinstance.py:1420} INFO - Marking task as SUCCESS. dag_id=caixa_tarefa_pje, task_id=cria_temp_dim_tarefa, execution_date=20220819T080000, start_date=20220819T171646, end_date=20220819T171646
[2022-08-19, 13:16:46 -04] {local_task_job.py:156} INFO - Task exited with return code 0

After upgrade OracleProvider to 3.3.0 with common-sql to 1.1.0 version, same statement now throws an exception:

[2022-08-20, 14:58:14 ] {sql.py:315} INFO - Running statement: DECLARE
    v_sql LONG;
BEGIN
    v_sql := '
create table usr_bi_cgj.dim_tarefa
(
    id_tarefa   NUMBER(22) not null primary key,
    ds_tarefa   VARCHAR2(4000) not NULL
);
';
    EXECUTE IMMEDIATE v_sql;
    COMMIT;
    EXCEPTION
        WHEN OTHERS
        THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE usr_bi_cgj.dim_tarefa';
    COMMIT;
END, parameters: None
[2022-08-20, 14:58:14 ] {taskinstance.py:1909} ERROR - Task failed with exception
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/oracle/operators/oracle.py", line 69, in execute
    hook.run(self.sql, autocommit=self.autocommit, parameters=self.parameters)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/common/sql/hooks/sql.py", line 295, in run
    self._run_command(cur, sql_statement, parameters)
  File "/home/airflow/.local/lib/python3.7/site-packages/airflow/providers/common/sql/hooks/sql.py", line 320, in _run_command
    cur.execute(sql_statement)
  File "/home/airflow/.local/lib/python3.7/site-packages/oracledb/cursor.py", line 378, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 121, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 375, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 376, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 369, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-06550: linha 17, coluna 3:
PLS-00103: Encontrado o símbolo "end-of-file" quando um dos seguintes símbolos era esperado:

   ; <um identificador>
   <um identificador delimitado por aspas duplas>
O símbolo ";" foi substituído por "end-of-file" para continuar.

What you think should happen instead

I think stripping ; from statement is causing this error

How to reproduce

No response

Anything else

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[potiuk]

cc: @kazanzhy - > will you take a look at that one or should I attempt to fix it ? I think having optional "strip_semicolon" and setting it to False by default in Oracle, should solve the issue.

[kazanzhy]

I also thought about the ; stripping as the cause of this error.
@hewerthomn does your original query has the trailing ;?

[hewerthomn]

Yes @kazanzhy , every statement has a semicolon at the end, and after END too

[kazanzhy]

It will be great if you have a look at #25855 and help me to test it.
I worked with a lot of databases, but unfortunately, Oracle isn't one of them.

[hewerthomn]

@kazanzhy I want to test it, but the last version of common-sql is 1.1.0

how can I test this update?

[potiuk]

how can I test this update?

@hewerthomn

You can BYOP (build your own provider):

1. checkout main
2. Install breeze pipx install -e ./dev/breeze
3. breeze release-management prepare-provider-packages --version-suffix-for-pypi dev0 common.sql

The .whl package should be prepared in your ./dist folder and you should be able to install it with:

pip install ./dist/WHEEL_FILE_NAME.whl

[potiuk]

(you need to have pipx installed first BTW)

[hewerthomn]

@potiuk I builded the package and installed in my container with airflow, it worked!

[2022-08-23, 09:59:17 -04] {oracle.py:66} INFO - Executing: DECLARE
    v_sql LONG;
BEGIN
    v_sql := '
create table usr_bi_cgj.dim_tarefa
(
    id_tarefa   NUMBER(22) not null primary key,
    ds_tarefa   VARCHAR2(4000) not NULL
);
';
    EXECUTE IMMEDIATE v_sql;
    COMMIT;
    EXCEPTION
        WHEN OTHERS
        THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE usr_bi_cgj.dim_tarefa';
    COMMIT;
END;
[2022-08-23, 09:59:17 -04] {base.py:68} INFO - Using connection ID 'bitjro' for task execution.
[2022-08-23, 09:59:18 -04] {sql.py:313} INFO - Running statement: DECLARE
    v_sql LONG;
BEGIN
    v_sql := '
create table usr_bi_cgj.dim_tarefa
(
    id_tarefa   NUMBER(22) not null primary key,
    ds_tarefa   VARCHAR2(4000) not NULL
);
';
    EXECUTE IMMEDIATE v_sql;
    COMMIT;
    EXCEPTION
        WHEN OTHERS
        THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE usr_bi_cgj.dim_tarefa';
    COMMIT;
END;, parameters: None
[2022-08-23, 09:59:18 -04] {sql.py:322} INFO - Rows affected: 0
[2022-08-23, 09:59:18 -04] {taskinstance.py:1420} INFO - Marking task as SUCCESS. dag_id=caixa_tarefa_pje, task_id=cria_temp_dim_tarefa, execution_date=20220822T090000, start_date=20220823T135917, end_date=20220823T135918
[2022-08-23, 09:59:18 -04] {local_task_job.py:156} INFO - Task exited with return code 0

😄

[potiuk]

Fantastic! thanks for confirmation! It's already merged and will go out in the next wave of providers :). Thanks @kazanzhy also for very quickl fixing ot that one :)

[hewerthomn]

Thanks!!! @kazanzhy @potiuk