Calling oracle Stored Procedure with xml
Team,
I am calling a stored procedure (oracle) which accepts 1 input (XML Type) and returns 1 (XML Type) output and error flag (INTEGER).
I am using a Connect-SQL (browse) tab to call the stored procedure.
When I run the activity which calls RDB-List, it is returning the below error:
Syntax : CALL PPTS.PM_PROCDOC_UPLDI_XML({TVM.input in},{TVM.ouput out},{TVM.Errorlable out})
HERE : PPTS is schema name and PM_DOC_UPLDI_XML is procedure.
Team,
I am calling a stored procedure (oracle) which accepts 1 input (XML Type) and returns 1 (XML Type) output and error flag (INTEGER).
I am using a Connect-SQL (browse) tab to call the stored procedure.
When I run the activity which calls RDB-List, it is returning the below error:
Syntax : CALL PPTS.PM_PROCDOC_UPLDI_XML({TVM.input in},{TVM.ouput out},{TVM.Errorlable out})
HERE : PPTS is schema name and PM_DOC_UPLDI_XML is procedure.
Error : 
Kindly help us to resolve this issue. Please share if there is any doc to call stored procedure with XML input and out.
Pegasystems Inc.
IN
Cognizant Technology Solutions
IN
HCL Technologies
IN
Hi Thavamani,
I'm also getting same error so have you found any solution for this issue please?
TCS
IN
HI,
I'm able to send the XML type parameter successfully and used it in SP.
please find the below syntax I have followed.
{SQLPage:yErrorPage}
\{call Sample_SP_Name('{Asis:.pro1}','{Asis:pro2}',XMLType({.pyTempText}))\}
my SP defination looks Like: Sample_SP_Name(Pro1 IN VARCHAR2,Pro2 IN VARCHAR2,Sample_XML IN XMLTYPE )
Thank you.
The standard JDBC syntax looks like the following:
{ call procedure-name ( parameter1 parameter2 parameter3 ... ) }
If you are using this syntax with Rule-Connect-SQL, it is important to escape the curly braces (using the backslash character), so that PegaRULES does not interpret them as part of one of its own directives. So your call would look like the following:
\{ call procedure-name (parameter1 parameter2 parameter3 ... ) \}
Note that in a stored procedure call, there is not usually a place to put a Class directive. It is therefore important that the Rule-Connect-SQL be defined on a class that is mapped to the database to which the query should be submitted.
Specifying Parameters:
If you had a stored procedure named my_proc that took no parameters, a call to this procedure in Rule-Connect-SQL would look as follows:
The standard JDBC syntax looks like the following:
{ call procedure-name ( parameter1 parameter2 parameter3 ... ) }
If you are using this syntax with Rule-Connect-SQL, it is important to escape the curly braces (using the backslash character), so that PegaRULES does not interpret them as part of one of its own directives. So your call would look like the following:
\{ call procedure-name (parameter1 parameter2 parameter3 ... ) \}
Note that in a stored procedure call, there is not usually a place to put a Class directive. It is therefore important that the Rule-Connect-SQL be defined on a class that is mapped to the database to which the query should be submitted.
Specifying Parameters:
If you had a stored procedure named my_proc that took no parameters, a call to this procedure in Rule-Connect-SQL would look as follows:
\{ call my_proc \}
If your stored procedure takes input parameters, and the value of those parameters will come from a clipboard property, then you may use standard PegaRULES RDB property directives for the values of the stored procedure parameters. For example, assume your stored procedure takes one string-type input parameter, and assume that the value of that parameter should come from the MyProperty property defined on MyPage. Your call might look like the following:
\{ call my_proc ( {MyPage.MyProperty} ) \}
I believe (I have seen in one of the Pega forum) that one can use UDB and DB2 stored procedures with rule-connect-sql.