Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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;
share|improve this question
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
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 posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.