Collaboration Center

Question

Replies

4928

Views

KenshoTsuchihashi

Member since 2010

132 posts

PEGA

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

Tarun Bolla

Infosys

replied to KenshoTsuchihashi

Hello,

Here is the format to call stored PROC

With Properties

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

Hardcoded

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

KenshoTsuchihashi

PEGA

replied to Tarun Bolla

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

KenshoTsuchihashi

PEGA

replied to KenshoTsuchihashi

Anyone has any idea?

Tarun Bolla

Infosys

replied to KenshoTsuchihashi

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

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

KenshoTsuchihashi

PEGA

replied to Tarun Bolla

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

Tarun Bolla

Infosys

replied to KenshoTsuchihashi

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

KenshoTsuchihashi

PEGA

replied to Tarun Bolla

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,