Question

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 ?

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Correct Answer
November 20, 2015 - 10:29am

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.

Comments

Keep up to date on this post and subscribe to comments

Pega
November 20, 2015 - 10:08am

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://pdn.pega.com/support-articles/how-to-install-user-defined-functions-udfs

Thanks

John

November 20, 2015 - 10:33am
Response to JOHNPW_GCS

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.

November 20, 2015 - 1:39pm
Response to JOHNPW_GCS

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.

Pega
November 20, 2015 - 10:29am

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.