
create table system.dual (
	dummy char(1)
);
grant select on system.dual to PUBLIC;
create synonym dual for system.dual;

insert into system.dual values ('X');

create view information_schema.tables as
select
        t_database as table_catalog,
        t_owner as table_schema,
        t_name as table_name,
        t_type as table_type
from
        genesis_tables
where(
	( t_type not like 'S')
	and
        (
                t_owner =  'PUBLIC'
        or
                t_owner = user()
        )
)
;

create view information_schema.views as
select
        v_database as table_catalog,
        v_owner as table_schema,
        v_name as table_name,
        v_text as view_definition
from
        genesis_views
where (
        (
                v_owner =  'PUBLIC'
        or
                v_owner = user() 
        )
)
;

create view information_schema.columns as
select
        c_database as table_catalog,
        c_owner as table_schema,
        c_table as table_name,
        c_name as column_name,
        c_position as ordinal_position,
        c_nulls as is_nullable,
        CASE c_type 
		WHEN 0 THEN    'INTEGER'
		WHEN 1 THEN    'CHAR'
		WHEN 2 THEN    'NUMBER'
		WHEN 12 THEN   'DATETIME'
		ELSE           'UNKNOWN'
	END DATA_TYPE ,
        c_length as character_octet_length,
        c_precision as numeric_precision,
        c_scale as numeric_scale
from
        genesis_columns
where(
        (
                c_owner =  'PUBLIC'
        or
                c_owner = user() 
        )
)
;

grant select on information_schema.tables to PUBLIC;
grant select on information_schema.views to PUBLIC;
grant select on information_schema.columns to PUBLIC;
/q
