/* +------------------------------------------------------------------------ */
/* | DESCRIPTION:                                                            */
/* |    The script version-disables all accessible tables in the current     */
/* |    data
 store.                                                          */ 
/* |                                                                         */
/* | CALL:                                                                   */
/* |    DisableVersioning;                                                   */
/* |                                                                         */
/* | PARAMETER:                                                              */
/* |    None.                                                                */
/* |                                                                         */
/* | NOTE:                                                                   */
/* |    Before executing the script the following should be noted:           */
/* |       - It is recommended to always connect to the Oracle user (FDO     */
/* |         data store) to be processed directly. Ensure that this user has */
/* |         been granted the Workspace Manager role WM_ADMIN_ROLE.          */
/* |       - It should be ensured that the Oracle user to be processed is    */
/* |         not currently used by any user or can be accessed by any user   */
/* |         during the execution of the script as this may result in the    */
/* |         failure of the script due – for example – session conflicts.    */
/* |       - It is recommended that the script output is saved to a log file.*/
/* |         The saved information can help to resolve any issues the script */
/* |         may encounter.                                                  */
/* |                                                                         */
/* |    The script will issue an exception if …                              */
/* |       … the Oracle user does not contain a FDO Meta Schema. For such an */
/* |         Oracle user, it is up to the user to version-enable the tables. */
/* |         However, the script can be used as a guideline to implement the */
/* |         task.                                                           */
/* |       … a preemptive check reveals the presence of conditional data in  */
/* |         any of the version-enabled tables. To resolve such an issue and */
/* |         version-disable the tables, the conditional data has to be      */
/* |         either committed or rolled back first. This can be achieved via */
/* |         the appropriate tools in Map or by using the corresponding      */
/* |         Workspace Manager APIs.                                         */
/* |                                                                         */
/* | IMPORTANT                                                               */
/* |    If it is ensured that the user invoking the script has the necessary */
/* |    privileges and is the only user accessing the Oracle user being pro- */
/* |    cessed no error other than the described exception should be encoun- */
/* |    tered. However, if the script documents an error, the Workspace      */
/* |    Manager documentation should be consulted first before attempting    */
/* |    any action to resolve the issue as a not well-founded action may     */
/* |    have severe consequences on the usability of the data in the Oracle  */
/* |    user.                                                                */
/* +------------------------------------------------------------------------ */

set serveroutput on size 50000

declare
	/* ------------------------------------------------------------------ */
	/* Declare the cursors needed to execute the request:                 */
	/*   chk_f_schema_cr:                                                 */
	/*     Cursor to check whether or not the FDO Meta Schema exists.     */
	/*   cond_data_cr:                                                    */
	/*     Cursor to check whether or not the schema contains conditional */
	/*     data.                                                          */
	/*   disable_tab_cr:                                                  */
	/*     Cursor to select all accessible tables to be version-disabled. */
	/* ------------------------------------------------------------------ */

	cursor chk_f_schema_cr is
		select object_name from user_objects
		  where object_name in ('F_CLASSDEFINITION', 'F_OPTIONS');


	cursor cond_data_cr is
		select table_name, workspace from user_wm_modified_tables
                  where workspace <> 'LIVE' and
                        table_name in (select table_name from user_wm_versioned_tables);

	cursor disable_tab_cr is
		select table_name from user_wm_versioned_tables;

	/* ------------------------------------------------------------------ */
	/* Declare some other required variables:                             */

	/* ------------------------------------------------------------------ */

	conditional_data_count	number;
	fdo_meta_schema_count	number;

	/* ------------------------------------------------------------------ */
	/* Declare necessary exceptions.                                      */

	/* ------------------------------------------------------------------ */

	cond_data_ex_exp	exception;
	fdo_meta_schema_exp	exception;

begin
	dbms_output.put_line('Version-disabling accessible tables');

	/* ------------------------------------------------------------------ */
	/* Check if the data store contains the FDO Meta Schema. If the FDO   */
	/* Meta Schema is not present issue an exception and terminate the    */
	/* script.                                                            */
	/* ------------------------------------------------------------------ */

	dbms_output.put_line(' ... Checking for FDO Meta Schema');
	fdo_meta_schema_count := 0;	
	for chk_f_schema_rec in chk_f_schema_cr
	loop
		fdo_meta_schema_count := fdo_meta_schema_count + 1;
	end loop;
	if fdo_meta_schema_count <> 2
	then
		raise fdo_meta_schema_exp;
	end if;

	/* ------------------------------------------------------------------ */
	/* Check whether or not there is conditional data. If there is, the   */
	/* script cannot be executed until the user has resolved this issue.  */
	/* ------------------------------------------------------------------ */

	dbms_output.put_line(' ... Checking for conditional data');
	conditional_data_count := 0;	
	for cond_data_rec in cond_data_cr
	loop
		conditional_data_count := conditional_data_count + 1;
		dbms_output.put_line(' ......> Table ' || cond_data_rec.table_name || ' in workspace ' || cond_data_rec.workspace);
	end loop;
	if conditional_data_count > 0
	then
		raise cond_data_ex_exp;
	end if;

	/* ------------------------------------------------------------------ */
	/* Query the list of version-enabled tables and version-disable each  */
	/* of them.                                                           */
	/* ------------------------------------------------------------------ */

	dbms_output.put_line(' ... Version-disabling accessible tables');
	for disable_tab_rec in disable_tab_cr
	loop
		dbms_output.put_line(' ......> version-disabling class table ' || disable_tab_rec.table_name);
		dbms_wm.DisableVersioning(disable_tab_rec.table_name, TRUE, TRUE);
	end loop;

	/* ------------------------------------------------------------------ */
	/* Update the corresponding entries in the table F_Options.           */
	/* ------------------------------------------------------------------ */

	dbms_output.put_line(' ... Updating table F_OPTIONS');
	update F_OPTIONS set value = '0' where name = 'LT_MODE' or name = 'LOCKING_MODE';
	commit;

	dbms_output.put_line('Version-disabling of accessible class tables finished');

exception
	/* ------------------------------------------------------------------ */
	/* Handle all exceptions.                                             */
	/* ------------------------------------------------------------------ */
	when cond_data_ex_exp
	then
		dbms_output.put_line(' ----------------------- EXECUTION ERROR ----------------------- ');
		dbms_output.put_line(' >>> Data store contains conditional data; cannot execute request');
 		dbms_output.put_line(' >>> to version-disable tables until conditional data has been');
 		dbms_output.put_line(' >>> rolled back or committed');
		dbms_output.put_line(' >>> Version-disabling of accessible class tables terminated');

	when fdo_meta_schema_exp
	then
		dbms_output.put_line(' ----------------------- EXECUTION ERROR ----------------------- ');
		dbms_output.put_line(' >>> Data store does not contain FDO Meta Schema; cannot execute');
 		dbms_output.put_line(' >>> request to version-enable tables.');
		dbms_output.put_line(' >>> Version-enabling of accessible tables terminated');
		
end;
/
