Read BLOB pzpvstream from database (pr_read_from_stream vs. DBMS_LOB.SUBSTR)
Hi,
I need to analyze the contents of some BLOB values on production (inconsistent SLA queue items). We do not have time to prepare a new patch and to move activity with obj-open-by-handle in it. I tried to prepare scripts to check them:
This sample works on dev PRPC 7.1.7.:
select pr_read_from_stream('.pxObjClass',pzInsKey, pzPVStream) from pc_work where rownum <3
1 PegaSample-Task
2 PegaSample-Task
For PRPC 6.3. SP1 the mentioned functioned and java class which it is calling is not defined. I tried the following:
select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(pzpvstream, 2000,1)) test from pc_work where rownum<3
1 ZlB7??Bix?͛ xUյ��� ...
2 ZlB7??C{x?͛ x�� ...
Do you know a way to decrpypt a BLOB on Pega 6.3.SP1 using PL/SQL?
We are using Oracle 11.2
Ivan
**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.
Benson Boys
US
You write a pl/sql function to disassemble the BLOB into 4K varchar2 chunks which you can shove into a CLOB datatype which can then be searched. It always happens that BLOB needs searched as soon as something breaks. Why not use CLOB in the first place ? Conversely you could leave it in 4K varchar2 chunks and search chunk by chunk.
ralph
Pegasystems Inc.
US
you can search all you want but keep in mind that 'the blob' is neither XML nor a fixed format data structure with fields at same offset / position in each record. The blob is a compressed dump of an in memory sparse structure.
Benson Boys
US
Thank you for your comment. That is why you convert it where possible. To search it. You always need to search it when something breaks or goes wonky with the app.
IAG
AU
All extractor functions (pr_read_int_from_stream,pr_read_decimal_from_stream,pr_read_from_stream) work fine when extracting the top level class properties. What is the way (syntax) for calling these functions when the child class property needs to be extracted? e.g. top level class pagedata has child class Premium.
Updated: 27 Jun 2016 11:22 EDT
Areteans Technology Solutions
AU
Any update on this? Would be great if someone can answer this.
How to open the pages / pagelist from a blob thrugh a RDB query. If not please provide a way where the binary data in the pzPVStream can be manipulated to get the actual xml data
LTI
IN
Hi Ratan@22,
Use RDB-Open and have some step page defined in Activity. Later using that step page you can call Call Show-Harness and create a Harness with your intended properties. Run Activity. It will show you data you are looking for.
Thanks,
Rajnish
Accenture Australia Ltd
AU
Identify the property name you want to query and add it like "pr_read_from_stream('.PropName',pzInsKey,pzPVStream) " in the select query.
i.e select pr_read_from_stream('.PropName',pzInsKey,pzPVStream) from table_name
JPMorgan Chase
US
SQL Error: ORA-29548: Java system class reported: could not identify release specified in classes.bin
29548. 00000 - "Java system class reported: %s"
*Cause: A command that uses a Java system class was aborted due to an
error reported by the Java system class.
*Action: Correct the error that was reported.
how to correctly install this SP? I got the following error when I tried to run a query for rpt purpose for mgr: