(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