Question

10
Replies
4618
Views
TSUCK Member since 2010 125 posts
PEGA
Posted: 2 years ago
Last activity: 1 year 8 months ago
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 Data Integration
Close popover
Moderation Team has archived post
Posted: 2 years ago

Hello,


Here is the format to call stored PROC


With Properties



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


Hardcoded



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


 

Posted: 2 years ago

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: 2 years ago

Anyone has any idea?

Posted: 2 years ago

This should have worked. You do not need the extra curly braces around the call.


call SAMPLE({OperatorID.TestID}, {OperatorID.TestName})

Accepted Solution
Posted: 2 years ago

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: 2 years ago

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,

Posted: 1 year ago

Attached document is clear to understand the Stored Procedure invocation. Thanks.

Posted: 1 year ago

here u are not give schema name 
Share this page LinkedIn
Copied!