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.

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' );

The SQL_TEXT and SQL_BIND values that are returned are:

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.

    test mdsys.sdo_elem_info_array;
    anytest sys.anydata;
    rc pls_integer;
    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');
        DBMS_OUTPUT.PUT_LINE('Null value');
    end if;
  • The documentation says "for an array, the values recorded are only the last set of bind values" – Philᵀᴹ Dec 6 '13 at 12:20
  • @Phil, is that when you bind an array to a single row or when you bind an array to a statement for multiple execution? For example, what I have above vs EXECUTE IMMEDIATE 'UPDATE bulk_collect_test SET object_id = object_id WHERE object_id = :1' USING l_tab(i); – travis Dec 6 '13 at 18:37
  • 1
    Could you try selecting v$sql_monitor.binds_xml from v$sql_monitor while someone is running it? sql_monitor doesn't capture every sql but when it does, it usually has pretty good capture of binds. Varrays are type struct, so we don't know how internally oracle will represent them. dba_hist_sqlbind is not useful since not all executions have their binds captured (it is by design). You might want to log a SR with oracle support and maybe they can help you. – Raj Dec 10 '13 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.