Is it possible to read a BLOB returned from an Oracle Stored Procedure via a Connect SQL Rule ?
I want to retrieve some content from a legacy system which has an Oracle PL / SQL Interface as follows
procedure document_search_by_id(
doc_id_in IN char,
doc_found_out OUT NUMBER,
doc_content_out OUT BLOB
);
I've tried invoking this using this in the Connect SQL rule that is called by an RDB-List step in an activity:
\{call document_search_by_id(
{MyDoc.DocumentId},
{MyDoc.DocumentFound out},
{MyDoc.Content out })
\}
MyDoc is a step page with DocumentId as Text, DocumentFound as Integer, and Content as text, however I get the following error:
There was a problem performing a database query: There was a problem getting a list: code: 6550 SQLState: 65000 Message: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DOCUMENT_SEARCH_BY_ID' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
I know that the DocumentId and DocumentFound types are correct by removing the Content parameter from the interface, but I can't find a type for Content that seems to match BLOB and removes this error.
Has any implemented retrieving a BLOB from a Stored Procedure in Pega 7 ?
Accepted Solution
Pegasystems
IN
There is no support for BLOB column type (outside of BLOB used by Pega itself to for its own internal table to store the contents of the entire clipboard page) in Pega platform.
Pegasystems Inc.
GB
Hi Paul,
Do the BLOBs contain Pega Data - or do you just mean BLOBs in general ?
If these are Pega BLOBs - I wonder if you could use 'User Defined Functions' (UDFs) to extract specific Pega Property Data from the BLOB at the DB level directly ?
Here's a list of the UDFs shipped with PRPC6.x (6.3 ?might be earlier - I can't remember of the top of my head) by default:
pr_read_from_stream pr_read_decimal_from_stream pr_read_int_from_stream
See : https://community.pega.com/support/support-articles/how-install-user-defined-functions-udfs
Thanks
John
Xchanging
GB
Hi John,
Thanks for the suggestion.
Sadly these are non Pega BLOBs, they are essentially document files saved to the database and are of various types (MS Word, PDF, TIFF) also they can vary in size running into multiple MB.
GovCIO
US
Looks like these tables are not present in PRPC V62 SP2. I did just check it. Do we have equivalent tables for them in V62 or this whole feature was introduced from V63 on?
Thanks,
Ravi Kumar.
Accepted Solution
Pegasystems
IN
There is no support for BLOB column type (outside of BLOB used by Pega itself to for its own internal table to store the contents of the entire clipboard page) in Pega platform.