GaussDB (Oracle-Compatible Mode)

Setting Compatibility Modes

The Oracle-compatible mode includes GaussDB Centralized (A-compatible) and GaussDB Distributed (Oracle-compatible).
If the target database type is GaussDB and the source database type is not MySQL or GoldenDB, schemas are migrated to GaussDB (Oracle-compatible) by default. Therefore, you are advised to create an Oracle-compatible GaussDB database.
Create an Oracle-compatible GaussDB database.

Log in to your GaussDB instance as a user who has the permission to create databases.
Create an Oracle-compatible GaussDB database.

Centralized:

create database databasename dbcompatibility = 'A';

Distributed:

create database databasename dbcompatibility = 'ORA';
Check whether the database is created.

select * from pg_database where datname = 'databasename';
- Centralized: If the value of datcompatibility is A, the database is created.
- Distributed: If the value of datcompatibility is ORA, the database is created.

Setting GUC Parameters

For Oracle-compatible GaussDB databases, you can set GUC parameters listed in Table 1 to enhance compatibility.

GaussDB provides many operation parameters. Configurations of these parameters affect the behavior of the database system. Before changing these parameters, learn the impact of these parameters on the database. Otherwise, unexpected results may occur.

**Table 1** GUC parameters of GaussDB (Oracle-compatible mode)
Oracle-Compatible Configuration Item	Compatibility Configuration Item	Description	Supported Database	Enabled
behavior_compat_options	display_leading_zero	Specifies how floating point numbers are displayed. 0 before the decimal point is displayed	Centralized/Distributed	Yes
	end_month_calculate	Specifies the calculation logic of the add_months function.	Centralized/Distributed	Yes
	bind_procedure_searchpath	Specifies the search path of the database object in a stored procedure for which no schema name is specified.	Centralized/Distributed	Yes
	correct_to_number	Specifies the compatibility of the to_number() result.	Centralized/Distributed	Yes
	unbind_divide_bound	Specifies the range check on the result of integer division.	Centralized/Distributed	Yes
	convert_string_digit_to_numeric	Determines whether to convert numeric constants of the character string type to those of the numeric type before these two types are compared.	Centralized/Distributed	Yes
	return_null_string	Specifies how to display the empty result (empty string '') of the lpad() and rpad() functions.	Centralized/Distributed	Yes
	merge_update_multi	Controls the UPDATE behavior if a piece of target data in the target table conflicts with multiple pieces of source data when MERGE INTO ... WHEN MATCHED THEN UPDATE and INSERT ... ON DUPLICATE KEY UPDATE are used.	Centralized/Distributed	Yes
	plstmt_implicit_savepoint	Determines whether the execution of an UPDATE statement in a stored procedure has an independent subtransaction.	Centralized/Distributed	Yes
	hide_tailing_zero	Controls configuration item for numeric display.	Centralized/Distributed	Yes
	rownum_type_compat	Specifies the ROWNUM type.	Centralized	Yes
	aformat_null_test	Specifies the logic for checking whether the row type is not null.	Centralized/Distributed	Yes
	aformat_regexp_match	Determines the matching behavior of regular expression functions.	Centralized/Distributed	Yes
	compat_cursor	Determines the compatibility behavior of implicit cursor states.	Centralized	Yes
	proc_outparam_override	Determines the overloading of output parameters of a stored procedure.	Centralized/Distributed	Yes
	proc_implicit_for_loop_variable	Controls the behavior of the FOR_LOOP query statement in a stored procedure.	Centralized	Yes
	allow_procedure_compile_check	Controls the compilation check of the SELECT and OPEN CURSOR statements in a stored procedure.	Centralized	Yes
	plsql_security_definer	Determines whether the definer permission is used by default when a stored procedure is created.	Centralized/Distributed	Yes
	plpgsql_dependency	Determines whether a function, stored procedure, or package containing undefined objects can be created.	Centralized	Yes
	sys_function_without_brackets	Calls system functions without input parameters in GaussDB (A-compatible mode) without parentheses after this parameter is enabled.	Centralized/Distributed GaussDB instance V2.0-8.200 and later	Yes
	array_count_compat	Determines whether NULL or 0 is returned by ARRAY_COUNT when the variable of the variable-length array type is NULL.	Centralized/Distributed GaussDB instance V2.0-8.200 and later	Yes
	forbid_skip_tableof_empty_str_elem	Controls the input behavior of the collection type without indexes for the character string in the format of '{1,,3}'.	Centralized/Distributed GaussDB instance V2.0-8.200 and later	Yes
	enable_use_ora_timestamptz	After it is enabled, the date and time returned by TIMESTAMP AT TIME ZONE are the same as those in an A-compatible GaussDB database.	Centralized/Distributed GaussDB instance V2.0-8.200 and later	Yes
	forbid_package_function_with_prefix	Reports an error when a function with prefixes is created in a package.	Centralized/Distributed GaussDB instance V2.0-8.200 and later	Yes
	show_full_error_lineno	Displays the line numbers starting from the CREATE statement when compiling errors are reported during stored procedure creation.	Centralized/Distributed GaussDB instance V2.0-8.200 and later	Yes
	plsql_rollback_keep_user	Determines whether ROLLBACK and ROLLBACK TO SAVEPOINT in PL/SQL changes the current user.	Centralized/Distributed GaussDB instance V2.0-8.200 and later	Yes
	current_sysdate	Returns the current OS time after SYSDATE is executed.	Centralized/Distributed GaussDB instance V2.0-8.200 and later	Yes
	select_into_return_null	Assigns a null value when STRICT is not specified and no result is returned by SELECT select_expressions INTO[STRICT] target FROM...	Centralized/Distributed GaussDB instance V2.0-8.200 and later	Yes
	time_constexpr_compact	Returns the WITH TIMEZONE or WITHOUT TIMEZONE data type based on whether a time zone is specified as a constant in a time expression.	Centralized/Distributed GaussDB instance V2.0-8.200 and later	Yes
