Vg Not Getting Call From Callque In Database

[Guest u4910]

I have everything in Oralce working except for the processing of the callque. I can connect fine. I can see the tables from the telephone loader and see the data in the callque. VG seems to not be able to grab the records from the callque and process them. I see
ORA-01036: illegal variable name/number
but I have no idea where it's coming from or why. Please see attached logs and advise.

 

Here is how I'm loading my test call from a copy of a production record into the test table:

 

insert into voiceguide2.callque (id,PhoneNumberPrefix,phonenumber,activatetime,onanswerlive,
TimeStart_Mon ,TimeStop_Mon,TimeStart_Tue,TimeStop_Tue,
TimeStart_Wed,TimeStop_Wed,TimeStart_Thu,TimeStop_Thu,
TimeStart_Fri,TimeStop_Fri,TimeStart_Sat,TimeStop_Sat,
TimeStart_Sun,TimeStop_Sun,
CampaignName
--,calloptions
)
select
id,PhoneNumberPrefix,phonenumber,sysdate,onanswerlive,
TimeStart_Mon ,TimeStop_Mon,TimeStart_Tue,TimeStop_Tue,
TimeStart_Wed,TimeStop_Wed,TimeStart_Thu,TimeStop_Thu,
TimeStart_Fri,TimeStop_Fri,TimeStart_Sat,TimeStop_Sat,
TimeStart_Sun,TimeStop_Sun,
CampaignName
--,calloptions
from voiceguide.cq
/

 

 

 

070213.zip

[SupportTeam]

Can you load outgoing calls into the system using VoiceGuide's Telephone Number Loader?

 

Can you load outgoing calls into the system using the OutDial XML file?

[Guest u4910]

Loading an xml, it takes the file, but gets the same ORA-01036 error.

The telephone number loader also gets ORA-01036.

I know I can use the default database instead, but would like to use Oracle before going in production on this system.

So any help to get this working is appreciated.

I can see the records in the call que from the telephone number loader.

I can also get records via the vg script during the call, so the connection works.

There just seems to be something that VG is doing when it is trying to insert calls or get calls from the call que.

Can you send the statements that are being executed against the database so I can look at them? Maybe there is a format issue in the way you are passing variables to the database.

[Guest u4910]

See attached.

0703_0806_vgEngine.txt

0703_ktTel.txt

[SupportTeam]

Could we please see the vgDialListLoad trace that captures the loading of the outgoing call into the database.

 

The SQL queries used are shown towards the bottom of this : http://www.voiceguide.com/vghelp/source/html/dial_vgdb_external_config.htm

 

Another way to get more vgEngine entries in the log is to set the VoiceGuide log level in VG.INI to 20 (see below). The SQL queries issued should then show in vgEngine log as well.

[Log]

VoiceGuide=20

[Guest u4910]

I grabbed the query from stage 2 and it executes fine from sqlplus in Oracle.

Stage 1 is where it seems to get the error. But I don't know what happens during stage 1. What is going on in stage 1?

0703_2256_vgDialListLoad.txt

0703_2242_vgEngine.txt

[SupportTeam]

The attached vgDialListLoad trace does not capture the loading of the outgoing call into the database.

 

Can you please load a call using the "Outbound Call Loader" application and post the vgDialListLoad trace that captures this.

 

Can you also post the the schema of the callque table that you created in Oracle? Perhaps one of the column names is wrong or is of wrong datatype.

 

The error happens with the SQL that you see in trace (shown below). Looks like logs mistakenly refer to 'stage1' when reporting the error.

 

 

224653.896 10 dial cmd_FindNextCall_Stage2_StandardActivationCheck ExecuteReader call SQL=[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('03-Jul-2013 22:46:53', 'dd-mon-yyyy hh24:mi:ss')) AND ((callque.TimeStart_Wed <= 2246) AND (callque.TimeStop_Wed > 2246)) AND (callque.RetriesLeft >= 0)) ]

[Guest u4910]

I see that it puts in "guid_no_used" and it looks like it does not belong there.

Also the number of columns does not match the number of values that is being inserted?

Please advise.

I can see that the cdrin is being used OK by the database and VG. It seems just the callque is an issue.

 

0705_1000_vgDialListLoad.txt

[SupportTeam]

The INSERT statement fails when issued to the database/table that you have created.

 

Can you also the the schema of the callque table that you created in Oracle? Most likely one of the column names is wrong or is of wrong datatype.

