Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\browserasst\owbb\create_apps

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\browserasst\owbb\create_apps_ddl.sql

begin

declare
	procedure exec(p_stmt in varchar2)
	is
	begin
		execute immediate p_stmt;
	exception
		when others then
      dbms_output.put_line('===>' ||sqlcode || ' ' || sqlerrm);
      dbms_output.put_line('    ' || substr(p_stmt, 1, 250));
	end;
begin

--|
		--|(for repositories.xml)
		--|
		--|Multiple repositories.xml files can be loaded into owbb_REPOS_TYPES;
		--|Validation Rules are reuseable among applcaitions since it is uniquely 
		--|based on repository type.
		--|
/* lmm		
	exec('create table owbb_repos_types (
		repos_type varchar2(128) not null,
		repos_version varchar2(128) not null,

validation_proc varchar2(4000) not null,
		display_name varchar2(256) not null,
		nls_id varchar2(256),
		icon_file varchar2(256),

primary key (repos_type, repos_version)
		)
	');
*/

--|
		--|(for roles.xml)
		--|
/* lmm		
	exec('create table owbb_roles (
		role_name varchar2(128) not null,
		display_name varchar2(128) not null,
		nls_id varchar2(256),
		primary key (role_name)
		)
	');
*/
		--|
		--|(for application.xml: Part 1)
		--|
/* lmm		
	exec('create sequence owbb_app_seq start with 10 increment by 1');

exec('create table owbb_applications (
		application_id number(38) not null,
		release varchar2(128) not null,
		release_date date not null,
		name varchar2(256) not null,
		nls_id varchar2(256),

repos_type varchar2(128) not null,

primary key (application_id),
		unique (repos_type)
		)
	');
*/

/* lmm		
	exec('create table owbb_app_repos_versions (
		application_id number(38) not null,

repos_type varchar2(128) not null,
		repos_version varchar2(128) not null,

unique (application_id, repos_type, repos_version),
		foreign key (application_id) references owbb_applications (application_id) 
		)
	');

exec('create table owbb_app_renderers (
		application_id number(38) not null,
		repos_type varchar2(128) not null,
	
		role_name varchar2(128) not null,
		renderer_type varchar2(128),
		start_page varchar2(256),
		resource_bundle varchar2(256),

unique (repos_type, role_name, renderer_type), 
		foreign key (application_id) references owbb_applications (application_id), 
		foreign key (role_name) references owbb_roles (role_name)
		)
	');
*/

--|
		--|(for types.xml)
		--|
