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,
Infosys
AU
Hello,
Here is the format to call stored PROC
With Properties
CALL SAMPLE (
{.PropertyRef1},
{.PropertyRef2}
)
Hardcoded
CALL SAMPLE (
'001',
'James Bond'
)
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?
Infosys
AU
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
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
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