This discussion is archived
10 Replies Latest reply: Jan 15, 2011 3:03 PM by 724818 RSS

Running stored procedures from IVR

724818 Newbie
Currently Being Moderated
Hello,

I have been trying to execute a stored procedure from the IVR but I keep getting a FAIL status no matter how I write the code on the IVR action node. I wrote my code following the instructions from another post here in the forum but it still returns only FAIL. I am using CCA 8.1.3 FP5 with an Oracle DB 10g.

-The IVR code:

EXEC CCA_SAT ?ASSET_NUM, $SR_NUM

-The sproc:

create or replace
PROCEDURE CCA_SAT (ASSET_NUM in number, SR_NUM out number) IS

BEGIN
SR_NUM := ASSET_NUM * 2;
END;


Anyone has any idea of what might be wrong? Connection with the DB is fine since I can run simple selects and play the results. Any help is appreciated.

Thanks in advance,

Davi Elias
  • 1. Re: Running stored procedures from IVR
    rschepis Explorer
    Currently Being Moderated
    The directions in the thread below:

    CCA Stored Procedure

    Will work... If you get any errors, pls set your call centers to trace level 5.

    Then run the proc from IVR.

    Post the log here if you need help reading the error.

    Search for the Proc name in the log.
  • 2. Re: Running stored procedures from IVR
    724818 Newbie
    Currently Being Moderated
    Hello rschepis,

    I did read the topic you posted before asking here. But I still get a FAIL answer after using the db query ivr node. Here is the callcenter log:


    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : CIVRCampaign::GetCommonAction[nodeid=139 - position=0  - type=5]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CCFRunIVRCampaign::RunCurrentAction[position=0  - type=5]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: [(ACTION_NODE_TYPE_SET_VARIABLES) variable=$ASSET_NUM - value=123456789]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: [(ACTION_NODE_TYPE_SET_VARIABLES) variable=$ASSET_NUM - value=123456789]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : CIVRCampaign::GetNextNodeAction[nodeid=139 - following position=0]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : CIVRCampaign::GetCommonAction[nodeid=139 - position=1  - type=5]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CCFRunIVRCampaign::RunCurrentAction[position=1  - type=5]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: [(ACTION_NODE_TYPE_SET_VARIABLES) variable=$status_ws - value=OK]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: [(ACTION_NODE_TYPE_SET_VARIABLES) variable=$status_ws - value=0]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : CIVRCampaign::GetNextNodeAction[nodeid=139 - following position=1]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : CIVRCampaign::GetCommonAction[nodeid=139 - position=2  - type=5]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CCFRunIVRCampaign::RunCurrentAction[position=2  - type=5]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: [(ACTION_NODE_TYPE_SET_VARIABLES) variable=$SR_NUM - value=3]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: [(ACTION_NODE_TYPE_SET_VARIABLES) variable=$SR_NUM - value=3]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : CIVRCampaign::GetNextNodeAction[nodeid=139 - following position=2]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : CIVRCampaign::GetCommonAction[nodeid=139 - position=3  - type=4]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CCFRunIVRCampaign::RunCurrentAction[position=3  - type=4]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: [(ACTION_NODE_TYPE_DATABASE_QUERY) timeout=30 - reties=0 - dblib=183 - variable=$status_ws - nbrows=60 - nbrowsvariable=30]
    21/12/2010 11:39:41.297 : 1 : 1708 : 652 : CallCenter : 27 : # 5: Store Procedure = EXEC CCA_SAT(123456789);
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : InsertNewQueryMessage:
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : InsertNewQueryMessage: INSERT NEW MESSAGE FOR [0]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : CIVRCampaign::GetNextNodeAction[nodeid=139 - following position=3]
    21/12/2010 11:39:41.297 : 4 : 1388 : 652 : CallCenter : 27 : DBQueriesThread: Thread [0] Handle new messages
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : CIVRCampaign::GetCommonAction[nodeid=139 - position=4  - type=1]
    21/12/2010 11:39:41.297 : 0 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::HandleMessage() - DB_EXECUTE_IVRMENU_STORE_PROCEDURE
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CCFRunIVRCampaign::RunCurrentAction[position=4  - type=1]
    21/12/2010 11:39:41.297 : 0 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::ExecuteIVRCampaignQuery(): DataBase[libid = 183 - alias = DEVSIEBE - user = SIEBEL] interactionid[26999078210570]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: [(ACTION_NODE_TYPE_PLAY_VARIABLE) variable=$status_ws - type=1]
    21/12/2010 11:39:41.297 : 0 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::ExecuteIVRCampaignQuery(): Query[EXEC CCA_SAT(123456789);]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CCFRunIVRCampaign::IVRCPlayFile(5, ROMI, prompt\lg24\P.wav, 0)
    21/12/2010 11:39:41.297 : 4 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::GetIVRDBConnection(): Using DataBase Connection[DEVSIEBE_SIEBEL_?????]
    21/12/2010 11:39:41.297 : 4 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::ExecuteIVRStoreProcedure(): Execute Store Procedure[EXEC CCA_SAT(123456789);]
    21/12/2010 11:39:41.297 : 4 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::ExecuteIVRStoreProcedure(): Execute Store Procedure Failed
    21/12/2010 11:39:41.297 : 0 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::ExecuteIVRCampaignQuery(): Reconnecting...
    21/12/2010 11:39:41.297 : 4 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::Reconnect(): Connecting to DataBase[alias = DEVSIEBE - user = SIEBEL - password = ?????]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CLineVoip::PlayMessage() Playing message[C:\ccanywhere\prompt\lg24\P.wav] playstop[
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : CH[5]: startPlaying [C:\ccanywhere\prompt\lg24\P.wav]
    21/12/2010 11:39:41.297 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CLineVoip::PlayMessage() Playing message... Started
    21/12/2010 11:39:41.765 : 4 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::ExecuteIVRCampaignQuery(): -- After Reconnect
    21/12/2010 11:39:41.765 : 4 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::ExecuteIVRStoreProcedure(): Execute Store Procedure[EXEC CCA_SAT(123456789);]
    21/12/2010 11:39:41.781 : 4 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::ExecuteIVRStoreProcedure(): Execute Store Procedure Failed
    21/12/2010 11:39:41.781 : 0 : 1388 : 652 : CallCenter : 27 : CDBQueriesHandler::ExecuteIVRQuery(): Execute Request Failed
    21/12/2010 11:39:41.781 : 1 : 1708 : 652 : CallCenter : 27 : CCallCenter::HandleStatServerCMessage: received a message fron StatServer client[0] for line[5]
    21/12/2010 11:39:41.781 : 4 : 1708 : 652 : CallCenter : 27 : # 5: LineVariables [name = 30 - value = ]
    21/12/2010 11:39:41.781 : 4 : 1708 : 652 : CallCenter : 27 : # 5: LineVariables [name = ACD_PRIORITY - value = 3]
    21/12/2010 11:39:41.781 : 4 : 1708 : 652 : CallCenter : 27 : # 5: LineVariables [name = ANI - value = anonymous]
    21/12/2010 11:39:41.781 : 4 : 1708 : 652 : CallCenter : 27 : # 5: LineVariables [name = ASSET_NUM - value = 123456789]
    21/12/2010 11:39:41.781 : 4 : 1708 : 652 : CallCenter : 27 : # 5: LineVariables [name = DNIS - value = 9495]
    21/12/2010 11:39:41.781 : 4 : 1708 : 652 : CallCenter : 27 : # 5: LineVariables [name = INTDATE - value = 1292938780]
    21/12/2010 11:39:41.781 : 4 : 1708 : 652 : CallCenter : 27 : # 5: LineVariables [name = INTID - value = 26999078210570]
    21/12/2010 11:39:41.781 : 4 : 1708 : 652 : CallCenter : 27 : # 5: LineVariables [name = SQLSTATUS - value = FAIL]
    21/12/2010 11:39:41.781 : 4 : 1708 : 652 : CallCenter : 27 : # 5: LineVariables [name = SR_NUM - value = 3]
    21/12/2010 11:39:41.781 : 4 : 1708 : 652 : CallCenter : 27 : # 5: LineVariables [name = status_ws - value = FAIL]
    21/12/2010 11:39:41.937 : 0 : 2092 : 652 : CallCenter : 27 : CH[5]: Number of Packet Delayed [0 : 16]
    21/12/2010 11:39:41.937 : 4 : 1708 : 652 : CallCenter : 27 : GOT rtp_event on channel [5] type [16000]
    21/12/2010 11:39:41.937 : 4 : 1708 : 652 : CallCenter : 27 : # 5: GOT EVENT_END_OF_PLAY
    21/12/2010 11:39:41.937 : 4 : 1708 : 652 : CallCenter : 27 : # 5: New event received [0X101 - PLAY_DONE_EVENT - 0X1002]
    21/12/2010 11:39:41.937 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CCFRunIVRCampaign::RunCurrentAction[position=4  - type=1]
    21/12/2010 11:39:41.937 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CCFRunIVRCampaign::IVRCPlayFile(5, ROMI, prompt\lg24\E.wav, 0)
    21/12/2010 11:39:41.937 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CLineVoip::PlayMessage() Playing message[C:\ccanywhere\prompt\lg24\E.wav] playstop[
    21/12/2010 11:39:41.937 : 4 : 1708 : 652 : CallCenter : 27 : CH[5]: startPlaying [C:\ccanywhere\prompt\lg24\E.wav]
    21/12/2010 11:39:41.937 : 4 : 1708 : 652 : CallCenter : 27 : # 5: CLineVoip::PlayMessage() Playing message... Started
    21/12/2010 11:39:42.000 : 4 : 1708 : 652 : CallCenter : 27 : CCallCenter::SendOutMessage(CMessage) Sending message [99880100] to [type=20, id=0, grp=0]


    The only thing it tells me is that it failed, not why. The procedure name is CCA_SAT and I called it using "EXEC CCA_SAT(?ASSET_NUM)" on the ivr. Any thoughts? Do you know any way to find out why the query failed?

    Thank you for the help,

    Davi Elias
  • 3. Re: Running stored procedures from IVR
    guilford Newbie
    Currently Being Moderated
    That stuff is pretty ugly.

    Try using the only IVR product made for the Oracle DB. SQL*VOX !!!

    Have a look here... SQL*VOX

    Edited by: guilford on Dec 23, 2010 12:17 AM
  • 4. Re: Running stored procedures from IVR
    rschepis Explorer
    Currently Being Moderated
    In your Database connection defined in Administration Manager, what parrameters are you using?
  • 5. Re: Running stored procedures from IVR
    724818 Newbie
    Currently Being Moderated
    Hello rschepis,

    I only have the standard configurations for the database:

    Alias, Name and Description : DEVSIEBE (I have a JDBC connection in webelogic with this name)
    Login : SIEBEL (Standard login for a Siebel DB)
    The password is correct since I can run simple queries on the db, like finding users.

    Is there any other configuration I am missing here? These seem quite straightforward so I do not see any problem with them.

    Thank you for the help and happy new year.

    Davi Elias.
  • 6. Re: Running stored procedures from IVR
    rschepis Explorer
    Currently Being Moderated
    What are your DB types and versions for:

    CCA backend DB
    IVR target DB
  • 7. Re: Running stored procedures from IVR
    724818 Newbie
    Currently Being Moderated
    Hello rschepis,

    Both DBs are Oracle 10g running on a linux machine. My CCA DB is also configured on a RAC.

    Regards,

    Davi Elias
  • 8. Re: Running stored procedures from IVR
    rschepis Explorer
    Currently Being Moderated
    Have you tried:

    EXEC {CALL CCA_SAT (?ASSET_NUM, $SR_NUM)}
  • 9. Re: Running stored procedures from IVR
    736634 Newbie
    Currently Being Moderated
    Have you tried;

    EXEC CCA_SAT ?ASSET_NUM, ?SR_NUM

    I had the same issue (MS Database) and CCA wouldn't let me use the $ sign, I collected information using variables such as $SR_NUM, but sent them to sproc as ?SR_NUM and that worked just fine.
  • 10. Re: Running stored procedures from IVR
    724818 Newbie
    Currently Being Moderated
    Rschepis and Amartinez,

    I did try both ways, but none worked. Since I have a schedule to follow I ended up replacing my stored procedure with a function and an insert. It is working just fine, it seems that the problem happens only with the spoc.

    Thank you very much for the help.

    Delias

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points