I have an application, and would like to use Oracle's FGA (Fine Grained-Auditing) to record varray bind information. Specifically, I am using Oracle Spatial and need to know at what extent users are actually querying the data. By default, the only value that is recorded by Oracle for varrays appears to be -1.
begin dbms_FGA.add_policy ( object_schema => 'GDB', object_name => 'COUNTRY', policy_name => 'COUNTRY_Aud', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types => 'SELECT' ); end; /
The SQL_TEXT and SQL_BIND values that are returned are:
SELECT SHAPE FROM GDB.COUNTRY WHERE mdsys.sdo_filter(GDB.COUNTRYS.SHAPE, MDSYS.SDO_GEOMETRY(:gtype1,:srid1,NULL,:elem_info1,:ordinates1), 'querytype=window') = 'TRUE' #1(4):2003 #2(4):4326 #3(-1): #4(-1):
I know that I could create a procedure to handle the auditing, but I don't know how to retrieve the actual varray.
I have tried getting the values out of the DBA_HIST_SQLBIND view, but the varray values are always null.
declare test mdsys.sdo_elem_info_array; anytest sys.anydata; rc pls_integer; begin select VALUE_ANYDATA into anytest from DBA_HIST_SQLBIND where SQL_ID='9vu11pguf5gx9' and datatype = 123 and rownum = 1; if(anytest is not null) then rc := anytest.getcollection(test); DBMS_OUTPUT.PUT_LINE('Got it'); else DBMS_OUTPUT.PUT_LINE('Null value'); end if; end; /