/*
	exec('create table owbb_types (
		application_id number(38) not null,
		repos_type varchar2(128) not null,
		repos_version varchar2(128) not null,

type_name varchar2(128) not null,
		display_name varchar2(256) not null,
		nls_id varchar2(256),
		primary key (application_id, repos_type, repos_version, type_name),
		foreign key (application_id) references owbb_applications (application_id)
		)
	');

exec('create table owbb_type_aliases (
		application_id number(38) not null,
		type_name varchar2(128) not null,
		alias_name varchar2(128) not null,

primary key (application_id, type_name, alias_name),
		foreign key (application_id) references owbb_applications (application_id) 
		)
	');

exec('create table owbb_type_bindings (
		application_id number(38) not null,
		repos_type varchar2(128) not null,
		repos_version varchar2(128) not null,

type_name varchar2(128) not null,
		call varchar2(256) not null,
		renderer varchar2(256) not null,

role_name varchar2(128) not null,
		data varchar2(4000) not null,

unique(application_id, repos_version, type_name, call, role_name),
		foreign key (application_id, repos_type, repos_version, type_name) 
			references owbb_types (application_id, repos_type, repos_version, type_name),
		foreign key (role_name) references owbb_roles (role_name)
		)
	');

exec('create table owbb_type_icons (
		application_id number(38) not null,
		repos_type varchar2(128) not null,
		repos_version varchar2(128) not null,

type_name varchar2(128) not null,
		icon_size varchar2(128) not null,
		icon_file varchar2(256) not null,
		unique (application_id, repos_type, repos_version, type_name, icon_size),
		foreign key (application_id, repos_type, repos_version, type_name) 
			references owbb_types (application_id, repos_type, repos_version, type_name)
		)
	');

exec('create table owbb_types ' ||
	'( ' ||
	'	type_name varchar2(64) not null, ' ||
	'	image_name varchar2(64), ' ||
	'	display_name varchar2(128) not null, ' ||
	'	primary key (type_name) ' ||
	')');

exec('create table owbb_type_aliases ' ||
	'( ' ||
	'	alias_name varchar2(128) not null, ' ||
	'	type_name varchar2(64) not null, ' ||
	'	primary key (alias_name), ' ||
	'	foreign key (type_name) references owbb_types (type_name) ' ||
	')');

exec('create table owbb_queries ' ||
	'( ' ||
	'	query_id number(38) not null, ' ||
	'	type_name varchar2(64) not null, ' ||
	'	repository_version varchar2(16) not null, ' ||
	'	usage varchar2(2) check (usage in (''PA'', ''CO'', ''RE'', ''PR'', ''DE'', ''NA'', ''US'', ''DP'')) not null, ' ||
	'	query_text clob, ' ||
	'	primary key (query_id) ' ||
	')');

exec('create table owbb_design_queries ' ||
	'( ' ||
	'	query_id number(38) not null, ' ||
	'	report_category varchar2(64) not null, ' ||
	'	repository_version varchar2(16) not null, ' ||
	'	usage varchar2(2) check (usage in (''HD'', ''PP'', ''PQ'', ''TT'', ''TC'', ''TW'', ''TS'', ''TQ'')) not null, ' ||
	'	sequence number(9) not null, ' ||
	'	sub_sequence number(9) not null, ' ||
	'	query_text varchar2(4000), ' ||
	'	primary key (query_id) ' ||
	')');

/* lmm		
	exec('create table owbb_role_queries ' ||
	'( ' ||
	'	query_id number(38) not null, ' ||
	'	role_name varchar2(64) not null, ' ||
	'	primary key (query_id, role_name), ' ||
	'	foreign key (query_id) references owbb_queries (query_id), ' ||
	'	foreign key (role_name) references owbb_roles (role_name) ' ||
	')');	
*/

exec('create table owbb_images ' ||
	'( ' ||
	'	image_name varchar2(64) not null, ' ||
	'	size_category varchar2(1) check (size_category in (''L'', ''M'', ''S'')) not null, ' ||
	'	gif_file varchar2(1000) not null, ' ||
	'	primary key (image_name, size_category) ' ||
	')');

--|
		--|(for reports.xml)
		--|

exec('create sequence owbb_report_seq start with 1 increment by 1');

/*
	exec('create table owbb_reports (
		report_id number(38) not null,

application_id number(38) not null,
		repos_type varchar2(128) not null,
		repos_version varchar2(128) not null,

role_name varchar2(128) not null,
		type_name varchar2(128) not null,

display_name varchar2(256) not null,
		nls_id varchar2(256),
		helper varchar2(128) not null,
		category varchar2(128),
		alt_dispatch varchar2(3) check (alt_dispatch in (''OPA'')),

primary key (report_id),
		unique (repos_type, repos_version, role_name, type_name, display_name),
		unique (repos_type, repos_version, role_name, type_name, helper, category),
		foreign key (application_id, repos_type, repos_version, type_name) 
			references owbb_types (application_id, repos_type, repos_version, type_name)
		)
	');
*/
	exec('create table owbb_reports (
		report_id number(38) not null, 
		type_name varchar2(64) not null, 
		report_display_name varchar2(100) not null, 
		repository_version varchar2(16) not null,
		report_helper varchar2(128) not null,
		report_category varchar2(64),
		dblink_name varchar2(128), 
		alt_dispatch varchar2(3) check (alt_dispatch in (''OPA'')), 
		primary key (report_id), 
		unique (type_name, report_display_name), 
		unique (type_name, report_helper, report_category), 
		foreign key (type_name) references owbb_types (type_name) 
		)
	');

exec('create table owbb_role_reports ' ||
	'( ' ||
	'	report_id number(38) not null, ' ||
	'	role_name varchar2(64) not null, ' ||
	'	primary key (report_id, role_name), ' ||
	'	foreign key (report_id) references owbb_reports (report_id), ' ||
	'	foreign key (role_name) references owbb_roles (role_name) ' ||
	')');

/*
	exec('create table tmp_owbb ' ||
	'( ' ||
	'	query_text clob ' ||
	')');	
*/

end;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de