Using Oracle With 7.3.1

[Guest u4910]

I'm trying to use Oracle with VG 7.3.1. I created the tables manually and VG seems to start OK. When I try to add a call via puting an XML in the data folder, VG complains that it can't insert the records needed. Please review the attachment and advise.

0513_2311_vgEngine.zip

[SupportTeam]

Trace shows that Oracle is returning:

 

ORA-00936: missing expression

 

in response to the INSERT command.

 

You may need to look at the Oracle logs to see why Oracle is responding with this error.

 

Have you ensured that the callque table in Oracle has all the fields/columns defined, and they are of appropriate type?

 

The file

C:\Program Files\VoiceGuide\System\setup\Db_Create_System.Data.OracleClient.sql

should show the table structure.

 

231422.378 20               OutDial load from [C:\Program Files\VoiceGuide\data\outdial_test.xml]
231422.381 20               DialListLoadFileXML ReadAllText call
231422.390 20               dial  Db_Insert_SingleEntry '74224','', [5/13/2012 11:14:22 PM], PortSelection=, campaign=DrewCommunityWellness121511, scheduler=0, priority=9, OnAnswerLive=[c:\payrem\drewcommunitywellness121511.wav], OnAnswerMachine=[c:\payrem\drewcommunitywellness121511.wav], OnAnswerFax=[], OnNotAnswered=[], OnRetriesExhausted=[], 60, 2, 5, RV=, Options= ESCALATION: 
231422.400 20               dial  cmdCQ : INSERT INTO callque (PhoneNumber, PhoneNumberPrefix, ActivateTime, TimeStart_Mon, TimeStart_Tue, TimeStart_Wed, TimeStart_Thu, TimeStart_Fri, TimeStart_Sat, TimeStart_Sun, TimeStop_Mon, TimeStop_Tue, TimeStop_Wed, TimeStop_Thu, TimeStop_Fri, TimeStop_Sat, TimeStop_Sun, CampaignName, Scheduler, Priority, OnAnswerLive, OnAnswerMachine, OnAnswerFax, OnNotAnswered, OnRetriesExhausted, AnswerTimeout, RetriesLeft, RetriesDelay,  RV, CallOptions, EscalationCalls) VALUES (@strNbrToDial, @strPhoneNumberPrefix, @dateActivateTime, @iTimeStart_Mon, @iTimeStart_Tue, @iTimeStart_Wed, @iTimeStart_Thu, @iTimeStart_Fri, @iTimeStart_Sat, @iTimeStart_Sun, @iTimeStop_Mon, @iTimeStop_Tue, @iTimeStop_Wed, @iTimeStop_Thu, @iTimeStop_Fri, @iTimeStop_Sat, @iTimeStop_Sun, @strCampaignName, @iScheduler, @iPriority, @strOnAnswerLive, @strOnAnswerMachine, @strOnAnswerFax, @strOnNotAnswered, @strOnRetriesExhausted, @iAnswerTimeout, @iRetriesLeft, @iRetriesDelay, @strRV, @strOptions, @strEscalationCalls)
231422.402 20               dial  cmdPU : INSERT INTO porttouse (CallID, PortNumber, ActivateTime, CampaignName, Scheduler, Priority, TimeStart_Mon, TimeStart_Tue, TimeStart_Wed, TimeStart_Thu, TimeStart_Fri, TimeStart_Sat, TimeStart_Sun, TimeStop_Mon, TimeStop_Tue, TimeStop_Wed, TimeStop_Thu, TimeStop_Fri, TimeStop_Sat, TimeStop_Sun ) VALUES (@iCallID, @iPortNumber, @dateActivateTime, @strCampaignName, @iScheduler, @iPriority, @iTimeStart_Mon, @iTimeStart_Tue, @iTimeStart_Wed, @iTimeStart_Thu, @iTimeStart_Fri, @iTimeStart_Sat, @iTimeStart_Sun, @iTimeStop_Mon, @iTimeStop_Tue, @iTimeStop_Wed, @iTimeStop_Thu, @iTimeStop_Fri, @iTimeStop_Sat, @iTimeStop_Sun )
231422.407 20               dial  cmd_callque_Insert ExecuteNonQuery [74224,,guid_not_used,5/13/2012 11:14:22 PM,800,800,800,800,800,800,800,2359,2359,2359,2359,2359,2359,2359,strCampaignName=DrewCommunityWellness121511,iScheduler=0,iPriority=9,strOnAnswerLive=c:\payrem\drewcommunitywellness121511.wav,strOnAnswerMachine=c:\payrem\drewcommunitywellness121511.wav,strOnAnswerFax=,strOnNotAnswered=,strOnRetriesExhausted=,iAnswerTimeout=60,iRetriesLeft=2,iRetriesDelay=5,strRV=,strOptions=,strEscalationCalls=] 
231422.407 20               dial  cmd_callque_Insert ExecuteNonQuery [iNSERT INTO callque (PhoneNumber, PhoneNumberPrefix, ActivateTime, TimeStart_Mon, TimeStart_Tue, TimeStart_Wed, TimeStart_Thu, TimeStart_Fri, TimeStart_Sat, TimeStart_Sun, TimeStop_Mon, TimeStop_Tue, TimeStop_Wed, TimeStop_Thu, TimeStop_Fri, TimeStop_Sat, TimeStop_Sun, CampaignName, Scheduler, Priority, OnAnswerLive, OnAnswerMachine, OnAnswerFax, OnNotAnswered, OnRetriesExhausted, AnswerTimeout, RetriesLeft, RetriesDelay,  RV, CallOptions, EscalationCalls) VALUES (@strNbrToDial, @strPhoneNumberPrefix, @dateActivateTime, @iTimeStart_Mon, @iTimeStart_Tue, @iTimeStart_Wed, @iTimeStart_Thu, @iTimeStart_Fri, @iTimeStart_Sat, @iTimeStart_Sun, @iTimeStop_Mon, @iTimeStop_Tue, @iTimeStop_Wed, @iTimeStop_Thu, @iTimeStop_Fri, @iTimeStop_Sat, @iTimeStop_Sun, @strCampaignName, @iScheduler, @iPriority, @strOnAnswerLive, @strOnAnswerMachine, @strOnAnswerFax, @strOnNotAnswered, @strOnRetriesExhausted, @iAnswerTimeout, @iRetriesLeft, @iRetriesDelay, @strRV, @strOptions, @strEscalationCalls)] 
231422.426 20         ERROR v7.3.4492.42488 (2012-04-19 22:36:17.54)       Db_Insert_callqueAndPortList_call : ORA-00936: missing expression

  at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
  at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
  at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
  at ktlib.vgDb.vgDb_ProviderInvariant.Db_Insert_callqueAndPortList(DbCommand& cmd_callque_Insert, DbCommand& cmd_PortList_Insert, clsFullTelNbr zPhoneNumber, String strPortSelection)
