Question


Pegasystems Inc.
JP
Last activity: 5 Feb 2019 5:40 EST
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,


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


Pegasystems Inc.
JP
Anyone has any idea?
This should have worked. You do not need the extra curly braces around the call.
call SAMPLE({OperatorID.TestID}, {OperatorID.TestName})


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:
Accepted Solution
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


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,


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


Crochet Technologies
IN
Thank you


Tech Mahindra
IN
here u are not give schema name
Hello,
Here is the format to call stored PROC
With Properties
Hardcoded