100320.882   0       dial  cmd_ListInsert [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)]
100320.883   0       dial  cmd_callque_Insert ExecuteNonQuery [5557776880,,guid_not_used,7/5/2013 10:03:09 AM,1,1,1,1,1,1,1,2359,2359,2359,2359,2359,2359,2359,strCampaignName=none,iScheduler=0,iPriority=1,strOnAnswerLive=,strOnAnswerMachine=,strOnAnswerFax=,strOnNotAnswered=,strOnRetriesExhausted=,iAnswerTimeout=60,iRetriesLeft=2,iRetriesDelay=10,strRV=,strOptions=,strEscalationCalls=]
100320.884   0 ERROR 7.3.4912.27739       Db_Insert_callqueAndPortList_call : ORA-01036: illegal variable name/number

   at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
   at System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind)
   at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
   at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
   at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
   at ..(DbCommand& , DbCommand& ,  , String )

[Guest u4910]

I'm using this on Windows 7 (32 bit) in production (VG 7.3.3)

But on my new system, Win 2008 R2 64 Bit (VG 7.3.3), it seems to be an issue.

 

 

22:43:25 SQL> descr callque
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
GUID NUMBER(38)
PHONENUMBER VARCHAR2(100)
PHONENUMBERPREFIX VARCHAR2(100)
ACTIVATETIME DATE
ANSWERTIMEOUT NUMBER(38)
CAMPAIGNNAME VARCHAR2(100)
SCHEDULER NUMBER(38)
PRIORITY NUMBER(38)
ONANSWERLIVE VARCHAR2(1000)
ONANSWERMACHINE VARCHAR2(1000)
ONANSWERFAX VARCHAR2(1000)
ONNOTANSWERED VARCHAR2(1000)
ONRETRIESEXHAUSTED VARCHAR2(1000)
RETRIESLEFT NUMBER(38)
RETRIESDELAY NUMBER(38)
RV VARCHAR2(4000)
TIMESTART_MON NUMBER(38)
TIMESTOP_MON NUMBER(38)
TIMESTART_TUE NUMBER(38)
TIMESTOP_TUE NUMBER(38)
TIMESTART_WED NUMBER(38)
TIMESTOP_WED NUMBER(38)
TIMESTART_THU NUMBER(38)
TIMESTOP_THU NUMBER(38)
TIMESTART_FRI NUMBER(38)
TIMESTOP_FRI NUMBER(38)
TIMESTART_SAT NUMBER(38)
TIMESTOP_SAT NUMBER(38)
TIMESTART_SUN NUMBER(38)
TIMESTOP_SUN NUMBER(38)
CALLOPTIONS VARCHAR2(4000)
ESCALATIONCALLS VARCHAR2(4000)

 

[Guest u4910]

I notice that the new one is int number(38) and I've changed them back to number to match what I have on production.

It still seems not to read the callque.

[SupportTeam]

Can you please DROP the current callque table and then create the callque table using the CREATE below:

CREATE TABLE callque
(
ID Int PRIMARY KEY,
GUID Int,
PhoneNumber VARCHAR(100) DEFAULT '',
PhoneNumberPrefix VARCHAR(100) DEFAULT '',
ActivateTime DATE,
AnswerTimeout Int DEFAULT 40,
CampaignName VARCHAR(100) DEFAULT '',
Scheduler Int DEFAULT 0,
Priority Int DEFAULT 10,
OnAnswerLive VARCHAR(1000) DEFAULT '',
OnAnswerMachine VARCHAR(1000) DEFAULT '',
OnAnswerFax VARCHAR(1000) DEFAULT '',
OnNotAnswered VARCHAR(1000) DEFAULT '',
OnRetriesExhausted VARCHAR(1000) DEFAULT '',
RetriesLeft Int DEFAULT 2,
RetriesDelay Int DEFAULT 5,
RV VARCHAR(4000) DEFAULT '',
TimeStart_Mon Int DEFAULT 1,
TimeStop_Mon Int DEFAULT 2359,
TimeStart_Tue Int DEFAULT 1,    
TimeStop_Tue Int DEFAULT 2359,    
TimeStart_Wed Int DEFAULT 1,    
TimeStop_Wed Int DEFAULT 2359,
TimeStart_Thu Int DEFAULT 1,    
TimeStop_Thu Int DEFAULT 2359,    
TimeStart_Fri Int DEFAULT 1,    
TimeStop_Fri Int DEFAULT 2359,    
TimeStart_Sat Int DEFAULT 1,    
TimeStop_Sat Int DEFAULT 2359,    
TimeStart_Sun Int DEFAULT 1,    
TimeStop_Sun Int DEFAULT 2359,        
CallOptions VARCHAR(4000) DEFAULT '',
EscalationCalls VARCHAR(4000) DEFAULT ''
);

