RB_* Tables for Oracle

rbuser

Hi,

We are using reportbuilder and I found the concept of creating th RB_Table,
RB_Field and
RB_Join tables and filling them with data to be too stupid (as Oracle
already has a Data Dictionary). Therefore I created the following scripts
which could be useful to somebody:

CREATE OR REPLACE VIEW RB_TABLE AS
Select Table_Name, Table_Name Table_Alias from User_Tables Union ALL Select
View_Name Table_Name, View_Name Table_Alias from User_Views

CREATE OR REPLACE VIEW RB_FIELD AS
Select
Table_Name, Column_Name Field_Name, Column_Name Field_Alias,
Case When Data_Type in ('NUMBER', 'FLOAT') Then 'dtDouble' When Data_Type in
('CLOB', 'BLOB', 'LONG') Then 'dtBLOB' When Data_Type = 'DATE' Then
'dtDateTime' Else 'dtString' End as DataType,
Case When Data_Type in ('CLOB', 'BLOB', 'LONG') Then 'F' Else 'T' End as
Searchable,
Case When Data_Type in ('CLOB', 'BLOB', 'LONG') Then 'F' Else 'T' End as
Sortable,
'F' AUTOSEARCH, 'F' MANDATORY, 'T' Selectable
from
User_Tab_Columns

Create Materialized View RB_JOIN AS
select
q.Table_Name Table_Name1,
u.Table_Name Table_Name2,
'dajtInner' Join_Type,
q.Column_Name Field_Names1,
'=' Operators,
u.Column_Name Field_Names2
from
user_constraints r, user_Cons_Columns q, user_cons_columns u
where
r.constraint_type='R' and
r.r_constraint_name = q.Constraint_Name and
r.constraint_name = u.constraint_name and
q.POSITION = u.position;