plsql_compile_check_options	plsql_expression_check	If the plsql_expression_check parameter is enabled, the plpgsql_dependency parameter must also be enabled.	Centralized GaussDB instance V2.0-3.2	Yes
a_format_version	10c	Specifies database platform compatibility configuration items.	Centralized/Distributed	Yes
a_format_dev_version	s1	Specifies the compatible minor version of the database platform.	Centralized/Distributed GaussDB instance V2.0-3.1	Yes
	s2	Specifies the compatible minor version of the database platform.	Centralized/Distributed GaussDB instance V2.0-3.2	Yes
	s3	Specifies the compatible minor version of the database platform.	Centralized/Distributed GaussDB instance V2.0-3.3	Yes
	s4	Specifies the compatible minor version of the database platform.	Centralized/Distributed GaussDB instance V2.0-8.0	Yes
	s5	Specifies the compatible minor version of the database platform.	Centralized/Distributed GaussDB instance V2.0-8.100	Yes
	s6	Specifies the compatible minor version of the database platform.	Centralized/Distributed GaussDB instance V2.0-8.200	Yes
sql_beta_feature	a_style_coerce	Affects the decode function.	Centralized/Distributed	Yes

Set the a_format_dev_version parameter as follows:

For GaussDB V2.0-3.1 Enterprise Edition, set the value to s1.
For GaussDB V2.0-3.2 Enterprise Edition, set the value to s2.
For GaussDB V2.0-3.3 Enterprise Edition, set the value to s3.
For GaussDB V2.0-8.0 Enterprise Edition, set the value to s4.
For GaussDB V2.0-8.100 Enterprise Edition, set the value to s5.
For GaussDB V2.0-8.200 Enterprise Edition and GaussDB V2.0-9.0, set the value to s6.

Setting Parameters

For details about how to set on-premises GaussDB GUC parameters, see Setting Parameters for GaussDB Centralized and Setting Parameters for GaussDB Distributed.

Procedure

GaussDB Centralized 8.0

Connect to the GaussDB database and switch to the GaussDB installation user.

su - omm

In the preceding command, omm is an example. Change it to the actual installation user.
Run the following commands to configure the parameters:

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "behavior_compat_options='bind_procedure_searchpath,truncate_numeric_tail_zero,plsql_security_definer,proc_outparam_override,aformat_null_test,rownum_type_compat,allow_procedure_compile_check,proc_implicit_for_loop_variable,plstmt_implicit_savepoint,end_month_calculate,disable_rewrite_nesttable,plpgsql_dependency,display_leading_zero,correct_to_number,unbind_divide_bound,convert_string_digit_to_numeric,hide_tailing_zero,return_null_string,aformat_regexp_match,compat_cursor,enable_funcname_with_argsname,tableof_elem_constraints,merge_update_multi'";

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "plsql_compile_check_options='plsql_expression_check'";

gs_guc reload -Z datanode -N all -I all -c "sql_beta_feature='a_style_coerce'";

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_version='10c'";

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_dev_version='s4'";

GaussDB Distributed 8.0

Connect to the GaussDB database and switch to the GaussDB installation user.

su - omm

In the preceding command, omm is an example. Change it to the actual installation user.
Run the following command to configure the parameters:

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "behavior_compat_options='bind_procedure_searchpath,truncate_numeric_tail_zero,plsql_security_definer,proc_outparam_override,aformat_null_test,plstmt_implicit_savepoint,end_month_calculate,disable_rewrite_nesttable,display_leading_zero,correct_to_number,unbind_divide_bound,convert_string_digit_to_numeric,hide_tailing_zero,return_null_string,aformat_regexp_match,enable_funcname_with_argsname,tableof_elem_constraints,merge_update_multi'";

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_version='10c'";

gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_dev_version='s4'";

gs_guc reload -Z datanode -N all -I all -c "sql_beta_feature='a_style_coerce'";