Issue: When building a package in Collect for an Oracle data source, the following error displays, or you are otherwise unable to connect to the data source:
To resolve, try the following suggestions:
- Oracle syntax is quite insistent on their schemas being specified in all caps. Change the [Schema Owner] of the Target Source record in Collect to and ALL CAPS version.
-
Connection issues can also be caused by system columns that have dollar signs ($) in their name.
Update the [Download SQL] value on the vertical of the "Oracle" connection type in Collect -> Administrative -> Connection Types -> 'Sources'
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 '%$%' AND COLUMN_NAME NOT LIKE 'SYS%') ORDER BY SEGMENT_COLUMN_ID asc, 1 ASC, 2 ASC, 3 ASC
You can also use this broader filter to exclude the Oracle system columns:
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%$%' ORDER BY SEGMENT_COLUMN_ID asc, 1 ASC, 2 ASC, 3 ASC
NOTE: Oracle system columns are not required. They typically have a name like SYS_NC00005$ and they are secretly added to Oracle tables to support various functionality (such as indexing). In fact, many people call them "hidden" or "virtual" columns. Here is some additional information:
https://blogs.oracle.com/sql/ora-54033-and-the-hidden-virtual-column-mystery
https://stackoverflow.com/questions/10141498/oracle-function-based-indexes
https://hoopercharles.wordpress.com/2013/03/09/bitten-by-a-virtual-column-_optimizer_ignore_hints-doesnt-ignore-hints/If you need the data in an Oracle system column, BOA recommends asking a DBA to create a view of the table which aliases the system column to a name that doesn't have a dollar sign character in it. That is what messes up Collect's 'Build Package' code.