Question

10
Replies
5393
Views
 Kensho Tsuchihashi (KenshoTsuchihashi)
PEGA
Project Delivery Leader
Pegasystems Inc.
JP
KenshoTsuchihashi Member since 2010 141 posts
PEGA
Posted: June 7, 2018
Last activity: February 5, 2019
Posted: 7 Jun 2018 11:43 EDT
Last activity: 5 Feb 2019 5:40 EST
Closed
Solved

How to call stored procedure

Hi,

I am trying to call Oracle's stored procedure from Pega but I get an error. Could somebody please tell me what is wrong with my code? Please see attached.

Thanks,

Low-Code App Development Data Integration Java and Activities
Moderation Team has archived post, This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.
Posted: 3 years ago
Posted: 11 Jun 2018 0:08 EDT
Tarun Kumar Reddy Bolla (Tarun Bolla)
Infosys

Infosys
AU

Hello,


Here is the format to call stored PROC


With Properties



CALL SAMPLE (
 {.PropertyRef1},
 {.PropertyRef2}
)


Hardcoded



CALL SAMPLE (
 '001',
 'James Bond'
)


 
Posted: 3 years ago
Posted: 11 Jun 2018 11:28 EDT
Kensho Tsuchihashi (KenshoTsuchihashi)
PEGA
Project Delivery Leader
Pegasystems Inc.
JP

Hi,


I tried many syntax but none of them worked. I tried also \{call SAMPLE({OperatorID.TestID}, {OperatorID.TestName}) \} where both properties have actual Text values but the result was the same. In the PegaRULES log file, below ORA-06550 error is seen:


2018-06-12 00:22:43,068 [http-nio-8084-exec-9] [  STANDARD] [                    ] [      MyApp:01.01.01] (tomer.MyCo_Int_Customer.Action) ERROR localhost|0:0:0:0:0:0:0:1 kensho.tsuchihashi - Error in RDB-Save

com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 6550 SQLState: 65000 Message: ORA-06550: 行1、列7:

PLS-00201: 識別子SAMPLEを宣言してください。

ORA-06550: 行1、列7:

PL/SQL: Statement ignored


DatabaseException caused by prior exception: java.sql.SQLException: ORA-06550: 行1、列7:

PLS-00201: 識別子SAMPLEを宣言してください。

ORA-06550: 行1、列7:

PL/SQL: Statement ignored


 | SQL Code: 6550 | SQL State: 65000
Posted: 3 years ago
Posted: 14 Jun 2018 10:42 EDT
Kensho Tsuchihashi (KenshoTsuchihashi)
PEGA
Project Delivery Leader
Pegasystems Inc.
JP

When I get rid of those, I get "ORA-06576 not a valid function of procedure name". My understanding is that syntax is required if you look at the help:


https://community.pega.com/sites/default/files/help_v74/procomhelpmain.htm#rule-/rule-connect-/rule-connect-sql/sqlguidelineshtm.htm#Connect_SQL_form___Data_Mapping


 

Accepted Solution

Posted: 3 years ago
Posted: 14 Jun 2018 22:09 EDT
Tarun Kumar Reddy Bolla (Tarun Bolla)
Infosys

Infosys
AU

Those angular braces are for sure not "required". They might be an optional syntax or syntax for some other db engine. I see you are using Oracle. Did you try including schema name in there? For example if my proc is in schema "sch1". I would call it as sch1.sample


 
Posted: 3 years ago
Posted: 16 Jun 2018 1:28 EDT
Kensho Tsuchihashi (KenshoTsuchihashi)
PEGA
Project Delivery Leader
Pegasystems Inc.
JP

Thanks Tarun. You are right, I had to prepend schema name to stored procedure name. The syntax \{\} is not required (worked both with and without it).


1. \{call SAMPLE({OperatorID.TestID}, {OperatorID.TestName}) \} => Fail

2. call SAMPLE({OperatorID.TestID}, {OperatorID.TestName}) => Fail

3. \{call DATA.SAMPLE({OperatorID.TestID}, {OperatorID.TestName}) \} => Success

4. call DATA.SAMPLE({OperatorID.TestID}, {OperatorID.TestName}) => Success


Thanks,