(RADIATOR) Stored Procedure!!!

hakim hakim at kccg.net
Mon Aug 25 04:41:30 CDT 2003


hello,
Feels good to be on Radiator after a long time. The last version I worked on was 2.15 or so... and its good to see Radiator 3.6 with lots of new features!!!

The question I have is a pretty simple one!!
I am trying to authenticate using AuthBy PLSQL! The archvies mentioned that Radiator expects a recordset back from the calling stored procedures. I am writing a stored procedure that has an out parameter of CURSOR (below is the stored procedure). "Oracle throws an error of Wrong number of arugments or datatype!"
 I am not able to figure out how to link the recordset!!! 

If anyone has a sample stored procedure and the Radiator config file , I would appreciate it. I am attaching the sample Rad Config file along with my stored procedure, perhaps someone can figure out what I am doing wrong.

----SAMPLE CONFIG FILE----
Trace 4
AuthPort 1812
AcctPort 1813
LogDir c:\Radiator3.6\dump
DictionaryFile ./dictionary
UsernameCharset a-zA-Z0-9\._ at -
LogFile %L/%Y-%m-2logfile.log

DefineFormattedGlobalVar dbSourceODBC tas_sys
DefineFormattedGlobalVar dbSourceOracle tas
DefineFormattedGlobalVar dbUsername xxx
DefineFormattedGlobalVar dbAuth xxx

<Client localhost>
Secret mysecret
DupInterval 300
</Client>

<SessionDatabase SQL>
DBSource dbi:Oracle:tas3
DBUsername xxx
DBAuth xxx
AddQuery	insert into RADONLINE (username,code) values ('%n','000')
</SessionDatabase>

<Handler>
<AuthBy PLSQL>
	NoDefault
	DBSource dbi:Oracle:tas
	DBUsername %{GlobalVar:dbUsername}
	DBAuth %{GlobalVar:dbAuth}
	AuthBlock  begin \
	       tasradius.sp_Auth2('%n',:sReturnValue); \
	       end;
	AuthParamDef :sReturnValue,User-Password,Check
</AuthBy>
</Handler>

----END OF SAMPLE CONFIG FILE ---

-----STORED PROCEDURE ---------
CREATE OR REPLACE package tasradius
is
type Cursors is ref cursor return radreturn%rowtype;
procedure sp_Auth2(sUsername in varchar2,sReturn out tasradius.Cursors);
end tasradius;

procedure sp_Auth2(sUsername in varchar2,sReturn out tasradius.Cursors)
is
begin
open sReturn for select password from authenticationtable_000 where username='test';
exception 
    when others then
		 null;
end sp_Auth2;
end tasradius;
----- END OF STORED PROCEDURE ---------

---- RADIUS TRACE 4 ERROS-------
Mon Aug 25 12:05:49 2003: DEBUG: Packet dump:
*** Received from 127.0.0.1 port 1510 ....
Code:       Access-Request
Identifier: 199
Authentic:  1234567890123456
Attributes:
	User-Name = "test"
	Service-Type = Framed-User
	NAS-IP-Address = 203.63.154.1
	NAS-Port = 1234
	Called-Station-Id = "123456789"
	Calling-Station-Id = "987654321"
	NAS-Port-Type = Async
	User-Password = "<141><238>,<217><175>\<4><246><188>8<9><160><216>}x<153>"

Mon Aug 25 12:05:49 2003: DEBUG: Handling request with Handler ''
Mon Aug 25 12:05:49 2003: DEBUG:  Deleting session for test, 203.63.154.1, 1234
Mon Aug 25 12:05:49 2003: DEBUG: do query is: 'delete from RADONLINE where NASIDENTIFIER='203.63.154.1' and NASPORT=01234': 

Mon Aug 25 12:05:51 2003: DEBUG: Handling with Radius::AuthPLSQL
Mon Aug 25 12:05:51 2003: DEBUG: Handling with Radius::AuthPLSQL: 
Mon Aug 25 12:05:51 2003: DEBUG: Query is: begin tasradius.sp_Auth2('test',:sReturnValue); end;