231422.430 20               dial  Db_Insert_porttouse not making any inserts as zConfigXml_sDialer_OutDialQue_porttouse_LinkField=Disable
231422.430 20               OutDial input file : loaded 0 entries from C:\Program Files\VoiceGuide\data\outdial_test.xml
231422.430 20               OutDial input file deleted : C:\Program Files\VoiceGuide\data\outdial_test.xml

[Guest u4910]

The table seems OK. I copied and pasted the insert statement into Oracle. What are the @ prefixes for? I assume that you are passing variables that are supposed to be translated before being passed into the database. What might the issue be? Here is what it looks like:

 

RetriesDelay, RV, CallOptions, EscalationCalls) VALUES (@strNbrToDial,

*

ERROR at line 6:

ORA-00936: missing expression

 

 

SQL> l

1 INSERT INTO callque (PhoneNumber, PhoneNumberPrefix, ActivateTime, TimeStart_Mon,

2 TimeStart_Tue, TimeStart_Wed, TimeStart_Thu, TimeStart_Fri, TimeStart_Sat,

3 TimeStart_Sun, TimeStop_Mon, TimeStop_Tue, TimeStop_Wed, TimeStop_Thu, TimeStop_Fri,

4 TimeStop_Sat, TimeStop_Sun, CampaignName, Scheduler, Priority, OnAnswerLive,

5 OnAnswerMachine, OnAnswerFax, OnNotAnswered, OnRetriesExhausted, AnswerTimeout, RetriesLeft,

6* RetriesDelay, RV, CallOptions, EscalationCalls) VALUES (@strNbrToDial,

[SupportTeam]

What are the @ prefixes for? I assume that you are passing variables that are supposed to be translated before being passed into the database.

Correct.

 

What might the issue be?

Can you see the Oracle logs that show you the actual command and parameters received by the Oracle?

 

What version of Oracle are you using and what version are the client drivers/connectors?

[Guest u4910]

I'm using Oracle 10.2.0.4 on a remote server. The client is 11.1 full client (not instant client) with Oracle ODBC. I can see VG do select statements as it looks for calls. I dont see where the inserts are successful at all. I don't even see it get passed to the database. I'll try a manual entry into the callque and see how that goes. Here is the select as it checks periodically.

I've tried odbc tracing and tracing on the client side for Oracle, but I haven't had any luck getting trace files to produce.

 

SELECT callque.ID, callque.PhoneNumber, callque.PhoneNumberPrefix, callque.ActivateTime, callque.CampaignName, callque.Priority, callque.OnAnswerLive, callque.OnAnswerMachine, callque.OnAnswerFax, callque.OnNotAnswered, callque.OnRetriesExhausted, callque.AnswerTimeout, callque.RetriesLeft, callque.RetriesDelay, callque.RV, callque.CallOptions, callque.EscalationCalls

FROM callque

WHERE ( (callque.ActivateTime <= to_date(:"SYS_B_0", :"SYS_B_1")) AND ((callque.TimeStart_Tue <= :"SYS_B_2") AND (callque.TimeStop_Tue > :"SYS_B_3")) AND (callque.RetriesLeft >= :"SYS_B_4"))

[Guest u4910]

If I want to create an entry myself in the database in the callque table, what is a good example for an insert statement that you can suggest?

[Guest u4910]

I did this and it worked when I inserted this record.

Please advise on how I need to use the "id" field and if that needs to be a sequence. Also do I need to worry abou the "quid". I won't use the portstouse yet, but maybe later on when I upgrade to a T1 line.

 

insert into callque (id,phonenumber,activatetime,onanswerlive)

values

(1,'74224',sysdate,'c:\payrem\drewcommunitywellness121511.wav')

[Guest u4910]

Also, VG seemed to create the entry in the CDROUT table OK, when I inserted the record in the callque table. So VG inserted into cdrout OK, but was not able to insert the record into the callque table based on the xml file being dropped into the data folder.

[SupportTeam]

The SQL command that is used to insert an entry into "callque" table can be seen in the vgEngine trace. It is:

 

INSERT INTO callque 
(PhoneNumber, PhoneNumberPrefix, ActivateTime, TimeStart_Mon, TimeStart_Tue, TimeStart_Wed, TimeStart_Thu, TimeStart_Fri, TimeStart_Sat, TimeStart_Sun, TimeStop_Mon, TimeStop_Tue, TimeStop_Wed, TimeStop_Thu, TimeStop_Fri, TimeStop_Sat, TimeStop_Sun, CampaignName, Scheduler, Priority, OnAnswerLive, OnAnswerMachine, OnAnswerFax, OnNotAnswered, OnRetriesExhausted, AnswerTimeout, RetriesLeft, RetriesDelay,  RV, CallOptions, EscalationCalls) 
VALUES 
(@strNbrToDial, @strPhoneNumberPrefix, @dateActivateTime, @iTimeStart_Mon, @iTimeStart_Tue, @iTimeStart_Wed, @iTimeStart_Thu, @iTimeStart_Fri, @iTimeStart_Sat, @iTimeStart_Sun, @iTimeStop_Mon, @iTimeStop_Tue, @iTimeStop_Wed, @iTimeStop_Thu, @iTimeStop_Fri, @iTimeStop_Sat, @iTimeStop_Sun, @strCampaignName, @iScheduler, @iPriority, @strOnAnswerLive, @strOnAnswerMachine, @strOnAnswerFax, @strOnNotAnswered, @strOnRetriesExhausted, @iAnswerTimeout, @iRetriesLeft, @iRetriesDelay, @strRV, @strOptions, @strEscalationCalls)

 

The type of values used in the fields in SQL command above can be seen a bit lower in the vgEngine trace. The vgEngine trace also tags some of the values for easier reading:

 

74224,,5/13/2012 11:14:22 PM,800,800,800,800,800,800,800,2359,2359,2359,2359,2359,2359,2359,
strCampaignName=DrewCommunityWellness121511,
iScheduler=0,iPriority=9,
strOnAnswerLive=c:\payrem\drewcommunitywellness121511.wav,
strOnAnswerMachine=c:\payrem\drewcommunitywellness121511.wav,
strOnAnswerFax=,
strOnNotAnswered=,
strOnRetriesExhausted=,
iAnswerTimeout=60,iRetriesLeft=2,iRetriesDelay=5,
strRV=,
strOptions=,
strEscalationCalls= 

 

The integers 800 and 2359 are the Monday->Sunday starting and stopping times.

Rest of values are pretty self-explanatory.

 

 

Please advise on how I need to use the "id" field and if that needs to be a sequence.

Best to have "id" field an auto-generating sequence number.

 

Also do I need to worry about the "quid".

GUID field does not need to be set. GUID can be used to link up the records between the callque and porttouse tables.

 

Please let us know if you have any more questions.

[Guest u4910]

Since the way the insert statement is formatted by VG from the xml in the data directory, Oracle will not recognize those @ fields as variables that it can read values from; the insert statement needs the values already translated. Since this is the case, I will no longer try to use xml files in the data folder to create calls since that will no longer work. I'll continue to create manual entries in the callque table since that is working fine.

Thanks,

[SupportTeam]

The @ notation is usually used by .NET Data Providers (Looks like Oracle has one here: http://www.oracle.com/technetwork/topics/dotnet/index-085163.html )

 

It looks like on your system an ODBC driver is used for connection ,instead of a ".NET Data Provider". (?)

 

But for advanced integration direct creation of entries in callque etc. tables is the best approach - it gives you more direct and immediate control over data loading and queue monitoring etc. We would recommend using this approach to achieve best integration between your system that generates the call list and VoiceGuide.