I've recently been working with SubSonic and Oracle. One of the troubles I am dealing with is that by default, SubSonic requires your user to own the schema in order to generate classes for this. This is because when it generates code it uses the USER_* objects to generate them.
Let's say you have been tasked out with allowing your application to query an HR database for user-specific information. Your company, like most companies, is very restrictive on who has access to that HR data. So, the DBA's create a view for you with just the information you need as well as an account to use when accessing it.
Now, this presents an issue because the account you have been given does not own the schema. This puts you in a bind because you will find that when you try to generate objects with SubSonic, nothing will get generated.
#region Schema Bits
private const string MANY_TO_MANY_LIST = "SELECT b.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.table_name like '%' + :mapSuffix";
const string TABLE_COLUMN_SQL = "SELECT user, a.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.table_name = :tableName";
const string SP_PARAM_SQL = @"SELECT a.object_name, a.object_type, b.position, b.in_out,
b.argument_name, b.data_type, b.char_length, b.data_precision, b.data_scale
FROM all_objects a, all_arguments b
WHERE a.object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')
AND a.object_id = b.object_id
AND a.object_name = :objectName";
const string SP_SQL = @"SELECT a.object_name, a.object_type, a.created, a.last_ddl_time
` FROM all_objects a
WHERE a.object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE') ";
const string TABLE_SQL = "SELECT a.table_name AS Name FROM all_tables a";
const string VIEW_SQL = "SELECT a.view_name AS Name FROM all_views a";
const string INDEX_SQL = "SELECT b.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.table_name = :tableName ";
const string GET_TABLE_SQL = "SELECT b.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' ";
const string GET_FOREIGN_KEY_SQL = "SELECT d.table_name " +
" 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.column_name = :columnName " +
" AND e.table_name = :tableName ";
const string GET_PRIMARY_KEY_SQL = "SELECT 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.table_name = :tableName ";
#endregion
All we needed to change was the "From" sections to indicate that we want to query
objects. You may need to work with your DBA's in order to get this additional level of access however, it will allow you get around the issues with schema ownership in oracle.