CREATE SERVER statement - Examples for the Oracle wrapper

Use the CREATE SERVER statement to register server definitions for the Oracle wrapper. This topic provides a complete example with the required parameters, and an example with additional server options.

The following example shows you how to register a server definition for an Oracle wrapper by issuing the CREATE SERVER statement:


CREATE SERVER oraserver TYPE oracle VERSION 8.1.7 WRAPPER wrapper_name 
       OPTIONS (NODE 'paris_node') ;

oraserver: A name that you assign to the Oracle database server. Duplicate server definition names are not allowed.
TYPE oracle: Specifies the type of data source server to which you are configuring access. For the Oracle wrapper, the server type must be oracle.
VERSION 8.1.7: The version of Oracle database server that you want to access. When specifying this value, both the release number and mod number are optional for Oracle. You can enter just the version number (8).
WRAPPER wrapper_name: The wrapper name that you specified in the CREATE WRAPPER statement.
NODE 'paris_node': The name of the node where the Oracle database server resides. Obtain the node name from the tnsnames.ora file. This value is case sensitive.
Although the name of the node is specified as an option in the CREATE SERVER statement, it is required for Oracle data sources.

Server options

When you create the server definition, you can specify additional server options in the CREATE SERVER statement. These server options can be general server options and Oracle-specific server options.

The federated server assumes that all of the Oracle VARCHAR columns contain trailing blanks. If you are certain that all of the VARCHAR columns in the Oracle database do not contain trailing blanks, you can set a server option to specify that the data source use a non-blank padded VARCHAR comparison semantic.

The following example shows an Oracle server definition with the VARCHAR_NO_TRAILING_BLANKS server option:


CREATE SERVER oraserver TYPE oracle VERSION 8.1.7 WRAPPER wrapper_name 
       OPTIONS (NODE 'paris_node', VARCHAR_NO_TRAILING_BLANKS 'Y') ;

Use the VARCHAR_NO_TRAILING_BLANKS server option when none of the columns contains trailing blanks. If only some of the VARCHAR columns contain trailing blanks, you can set an option on those columns with the ALTER NICKNAME statement.