Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

UCCX 9.0(2) Stored Procedure not working in CCX Editor

Dears,

I am not getting any result while calling a stored procedure in DB READ in CCX Editor.

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit.

Using SQL PLUS, I was able to call the stored procedure and to get results. (find attached file)

Can someone guide me about the syntax required for calling a stored procedure ?

Here is the Call Procedure defined in Oracle Database:

Create or replace PROCEDURE GetLoyaltyPoint

(

@MEM_NUM VARCHAR,

@CELL_PH_NUM VARCHAR,

@Mobile_Phone_Number VARCHAR,

@POINT_TYPE_A_VAL  NUMBER(22,7) OUT,

@ERR_STATUS number out

)

AS

@POINT_TYPE_A_VAL := 0;

@ERR_STATUS:=0

BEGIN

SELECT POINT_TYPE_A_VAL INTO @POINT_TYPE_A_VAL

FROM SIEBEL.S_LOY_MEMBER LY,

SIEBEL.S_CONTACT CON

WHERE  

CON.ROW_ID = LY.PR_CON_ID

AND (CON.CELL_PH_NUM = LY.X_NMCR1_CELL_NUM AND CON.CELL_PH_NUM = @CELL_PH_NUM)

OR (CON.WORK_PH_NUM = LY.PIN_NUM AND CON.WORK_PH_NUM = @Mobile_Phone_Number))

AND LY.MEM_NUM = @MEM_NUM;

@ERR_STATUS := 0;

EXCEPTION

   WHEN OTHERS THEN

       @ERR_STATUS := 1;

END

/

So in this example the Stored Procedure name is GETLOYALTYPOINT with 2 inputs (MEM_NUM,CELL_PH_NUM) and 2 outputs (POINT_TYPE_A_VAL,ERR_STATUS).

I have to enter as input the loytalty number and mobile number and get the number of points.

I tried in CCX Editor DB READ the following SQL Query:

exec GetLoyaltyPoint 1-3163053, +966561403803

I got 0 results !

Thanks.

Best Regards,

Mofa

234
Views
0
Helpful
0
Replies
CreatePlease login to create content