Accounting issues in Oracle solved

[Guilherme Franco]

Hello,

I had to modify oracle-dialup.conf to make accounting on/off to work.

In AcctSessionTime, the original query would generate "expected
NUMBER, got INTERVAL" error.

Here is the original:

accounting_onoff_query = "UPDATE ${acct_table1} SET
AcctStopTime=TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'),
AcctSessionTime=((TO_DATE('%S','yyyy-mm-dd hh24:mi:ss') -
AcctStartTime)*86400), AcctTerminateCause='%{Acct-Terminate-Cause}',
AcctStopDelay = %{Acct-Delay-Time:-0} WHERE AcctSessionTime=0 AND
AcctStopTime IS NULL AND NASIPAddress = '%{NAS-IP-Address}' AND
AcctStartTime <= TO_DATE('%S','yyyy-mm-dd hh24:mi:ss')"


So I modified it to:

accounting_onoff_query = "UPDATE ${acct_table1} SET
AcctStopTime=TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'),
AcctSessionTime=(to_number(TO_DATE('%S','yyyy-mm-dd hh24:mi:ss') -
cast(AcctStartTime as date))*86400),
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay =
%{Acct-Delay-Time:-0} WHERE AcctSessionTime=0 AND AcctStopTime IS NULL
AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStartTime <=
TO_DATE('%S','yyyy-mm-dd hh24:mi:ss')"

And it works great now.