and then create the following indexes:

CREATE INDEX IdxCqActivateTime ON callque (ActivateTime);
CREATE INDEX IdxCqCampaignName ON callque (CampaignName);
CREATE INDEX IdxCqPriority ON callque (Priority);
CREATE INDEX IdxCqTimeStart_Mon ON callque (TimeStart_Mon);
CREATE INDEX IdxCqTimeStart_Tue ON callque (TimeStart_Tue);
CREATE INDEX IdxCqTimeStart_Wed ON callque (TimeStart_Wed);
CREATE INDEX IdxCqTimeStart_Thu ON callque (TimeStart_Thu);
CREATE INDEX IdxCqTimeStart_Fri ON callque (TimeStart_Fri);
CREATE INDEX IdxCqTimeStart_Sat ON callque (TimeStart_Sat);
CREATE INDEX IdxCqTimeStart_Sun ON callque (TimeStart_Sun);
CREATE INDEX IdxCqTimeStop_Mon ON callque (TimeStop_Mon);
CREATE INDEX IdxCqTimeStop_Tue ON callque (TimeStop_Tue);
CREATE INDEX IdxCqTimeStop_Wed ON callque (TimeStop_Wed);
CREATE INDEX IdxCqTimeStop_Thu ON callque (TimeStop_Thu);
CREATE INDEX IdxCqTimeStop_Fri ON callque (TimeStop_Fri);
CREATE INDEX IdxCqTimeStop_Sat ON callque (TimeStop_Sat);
CREATE INDEX IdxCqTimeStop_Sun ON callque (TimeStop_Sun);

and then try loading the call into the system using the Telephone Number Loader application again, then please post the vgDialListLoad trace that captures this call load attempt.

[Guest u4910]

I tried that and it still didn't work.

See attached.

0710_0929_vgEngine.zip

[Guest u4910]

I still see that guid_not_used and that may be causing an issue on the insert?

[SupportTeam]

guid_not_used is just an indication that nbothing is being inserted into the GUID colums.

 

Are you able to see in your database logs what caused the "ORA-01036: illegal variable name/number" to be returned to the calling application?

[Guest u4910]

I've tried various things to see the failed statements going to the database. I am only able to trace the good statements. I'll keep looking.

I've tried tracing ODBC on the client too, but I don't think it's going through odbc.

If you have any other ideas, let me know.

[Guest u4910]

I still can't seem to make it work.

I can get data from in the script with the db modules.

I'm using the same connection type and string, but it still gives the ORA-01036 error.

I've tried other connection types and strings and can't make it work any better.

I've copied and pasted the select statement it's using into the database and it works fine.

Is there any way to get any higher level of detail on the exact string it's passing?

I'm thinking there is a format or translation issue going on.

[SupportTeam]

Can you please advise version of Oracle used and the ADO.NET Data Provider used.

 

Can you provide direct download link to Development/Evaluation version of the Database that you use?

 

We can then download the database engine and install on one of our our test systems here and see what can be the issue.

[Guest u4910]

Database is 11.2.0.3 (with the .3 patch).
I have the same issue with any database I use (9.2,10.2,11.2)
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-sparc64soft-088481.html

The client installed is
win32_11gr1_client
win64_11gr2_client

The OS VG is on is:
Windwos 2008 R2 64 Bit.


If you have Windows 2008 R2 64 Bit and are able to use the callque with any version of Oracle, let me know.

Note: I do not have any issues with the XP 32 bit machine I'm running on now with the same version of VG

 

[SupportTeam]

We set up a test with Oracle 11g Express.

 

The table was created manually using this command:

CREATE TABLE callque
(
ID NUMBER PRIMARY KEY,
PhoneNumber VARCHAR(100) DEFAULT '',
PhoneNumberPrefix VARCHAR(100) DEFAULT '',
ActivateTime DATE,
AnswerTimeout NUMBER DEFAULT 40,
CampaignName VARCHAR(100) DEFAULT '',
Scheduler NUMBER DEFAULT 0,
Priority NUMBER DEFAULT 10,
OnAnswerLive VARCHAR(1000) DEFAULT '',
OnAnswerMachine VARCHAR(1000) DEFAULT '',
OnAnswerFax VARCHAR(1000) DEFAULT '',
OnNotAnswered VARCHAR(1000) DEFAULT '',
OnRetriesExhausted VARCHAR(1000) DEFAULT '',
RetriesLeft NUMBER DEFAULT 2,
RetriesDelay NUMBER DEFAULT 5,
RV VARCHAR(4000) DEFAULT '',
TimeStart_Mon NUMBER DEFAULT 1,
TimeStop_Mon NUMBER DEFAULT 2359,
TimeStart_Tue NUMBER DEFAULT 1,    
TimeStop_Tue NUMBER DEFAULT 2359,    
TimeStart_Wed NUMBER DEFAULT 1,    
TimeStop_Wed NUMBER DEFAULT 2359,
TimeStart_Thu NUMBER DEFAULT 1,    
TimeStop_Thu NUMBER DEFAULT 2359,    
TimeStart_Fri NUMBER DEFAULT 1,    
TimeStop_Fri NUMBER DEFAULT 2359,    
TimeStart_Sat NUMBER DEFAULT 1,    
TimeStop_Sat NUMBER DEFAULT 2359,    
TimeStart_Sun NUMBER DEFAULT 1,    
TimeStop_Sun NUMBER DEFAULT 2359,        
CallOptions VARCHAR(4000) DEFAULT '',
EscalationCalls VARCHAR(4000) DEFAULT ''
)

and an entry was loaded into the database manually using:

INSERT INTO SYSTEM.CALLQUE
    (ID, PHONENUMBER, PHONENUMBERPREFIX, ACTIVATETIME, ANSWERTIMEOUT, CAMPAIGNNAME, SCHEDULER, PRIORITY, ONANSWERLIVE, ONANSWERMACHINE, ONANSWERFAX, ONNOTANSWERED, ONRETRIESEXHAUSTED, RETRIESLEFT, RETRIESDELAY, RV, TIMESTART_MON, TIMESTOP_MON, TIMESTART_TUE, TIMESTOP_TUE, TIMESTART_WED, TIMESTOP_WED, TIMESTART_THU, TIMESTOP_THU, TIMESTART_FRI, TIMESTOP_FRI, TIMESTART_SAT, TIMESTOP_SAT, TIMESTART_SUN, TIMESTOP_SUN, CALLOPTIONS, ESCALATIONCALLS)
VALUES
    (43, '333', '333', TO_TIMESTAMP('27.05.2013 01:12:12.3456','DD.MM.YYYY HH24:MI:SSXFF'), 30, 'camp', 1, 1, 'c:\live.vgs', 'c:\am.vgs', '', 'c:\na.vbs', 'c:\re.vbs', 1, 1, 'rv', 1, 2369, 1, 2369, 1, 2369, 1, 2369, 1, 2369, 1, 2369, 1, 2369, '', '');

VoiceGuide read that entry in from the callque table and performed the outgoing call.

 

The Config.xml entry was:

<OutDialQue_ADODB_Provider>Oracle.DataAccess.Client</OutDialQue_ADODB_Provider>
<OutDialQue_Database>XE</OutDialQue_Database>
<OutDialQue_ConnectString>Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.20)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_Device_Voice=$DATABASE)));User Id=system;Password=adFh237a;</OutDialQue_ConnectString>
<OutDialQue_PortToUse_LinkField>Disable</OutDialQue_PortToUse_LinkField>
<OutDialQue_SqlPrefix>SELECT</OutDialQue_SqlPrefix>
<OutDialQue_SqlSuffix></OutDialQue_SqlSuffix>

NB. The Telephone Number Loader app was still raising "ORA-00936: missing expression" errors when trying to load the calls into he Oracle. Telephone Number Loader app uses a different approach to insert new calls then the SQL quoted above.

[Guest u4910]

I used your example dialer string below and I got it working now from the database!!!

I'll post more when my tests are complete.

Thanks!!!

[Guest u4910]

One thing I notice is that I don't see any records going into cdrout. I see cdrin records in that table.

I'll post more later. I just don't see any records and no references in the log file to cdrout. Strange.

I do see the cdrout files, as they are normally there, just not in the database table.

[Guest u4910]

CDROUT table working now. I was out of space.

All is working perfect now from the database!

Thanks again. I think it was just a choice in connection type. Oracle.DataAccess.Client works better on 2008 R2 than the other one.