How to resolve error "Oracle bulk load failed, error code : 01" when using the Statistica's "Write Spreadsheet to Database" node
Article ID: KB0070361
Updated On:
| Products | Versions |
|---|---|
| Spotfire Statistica | 14.0.1 and higher |
Description
When running the "Write Spreadsheet to Database" node in a Statistica workspace using an Oracle database connection, the following error will occur when the client password contains a special character such as @!&%)(" :
To determine the exact error, follow the steps below:
Standard error: LRM-0016: syntax error at ')'
To determine the exact error, follow the steps below:
- Create a directory c:\sqlloader_test
- Extract sqlloader_test.zip to c:\sqlloader_test (sqlloader_test.zip is attached at the bottom of this article)
- Edit run_sqlloader_test.bat. Replace the USERID with the user name, password and "Data Source" from the oracle connect string. Use the format USER/PASSWORD@DATASOURCE
- Open powershell or command line. Go to c:\sqlloader_test and run the file "run_sqlloader_test.bat"
Standard error: LRM-0016: syntax error at ')'
Issue/Introduction
"Oracle bulk load failed, error code : 01" may be caused by a special character in the password for Oracle client on Windows.
Environment
OS: Windows
Database: Oracle
Resolution
Replace any special character with a # (pound or hashtag) character in the Oracle password.
When using a password with special characters with a Oracle client on Windows, the only special character that does not need to be escaped is the # (pound or hash character).
Note: Escaping is defined as needing a character that invokes an alternative interpretation on the following character in a string. For example, a reserved character is a character like the double quote or ". It is typically used at the start and ending of a string, for instance, "Spotfire Statistica." But if a a double quote is to be used like this "Robert "BOB" Doe", which should be read Robert "BOB" Doe, this would cause an error. To resolve this, the escape character, double quote ("), is used to render the correct character, like this: "Robert ""BOB"" Doe" The double quote is escaped by the double quote character.
When using a password with special characters with a Oracle client on Windows, the only special character that does not need to be escaped is the # (pound or hash character).
Note: Escaping is defined as needing a character that invokes an alternative interpretation on the following character in a string. For example, a reserved character is a character like the double quote or ". It is typically used at the start and ending of a string, for instance, "Spotfire Statistica." But if a a double quote is to be used like this "Robert "BOB" Doe", which should be read Robert "BOB" Doe, this would cause an error. To resolve this, the escape character, double quote ("), is used to render the correct character, like this: "Robert ""BOB"" Doe" The double quote is escaped by the double quote character.
Additional Information
Windows special characters that need escaping in Oracle (which does not include the # character):
https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/cepma/special_chars_pwd.html#GUID-182438E9-10E8-4B1D-AEE7-D8EB39D7036C
Special Characters and Reserved words: https://docs.oracle.com/en/industries/health-sciences/life-sciences-data-hub/3.0/app-dev-guide/special-characters-and-reserved-words.html
https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/cepma/special_chars_pwd.html#GUID-182438E9-10E8-4B1D-AEE7-D8EB39D7036C
Special Characters and Reserved words: https://docs.oracle.com/en/industries/health-sciences/life-sciences-data-hub/3.0/app-dev-guide/special-characters-and-reserved-words.html
Attachments
Feedback
Yes
No
Powered by
