Code:
def add_to_upload_file_table(connection, **data):
query = """
INSERT INTO FILE_UPLOAD (
FILENAME, FILE_MIMETYPE, FILE_CHARSET, FILE_BLOB,
FILE_COMMENTS, CREATED_ON, CREATED_BY, CUSTOMER_NAME,
OPP_NAME
)
VALUES (
:FILENAME, :FILE_MIMETYPE, :FILE_CHARSET, :FILE_BLOB,
:FILE_COMMENTS, :CREATED_ON, :CREATED_BY, :CUSTOMER_NAME,
:OPPORTUNITY_NAME
)
"""
data["CREATED_ON"] = datetime.now()
data["CREATED_BY"] = "Python Automated Script"
with connection.cursor() as cursor:
cursor.execute(query, data)
connection.commit()
The trigger:
create or replace TRIGGER "FILE_UPLOAD_RUN_IMPORT" after
INSERT ON file_upload
FOR EACH ROW
BEGIN
sizing_upload.process_uploads;
END;
/
Procedure:
`PROCEDURE process_uploads AS`
-- Go through uploads table to figure which haven't been processed yet
BEGIN
dbms_output.put_line('Starting sizing_upload.process_uploads...');
FOR upload_rec IN (
SELECT file_upload.*
FROM file_upload
WHERE (status = 'IMPORTED'
OR status = 'FILE_UPLOADED'
OR status IS NULL)
AND customer_name != 'SUPKGTEST'
)
LOOP
process_upload_id(upload_rec.file_id);
END LOOP;
END;
Error:
oracledb.exceptions.DatabaseError:
ORA-04091: table SIZING5.FILE_UPLOAD is mutating, trigger/function may not see it ORA-06512: at "SIZING5.SIZING_UPLOAD", line 26
ORA-06512: at "SIZING5.SIZING_UPLOAD", line 26
ORA-06512: at "SIZING5.FILE_UPLOAD_RUN_IMPORT", line 2
ORA-04088: error during execution of trigger 'SIZING5.FILE_UPLOAD_RUN_IMPORT'
I'm uploading an blob_file into a table, and this table has a trigger onit that makes things.
But I just tried multiple things like taking out the commit , and just hit and try errors. I also put the status column into imported, or just into null but all the errors are the same
with connection.cursor() as cursor:
cursor.execute(query, data)
connection.commit()