Environment
Situation
This technical note describes two issues that can occur with the DATABridge Oracle Client, how DATABridge 6.2 SP2 Client handles these issues, and the implications of the changes for new and existing customers.
Background
In DATABridge Client 6.2 SP1 and earlier, running the Oracle Client on a non-English database required writing session scripts to compensate for languages that use the comma as the decimal character. Failure to do so resulted in SQL errors caused by periods in the data for numeric columns where a comma was expected. Similarly, SQL*Loader records that contained data with commas in numeric columns resulted in errors, which caused the records to be discarded. This problem was solved by setting the bcp_decimal_character to a comma. However, this solution conflicted with the bcp_delim character, which then had to be set to something else (like a vertical bar [|]).
A second problem occurred when using the Client with Oracle databases that use the AL32UTF8 character set. Some characters were translated to two-character sequences when they were loaded into the database. This sometimes resulted in truncation errors, as the column width was assigned assuming that all characters would be single bytes. The solution for this problem was also to write a session script to change the NLS_LENGTH_SEMANTICS parameter to CHAR, which affects how a create table statement interprets the length of a CHAR or VARCHAR2 column.
Beginning with DATABridge 6.2 SP 2 Client both issues are addressed by reading the NLS parameters from the Oracle database and automatically doing whatever needs to be done to make the Client work in the environment defined by the NLS parameters.
Resolution
Implications of These Changes
If you are a new customer, no action is required to run the Client when the language is any other than U.S. English or when the character set is AL32UTF8.
If you already have session scripts that perform only the aforementioned actions and do not do anything else, delete them. If you leave them in the scripts directory, they will be run instead of the Client performing the equivalent actions on its own. For a non U.S. English language the Client alters the session to set the NLS_LANGUAGE parameter to AMERICAN and the NLS_TERITORY parameter to AMERICA. For a UTF8 database it alters the session and sets the NLS_LENGTH_SEMANTICS parameter to CHAR (instead of BYTE).
If you use the Client Configurator to create a new data source, the affected parameters are automatically adjusted for you. However, if you use command line client dbutility, the client will verify that these parameters are set up correctly or issue an error message asking you to correct their values.
Writing Session Scripts
If you need to write a session script, place the file in the user script directory specified in the Client configuration file and name it script.user.session.
In the case of non U.S. English languages that use the comma as the decimal character, you must include the following statement in this script:
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN' NLS_TERRITORY = 'AMERICA'If the script contains multiple SQL statements, they must be separated by a line that contains the sequence:
/***/where the first slash is in column 1 and there are no intervening spaces in the string. You can add a comment to this line, by adding a space to end of the line followed by two slashes (//) and then following that with the comment.
If your database uses the AL32UTF8 character set, you must include the following SQL statement in the script:
ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR' The next time the Client runs, it will find the script file user.session.script and run the ALTER SESSION statements.
SQL*Loader Parameters
Provided the SQL*Loader parameters are set up correctly, the changes provided in the Client 6.2 SP 2 are transparent and require no changes.
If you use the Client Configurator, SQL*Loader parameters will be automatically changed. However, you will need to reclone any data set that has numeric data that has a fractional part (that is, DMSII NUMBER (m,n) and REAL(m,n) or REAL).
If needed, you can modify the SQL*Loader parameters in the Client Configuration by using either the Client Console or the text configuration file (dbridge.cfg).
Modifying the SQL*Loader Parameters
To modify the SQL*Loader parameters in the Client Console:
- From the Client Console, right-click the data source and choose Client Configuration.
- From the left panel of the Client Configuration dialog box, click Bulk Loader and make the following changes:
- For Delimiter, enter the pipe symbol ( | ). (You can use a different symbol, provided it isn't a number, a decimal character, a comma, a plus or minus character, or a quotation mark; however, the pipe symbol tends to be most readable.)
- Select the check box "Use comma for decimal character," and then click OK.
To modify the SQL*Loader parameters in a text Client configuration file, modify the following two lines in the [bulk_loader] section, as follows:
bcp_decimal_char = ","bcp_delim = ”|” Note: If you use the command line client and have a session script for the data source in the user scripts directory, the client will run that script instead. The command line client will verify the SQL*Loader parameter, but will not make any corrections to it. Instead, it will issue an error.
For more information about bulk loader settings, see Appendix C: Client Configuration in the DATABridge Client Administrator's Guide, available from https://support.microfocus.com/manuals/databridge.html.