Valued Contributor I

oracel clearpass sql statement


I'm writing a simple auth source that queries an oracle database to see  if a mac address is present. If it is, I can assign a particular role to the connection.


If I have


select COUNT(*)   FROM sns.quarantined_macs WHERE mac = UPPER('%{Connection:Client-Mac-Address-Hyphen}')


as my auth source attribute, then when i click on save, it seems to get saved correctly.


If I have


select COUNT(*) into quarantined_mac  FROM sns.quarantined_macs WHERE mac = UPPER('%{Connection:Client-Mac-Address-Hyphen}')


where quarantined_mac is defined as an integer, I get


The filter has been saved but has the following error:
Invalid SQL syntax - ORA-00905: missing keyword






So what;s wring with the sql ?




Valued Contributor I

Re: oracel clearpass sql statement

o.k. The following is accepted by clearpass when defining the Oracle SQL statement;-


declare quarantined_mac number;begin select COUNT(*) into quarantined_mac FROM sns.quarantined_macs WHERE mac = UPPER('%{Connection:Client-Mac-Address-Hyphen}');end;


Just need to check that clearpass picks up the result from the declared number




Re: oracel clearpass sql statement

Thanks for letting us know. It's always nice when someone posts an update. I'm sure it will help out others. ;)
Thank You,

--Give Kudos: found something helpful, important, or cool? Click Kudos Star in a post.

--Problem Solved? Click "Accepted Solution" in a post.
Valued Contributor I

Re: oracel clearpass sql statement

o.k. next problem and for me its due to a lack of info in the clearpass online help.


It seems that for Oracle I need to supply a service name somewhere but I don't know where I put it in the available clearpass fields and theres nothing in the online help other than "use these fields for a generic sql service"


The error message I get is


ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

- Wait a moment and try to connect a second time.

- Check which services are currently known by the listener by executing: lsnrctl services <listener name>

- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

- Check for an event in the listener.log file.



Well at least its a step forward :-))


Search Airheads
Showing results for 
Search instead for 
Did you mean: