SQL Error ORA-00906 When Setting Up Oracle Database to Log TestStand Results

Issue Details

We want to log our TestStand results into an Oracle Database. To set up the database we try to use the SQL script created by TestStand using TestStand's default SQL schema called "Oracle (NI)". Several commands of this script fail, giving the following error message:
Executed with Errors
ORA-00906: missing left parenthesis

How can we solve this issue and connect TestStand to Oracle Database?




 

Solution

NI TestStand's SQL scripts are meant to provide you with a default database schema for the respective database system you want to use. There are two different types of scripts available with TestStand you could use to set up your database structure:

• Using one of the shipped pre-built SQL scripts available in C:\Program Files (x86)\National Instruments\TestStand <version>\Components\Models\TestStandModels\Database\ (Note: change <version> to the version of TestStand you are using). The script you can use to set up the database structure in Oracle is Oracle Create Result Tables.sql.
• Use TestStand's Schema Builder, after setting up the database connection (menu Configure » Result Processing... » Database Options » Data Link Options). To build the SQL script, use the following steps:

1. Go to Configure » Result Processing... » Database Options » Schemas

2. Select the schema you want to use (e.g. Oracle (NI) regarding the issue described above).
3. Click Validate....

4. Select Add Missing Items and click Generate SQL to generate the SQL commands to add the missing tables. Note: This might generate multiple commands for one table, in case there are multiple variants defined in the selected schema.

5. Database Viewer opens and shows the generated SQL script. In general, SQL scripts generated from TestStand's default schemas are meant to get you as close as possible to a valid SQL script setting up a database schema for the respective database system you want to use. However, as the database schema needed depends on the specific application you want to build, and as there are minor differences between different database types and versions, there is manual work needed. Regarding e.g. the default schema Oracle (NI) for Oracle Database, taken from the issue described above, three changes have to be considered:
   1. Select one of the multiple commands generated for creating the table PROP_ANALOGWAVEFORM. This is explained in TestStand's popup as well as at the beginning of the generated SQL script.

2. The generated SQL commands refer to a general data type RAW for storing data. This data type needs to be changed in regards to the intended use of the data columns. Some possible options for e.g. Oracle databases as described in the issue described above are: BLOB, RAW(<size>), LONG RAW. Find more about the built-in data types of Oracle databases e.g. here.

3. In case it is needed for your intended use: Extend the SQL script respectively the database with additional tables.​

6. Execute the script when ready.

7. Check for execution errors, reload the database structure and verify it. If necessary, change the script, and execute it again.

8. Switch back to TestStand's Schema Validation window, click Revalidate, check the result, and click Done if no error messages are shown:

Additional Information

Oracle recommends using the BLOB data type instead of LONG RAW, as BLOB is subject to far fewer restrictions than any LONG data type. Find more information in this external article: Data Types - Database SQL Language Reference (Oracle Database 11g Release 2)