Accounting with Oracle and stored procedures

Tore Anderson tore at linpro.no
Wed Oct 10 16:21:09 CEST 2007


Hi.  I'm having problems integrating FreeRADIUS with Oracle
(Instantclient 10.2.0.3 library).  My client makes two stored
procedures available to me:

FUNCTION session_start(id_in IN VARCHAR2, terminal_address_in IN VARCHAR2, nas_address_in IN VARCHAR2, msisdn_in IN VARCHAR2, apn_in IN VARCHAR2, rat_in NUMBER, start_date_in IN DATE) RETURN INTEGER;

and

FUNCTION session_stop(id_in IN VARCHAR2, bytes_transmitted_in IN NUMBER, bytes_received_in IN NUMBER, stop_date_in IN DATE) RETURN INTEGER;

The juicy bits from my sql{} statement:

driver = "rlm_sql_oracle"
server = "foobar"
login = "foobar"
password = "foobar"
radius_db = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=foobar)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=foobar)))"
accounting_start_query = "SELECT session_start('%{Acct-Session-Id}', '%{Framed-IP-Address}', '%{NAS-IP-Address}', '%{Calling-Station-Id}', '%{Called-Station-Id}', '0', TO_DATE('%S','yyyy-mm-dd hh24:mi:ss')) FROM dual"
accounting_stop_query = "SELECT session_stop('%{Acct-Session-Id}', '0', '0', TO_DATE('%S','yyyy-mm-dd hh24:mi:ss')) FROM dual"

This is parsed correctly and FreeRADIUS (v1.1.0) starts up nicely
enough.  But when an accounting query comes in, I get the following
error:

rlm_sql (sql): Reserving sql socket id: 4
SELECT session_start('c16dd2c12dc4536a', '10.16.12.253', '10.0.0.1', '12341324', 'foobar', '0', TO_DATE('2007-10-10 16:16:19','yyyy-mm-dd hh24:mi:ss')) FROM dual
rlm_sql_oracle: execute query failed in sql_query: ORA-24374: define not done before fetch or execute and fetch
rlm_sql (sql): Attempting to connect rlm_sql_oracle #4
rlm_sql (sql): Connected new DB handle, #4
SELECT session_start('c16dd2c12dc4536a', '10.16.12.253', '10.0.0.1', '12341324', 'foobar', '0', TO_DATE('2007-10-10 16:16:19','yyyy-mm-dd hh24:mi:ss')) FROM dual
rlm_sql_oracle: execute query failed in sql_query: ORA-24374: define not done before fetch or execute and fetch
rlm_sql (sql): failed after re-connect
rlm_sql (sql): Couldn't insert SQL accounting START record - ORA-24374: define not done before fetch or execute and fetch

We've also tried changing from FUNCTION to PROCEDURE, but no change
in behaviour.

Anyone here had similar problems?  I'm thankful for any suggestions!

Regards
-- 
Tore Anderson



More information about the Freeradius-Users mailing list