10 Replies Latest reply: Jan 15, 2011 3:03 PM by davi.elias RSS

    Running stored procedures from IVR

    davi.elias
      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
          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
            davi.elias
            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
              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
                In your Database connection defined in Administration Manager, what parrameters are you using?
                • 5. Re: Running stored procedures from IVR
                  davi.elias
                  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
                    What are your DB types and versions for:

                    CCA backend DB
                    IVR target DB
                    • 7. Re: Running stored procedures from IVR
                      davi.elias
                      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
                        Have you tried:

                        EXEC {CALL CCA_SAT (?ASSET_NUM, $SR_NUM)}
                        • 9. Re: Running stored procedures from IVR
                          736634
                          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
                            davi.elias
                            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