3.1.2.5 Configuring SQL Preferences

When displaying tables and objects in Database reference or target, GGSA by default only shows the objects that are owned by the user. If you want the system to display all objects the user has access to, regardless of the owner, enter your own SQL query here.

To set your SQL preferences:

1. Click the user name in the top right corner of the screen and Select System Settings from the drop-down list.
2. Click SQL Queries Configurations, and set the following configurations:
   • Oracle DB Reference and Target:

     Enter a SQL query to fetch the table column details, for all the tables to be used for creating references or targets. The SELECT query must contain the following attributes for each column: table_name, column_name, data_type, data_length, data_precision, data_scale, nullable and data_default. WHERE statements can be used to filter the tables listed.

     Example: SELECT table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, data_default FROM all_tab_columns WHERE table_name LIKE DEMO ORDER BY table_name. In this example all the tables with names starting with DEMO will be displayed when using references or targets.

   • Oracle DB Geofence: Enter the query for geofence tables and columns details.

     Enter a SQL query to fetch the table column details, for all the tables to be used for creating Geofences. The SELECT query must contain the following attributes for each column: table_name, column_name, data_type, data_length, data_precision, data_scale. The tables listed in for geofence must have at least one column of the SDO_GEOMETRY type. Hence the following where clause is mandatory: WHERE table_name IN (SELECT TABLE_NAME FROM all_tab_columns WHERE data_type='SDO_GEOMETRY'.

     Example: SELECT table_name, column_name, data_type, data_length, data_precision, data_scale FROM all_tab_columns WHERE table_name IN (SELECT TABLE_NAME FROM all_tab_columns WHERE data_type='SDO_GEOMETRY') AND table_name LIKE GEO ORDER BY table_name desc. In this example, all the tables with names starting with GEO will be listed in the descending order of the table name.

3. Click Save.