Problem solve
Get help with specific problems with your technologies, process and projects.
Storing documents in BLOBs vs. external FTP server
We were "sold" by Oracle consultants on the advantages of storing all our documents into BLOBs in the database instead of the external FTP server method we were using. We now find that you cannot trap an update to a BLOB field in a trigger.
We were "sold" by Oracle sales and technical consultants on the advantages of storing all our documents into BLOBs in the database instead of the external FTP server method we were using. We just converted 500,000 documents. We now find that you cannot trap an update to a BLOB field in a trigger. Anyone with just read access can select it into a local blob variable and change the contents of the blob and we would never know. These are legal documents and we can't audit the changes from the database side. Do you know any workaround for this?
The only way I know of how to do this is to create the table to hold the BLOBs in a new schema. Your application users do not have privileges to do anything with this table. Then create a stored procedure in this new schema which can update the BLOB contents. This stored procedure will also log an audit entry into an audit trail table so that you know who invoked the procedure and when. Grant EXECUTE on this stored procedure to your users. They only way they can update the BLOB is through the stored proc which also enforces your auditing requirements.
Start the conversation
0 comments