Skip to main content

[Collect] Oracle System Columns Non-Query Statement Error

Caroline St.John
  • Updated

Oracle System Columns are Producing "The service was unable to evaluate the non-query statement" Error

When building tables using Oracle sources, sometimes the DSP® produces an error that tells the user the service cannot evaluate the non-query statement due to a violation of a PRIMARY KEY constraint.

This article contains the following:

Issue

Some users get the following message when attempting to build tables for Oracle sources:

ERROR! Insert into <column> Failed.

The service was unable to evaluate the non-query statement. 
Violation of PRIMARY KEY constraint

The statement has been terminated.

Reason for Error

The DSP is attempting to extract schema information from Oracle system columns that get created by certain table elements, such as Indexes.  

Resolution 

Ignore these columns by updating the Download SQL value on the Vertical View of the ODBCORACLE Connection Type.

To update the Download SQL value:

  1. Click Collect > Administrative > Connection Types in the Navigation pane.
  2. Click the Sources icon for the SQLSERVER Connection Type.
  3. Click Vertical View for the ODBCORACLE Source Connection Type.
  4. Click Edit.
  5. Replace the syntax in the Download SQL text area from:

    SELECT '#SOURCE#' AS Source, TABLE_NAME AS TableName, OWNER AS SchemaOWNER, SEGMENT_COLUMN_ID AS COLUMNORDER, COLUMN_NAME AS ColumnName, DATA_TYPE AS DataType, CHAR_LENGTH AS DataLength, DATA_PRECISION AS DataPrecision, DATA_SCALE AS DataScale, TO_CHAR(DECODE( NULLABLE, 'N', '0', '1')) as DATANULLABLE FROM SYS.ALL_TAB_COLS WHERE OWNER = '#SOURCEDBO#' and TABLE_NAME = UPPER('#SOURCETABLE#') ORDER BY SEGMENT_COLUMN_ID asc, 1 ASC, 2 ASC, 3 ASC

    to:

    SELECT '#SOURCE#' AS Source, TABLE_NAME AS TableName, OWNER AS SchemaOWNER, SEGMENT_COLUMN_ID AS COLUMNORDER, COLUMN_NAME AS ColumnName, DATA_TYPE AS DataType, CHAR_LENGTH AS DataLength, DATA_PRECISION AS DataPrecision, DATA_SCALE AS DataScale, TO_CHAR(DECODE( NULLABLE, 'N', '0', '1')) as DATANULLABLE FROM SYS.ALL_TAB_COLS WHERE OWNER = '#SOURCEDBO#' and TABLE_NAME = UPPER('#SOURCETABLE#') AND COLUMN_NAME NOT LIKE 'SYS%\$%' ESCAPE '\' ORDER BY SEGMENT_COLUMN_ID asc, 1 ASC, 2 ASC, 3 ASC

  6. Click Save.

Related articles