id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29170	Unable to add triggers in migrations on Oracle.	Danny Willems	nobody	"We have several database triggers we need to insert into our Oracle database using a custom migration. The migration runs without raising an error and the triggers are created in the database, however, when they are invoked as a result of various operations in the Django admin we see an error that says that the trigger cannot be compiled. After investigation we realised that a required semicolon was being removed from the SQL defined in the migration. Ordinarily this is removed from standard SQL statements such as SELECT, INSERT etc but in the case of triggers it is required as a way to delimit multiple BEGIN...END statements.

After debugging the issue we found the cause in this line of code:

https://github.com/django/django/blob/master/django/db/backends/oracle/base.py#L481

It appears that the blanket assumption that cx_Oracle does not require semicolons does not hold for triggers. 

Here is a simplified migration that shows the issue:


{{{
from django.db import migrations

class Migration(migrations.Migration):
    dependencies = []

    create_trigger_insert_entry = """"""
    CREATE OR REPLACE TRIGGER UPDATE_BALANCE
    AFTER DELETE OR INSERT OR UPDATE OF AMOUNT ON ENTRY
    BEGIN
      UPDATE ACCOUNT_BALANCE B
      SET (B.BALANCE, B.ACCOUNT) = (SELECT SUM(AMOUNT) sum_amount, account
                              FROM ENTRY e WHERE e.ACCOUNT = B.ACCOUNT GROUP BY ACCOUNT)
      WHERE EXISTS (SELECT 1 FROM ENTRY e WHERE e.ACCOUNT = B.ACCOUNT);
    END;
    """"""

    delete_trigger_insert_entry = ""DROP TRIGGER UPDATE_BALANCE""

    operations = [
        migrations.CreateModel(
            name='Account',
            fields=[
                ('name', models.CharField(max_length=32, primary_key=True, serialize=False)),
            ],
            options={
                'db_table': 'account',
                'managed': True,
            },
        ),

        migrations.CreateModel(
            name='Entry',
            fields=[
                ('value_date', models.DateTimeField()),
                ('amount', models.DecimalField(decimal_places=8, max_digits=23)),
            ],
            options={
                'db_table': 'entry',
                'managed': True,
            },
        ),
        migrations.CreateModel(
            name='AccountBalance',
            fields=[
                ('account', models.OneToOneField(db_column='account', on_delete=django.db.models.deletion.DO_NOTHING, primary_key=True, serialize=False, to='app.Account')),
                ('balance', models.DecimalField(decimal_places=14, max_digits=38)),
            ],
            options={
                'db_table': 'account_balance',
                'managed': True,
            },
        ),
        migrations.AddField(
            model_name='entry',
            name='account',
            field=models.ForeignKey(db_column='account', on_delete=django.db.models.deletion.CASCADE, to='app.Account'),
        ),
        migrations.RunSQL(sql=create_trigger_insert_entry, reverse_sql=delete_trigger_insert_entry),
    ]
}}}

As a workaround, we « fixed » this issue by overriding the method `_fix_for_params` with the following code:
{{{
def _fix_for_params(self, query, params, unify_by_values=False):
    # cx_Oracle wants no trailing ';' for SQL statements.  For PL/SQL, it
    # it does want a trailing ';' but not a trailing '/'.  However, these
    # characters must be included in the original query in case the query
    # is being passed to SQL*Plus.
    # ---> Fix this issue 
    if query.endswith("" END;""):
        pass
    elif query.endswith(';') or query.endswith('/'):
        query = query[:-1]
    if params is None:
        params = []
        query = query
    [...]
}}}
and we used
{{{
django.db.backends.oracle.base.FormatStylePlaceholderCursor._fix_for_params = _fix_for_params
}}}
in the migration file as it doesn't impact all the Django project. We would be happy to raise a pull request to get this fixed and obviously if anyone has a better way of doing this, we'd gladly oblige."	Bug	new	Database layer (models, ORM)	3.0	Normal		oracle trigger database	Mariusz Felisiak	Accepted	0	0	0	0	0	0