We have a business need to store Excel/Pdf files as BLOB in an oracle database a

padmanabhanv over 11 years ago

We have a business need to store Excel/Pdf files as BLOB in an oracle database and the ability to search and retrieve the same from Appian later on. Tried mapping the document to a base64 encoded string and then storing to the db, but when the doc size is large NULL gets stored. Unable to achieve this by using the query rule- the error does not make sense, but I guess it is because of the BLOB data type.
Is there a straightforward plugin or function to achieve this or should we resort to writing a plugin? Please advise....

Discussion posts and replies are publicly visible

0 Robert Shankin
Appian Employee
over 11 years ago

What about your requirement necessitates this operation? Why not store the Excel and PDF files in Appian?

As the size of the base64 string increases, you're going to reach a point where it's too large for Oracle to insert into the blob in a single statement. The string would to be "chunked" apart and appended into the blob using a stored procedure.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 padmanabhanv over 11 years ago

Rob, The document does get stored in Appian too, but it is business critical to update the same as a BLOB to an oracle db, the rationale being that there are other systems leveraging the same information downstream and they don't want to change that part of the file storage mechanism for now.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sathya Srinivasan
Appian Employee
over 11 years ago

In general, having data replicated independently across multiple systems is a bad practice. You are no longer seeing a single-point of truth. This can have adverse effect rather than benefit. Adding to this, as Rob highlights, this architecture can adversely affect the performance of a production system. My recommendation is to discuss with client to find an alternate way of accessing this info from an external system and maintain a single source of truth (be it in Appian or in external system).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 padmanabhanv over 11 years ago

Sathya, point taken - but assuming that business still needs the Appian workflow to push the document to a oracle db, should we go ahead and write a plugin?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sathya Srinivasan
Appian Employee
over 11 years ago

Yes, that is one option. But my recommendation stands. You may end up providing a solution to what the client wants but this may lead to scalability challenges in the future due to poor architecture.
For me, the big question is, what is it that the client sees they'd achieve by performing this action? Architecturally, you should address their concern by keeping a single source of truth.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 padmanabhanv over 11 years ago

On having a single source of truth - business wants it to be the file stored in oracle via Appian; reason being that there are other end systems which would look up the file from the same database for reporting/reference.
We may end up convincing business to store the doc in Appian alone, but wanted to rule out technical complications on achieving the same and hence the question. Thanks for the guidance. Appreciate it
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel