Question


Veteran Careers First
US
Last activity: 19 Apr 2018 12:56 EDT
Oracle Stored procedure which will return a value back to PEGA
I am attempting to call a stored procedure from Oracle which will return a value back to PEGA. I continue to get various errors on the tracer. Is there a tutorial or some solid information with “clear” examples…
I have already reviewed to following:
https://community.pega.com/sites/default/files/help_v719/procomhelpmain.htm
https://docs.pega.com/calling-stored-procedure-returns-resultset-oracle
https://docs.pega.com/how-process-result-set-returned-oracle-stored-procedures
-
Does a function within PEGA need to be create first…
-
If so where and how.


OCBC Bank
SG
Hi,
Please refer to below links on how to call a stored procedure using Connect-SQL
https://collaborate.pega.com/discussion/it-possible-call-stored-procedures-pega
https://pdn.pega.com/node/8755/book_pdf
https://community.pega.com/support/support-articles/error-while-calling-sql-function-rdb-list
In case if you are still facing issues, please share the error you are getting in tracer.


Veteran Careers First
US
Just to confirm, does a PEGA function (not stored procedure) needed to be created first....
Also, the second link does not open.


PEG
GB
I'm not entirely sure what you mean by Pega function. I don't believe a Rule-Utility-Function would be needed though, if this is what you meant.
For that second link, I'm not too sure what happened as I can see the colon in the text, but hovering over the link the tooltip doesn't show it. So you may just want to re-add it into your browser, after clicking on the link.


Veteran Careers First
US


Veteran Careers First
US
In step 1, this link says wrap Oracle stored procedure in a function: https://docs.pega.com/calling-stored-procedure-returns-resultset-oracle
In step 1, this link creates a stored function: https://docs.pega.com/how-process-result-set-returned-oracle-stored-procedures
The resolution in this link for step 1, creates the SQL Function: https://community.pega.com/support/support-articles/error-while-calling-sql-function-rdb-list


LTI (L&T Infotech)
CA
Hi Sandeep,
I don't think that you need any explicit function to just call a store proc. You can consider below example to call store proc which sends and receives data from store proc. You can call store from Browse tab of Connect SQL rule.
\{ call purge_log_table ({purgePg.purgeDays }, {purgePg.status OUT}) \}
I hope this will help.


Veteran Careers First
US
Would this syntax be different if it is an Oracle store procedure....


LTI (L&T Infotech)
CA
Yes this is Oracle Store proc syntax.


Veteran Careers First
US
In step 1, this link says wrap Oracle stored procedure in a function: https://docs.pega.com/calling-stored-procedure-returns-resultset-oracle
In step 1, this link creates a stored function: https://docs.pega.com/how-process-result-set-returned-oracle-stored-procedures
The resolution in this link for step 1, creates the SQL Function: https://community.pega.com/support/support-articles/error-while-calling-sql-function-rdb-list