Mon Aug 25 12:05:51 2003: ERR: Execute failed for 'begin tasradius.sp_Auth2('test',:sReturnValue); end;': ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_AUTH2'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored (DBD: oexec error)
Mon Aug 25 12:05:51 2003: ERR: Execute failed for 'begin tasradius.sp_Auth2('test',:sReturnValue); end;': ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_AUTH2'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored (DBD: oexec error)
Mon Aug 25 12:05:51 2003: DEBUG: Radius::AuthPLSQL looks for match with test
Mon Aug 25 12:05:51 2003: INFO: Access rejected for test: No such user
Mon Aug 25 12:05:51 2003: DEBUG: Packet dump:
*** Sending to 127.0.0.1 port 1510 ....
Code:       Access-Reject
Identifier: 199
Authentic:  1234567890123456
Attributes:
	Reply-Message = "Request Denied"

Mon Aug 25 12:05:51 2003: DEBUG: Packet dump:
*** Received from 127.0.0.1 port 1510 ....
Code:       Accounting-Request
Identifier: 200
Authentic:  <229><218>m<217><28><5>|=<127><175><25><238>p<248><166><30>
Attributes:
	User-Name = "test"
	Service-Type = Framed-User
	NAS-IP-Address = 203.63.154.1
	NAS-Port = 1234
	NAS-Port-Type = Async
	Acct-Session-Id = "00001234"
	Acct-Status-Type = Start
	Called-Station-Id = "123456789"
	Calling-Station-Id = "987654321"
	Acct-Delay-Time = 0

Mon Aug 25 12:05:51 2003: DEBUG: Handling request with Handler ''
Mon Aug 25 12:05:51 2003: DEBUG:  Adding session for test, 203.63.154.1, 1234
Mon Aug 25 12:05:52 2003: DEBUG: do query is: 'delete from RADONLINE where NASIDENTIFIER='203.63.154.1' and NASPORT=01234': 

Mon Aug 25 12:05:52 2003: DEBUG: do query is: 'insert into RADONLINE (username,code) values ('test','000')': 

Mon Aug 25 12:05:52 2003: DEBUG: Handling with Radius::AuthPLSQL
Mon Aug 25 12:05:52 2003: DEBUG: Handling accounting with Radius::AuthPLSQL
Mon Aug 25 12:05:52 2003: DEBUG: Accounting accepted
Mon Aug 25 12:05:52 2003: DEBUG: Packet dump:
*** Sending to 127.0.0.1 port 1510 ....
Code:       Accounting-Response
Identifier: 200
Authentic:  <229><218>m<217><28><5>|=<127><175><25><238>p<248><166><30>
Attributes:

Mon Aug 25 12:05:52 2003: DEBUG: Packet dump:
*** Received from 127.0.0.1 port 1510 ....
Code:       Accounting-Request
Identifier: 201
Authentic:  ?<221><245>H<185>/<206><197>_<127>+]<130>_\L
Attributes:
	User-Name = "test"
	Service-Type = Framed-User
	NAS-IP-Address = 203.63.154.1
	NAS-Port = 1234
	NAS-Port-Type = Async
	Acct-Session-Id = "00001234"
	Acct-Status-Type = Stop
	Called-Station-Id = "123456789"
	Calling-Station-Id = "987654321"
	Acct-Delay-Time = 0
	Acct-Session-Time = 1000
	Acct-Input-Octets = 20000
	Acct-Output-Octets = 30000

Mon Aug 25 12:05:52 2003: DEBUG: Handling request with Handler ''
Mon Aug 25 12:05:52 2003: DEBUG:  Deleting session for test, 203.63.154.1, 1234
Mon Aug 25 12:05:52 2003: DEBUG: do query is: 'delete from RADONLINE where NASIDENTIFIER='203.63.154.1' and NASPORT=01234': 

Mon Aug 25 12:05:52 2003: DEBUG: Handling with Radius::AuthPLSQL
Mon Aug 25 12:05:52 2003: DEBUG: Handling accounting with Radius::AuthPLSQL
Mon Aug 25 12:05:52 2003: DEBUG: Accounting accepted
Mon Aug 25 12:05:52 2003: DEBUG: Packet dump:
*** Sending to 127.0.0.1 port 1510 ....
Code:       Accounting-Response
Identifier: 201
Authentic:  ?<221><245>H<185>/<206><197>_<127>+]<130>_\L
Attributes:




---- END OF RADIUS TRACE 4 ERROS-------

BTW:I am using Radiator3.6 on windows 2000 server with Active Perl 5.6.1.635, DBI 1.34,DBD-Oracle 1.12 (installed with Active Perl PPM) and oracle 8i database.


Best Regards
Hakim T
Kuwaiti Canadian Consulting Group - KCCG


===
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.


More information about the radiator mailing list