potiuk commented on PR #23971:
URL: https://github.com/apache/airflow/pull/23971#issuecomment-1221515787
> I also think we adressed this already?
> I'm not near my laptop but as far as I remember we added a
`split_statment` flag so:
> `OracleOperator(..., split_statment=False)`
> Should work in this case.
Yeah. I am not talking about "split_statement". I know it's hard to split
such PL/SQL, but I believe the problem is it does not work also when we set
split_statement = False.
I think the problem is that we currently ALWAYS remove the closing `;` from
each statement and this is the root of the problem as apparently Oracle does
not really like it.
```
if split_statements:
sql = self.split_sql_string(sql)
else:
sql = [self.strip_sql_string(sql)]
```
strip_sql_string:
```
@staticmethod
def strip_sql_string(sql: str) -> str:
return sql.strip().rstrip(';')
```
The result is that If you pass "run" method a statement with ";" at the end
it will be converted into one-element array of statements and the `;` will be
removed in that one element. I think this is the root cause of the problem.
Now the question is @kazanzhy - why do we remove that `;` . Is there a
reason that other DBs are complaining if we do not remove it? Unfortunately
DBAPI PEP https://peps.python.org/pep-0249/#cursor-methods is silent about
whether the semicolon should be there or not. and while in SQL it is generally
a separator between multiple statement whether it is merely separator (not
needed in the statement) or terminator (needed in the statement) is not only
debatable, but also changes over time. For example whil it was allowed to treat
semicolon as just a separator or whether it is a mandatory terminator has
changed in SQLServer -
https://wp.larnu.uk/fundamentals-the-semicolon-is-a-statement-terminator/ . It
was possible to run a statement in sqlserver without semicolon, but this was
deprecated.
So I think we should have flexibility on whether to remove the semicolon or
not.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
