It is possibile to personalize standard queries used by Formwork to read database metadata such as table names, column names, indexes and all information required by Formwork to work properly.
The following 8 queries can be overridden:
| Name | Purpose | Default value |
| ORACLE_TABLE_SQL | Read all tables names | SELECT a.owner || '.' || a.table_name AS Name
FROM all_tables a WHERE a.owner not in ('CTXSYS','SYS','EXFSYS','MDSYS','ORDSYS','SYSTEM','WKSYS','WMSYS', 'APEX_030200') |
| ORACLE_VIEW_SQL | Read all view names |
SELECT a.owner || '.' || a.view_name AS Name
FROM all_views a WHERE a.owner not in ('CTXSYS','SYS','EXFSYS','MDSYS','ORDSYS','SYSTEM','WKSYS','WMSYS', 'APEX_030200') |
| ORACLE_INDEX_SQL | Read all indices of a table | SELECT b.owner || '.' || b.table_name as table_name, b.column_name,
DECODE (a.constraint_type, 'R', 'FOREIGN KEY', 'P', 'PRIMARY KEY', 'UNKNOWN' ) constraint_type FROM all_constraints a, all_cons_columns b WHERE a.constraint_name = b.constraint_name AND a.constraint_type IN ('R', 'P') AND b.owner || '.' || b.table_name = :tableName |
| ORACLE_TABLE_COLUMN_SQL | Read all columns of a table | SELECT user, a.owner || '.' || a.table_name as table_name, a.column_name, a.column_id, a.data_default,
a.nullable, a.data_type, a.char_length, a.data_precision, a.data_scale FROM all_tab_columns a WHERE a.owner not in ('CTXSYS','SYS','EXFSYS','MDSYS','ORDSYS','SYSTEM','WKSYS','WMSYS', 'APEX_030200') AND a.owner || '.' || a.table_name = :tableName |
| ORACLE_MANY_TO_MANY_LIST | Read all many-to-many relationships | SELECT b.owner || '.' || b.table_name as table_name
FROM all_constraints a, all_cons_columns b WHERE a.table_name = :tableName AND a.constraint_type = 'R' AND a.r_constraint_name = b.constraint_name AND b.owner || '.' || b.table_name like '%' + :mapSuffix |
| ORACLE_GET_TABLE_SQL | Extract constraints from a column | SELECT b.owner || '.' || b.table_name as table_name
FROM all_constraints a, all_cons_columns b WHERE a.constraint_name = b.constraint_name AND a.constraint_type IN ('R', 'P') AND b.column_name = :columnName AND a.constraint_type = 'P' |
| ORACLE_GET_PRIMARY_KEY_SQL | Extract primary keys from a table | SELECT e.owner || '.' || e.table_name AS TableName, c.column_name AS ColumnName
FROM all_cons_columns c, all_cons_columns d, all_constraints e WHERE d.constraint_name = e.r_constraint_name AND c.constraint_name = e.constraint_name AND d.owner || '.' || d.table_name = :tableName |
| ORACLE_GET_FOREIGN_KEY_SQL | Extract foreign keys from a table | SELECT d.owner || '.' || d.table_name as table_name
FROM all_cons_columns c, all_constraints d, all_constraints e WHERE d.constraint_name = e.r_constraint_name AND c.constraint_name = e.constraint_name AND c.column_name = :columnName AND e.owner || '.' || e.table_name = :tableName |
In order to override these queries, edit web.config file (which is an xml file) on your server root folder, and add a new descending of <configuration><appSettings> element using the structure:
<add key="QUERY_NAME" value="QUERY_VALUE" />
where
- QUERY_NAME is the name of the previous query shown
- QUERY_VALUE is the new custom query you want Formwork to perform.