On Oracle datasource discover table columns metadata and profile columns get table and column metadata fail

[stelapo]

Hello! I am trying to use an Oracle datasource by setting the schema property to perform discovery and profile capabilities. Due to a bug the query run by Soda to get the columns metadata is wrong for two reasons:

• it uses the TABLE_SCHEMA column that does not exist in the ALL_TAB_COLS view; the correct column is OWNER
• filtering the view applies the upper function on the column containing the schema, but changes the value to lower case

Datasource configuration:

data_source oracle_beneficiario_with_connstring:
  type: oracle
  schema: EXAMPLE
  username: ${ORACLE_USER}
  password: ${ORACLE_PASSWORD}
  connectstring: "${ORACLE_HOST}:${ORACLE_PORT}/${ORACLE_SERVICE_NAME}"

Discover and profile configuration:

discover datasets:
  datasets:
    - include MYTABLE

profile columns:
  columns:
    - MYTABLE.%

Errors from Soda discovering:

[11:48:13] Query error: oracle_beneficiario_with_connstring.discover-tables-column-metadata-for-BENEFICIARIO_LIGHT: ORA-00904: "TABLE_SCHEMA": invalid identifier
SELECT COLUMN_NAME, DATA_TYPE, NULLABLE
FROM ALL_TAB_COLS
WHERE upper(table_name) = 'MYTABLE'
  AND upper(table_schema) = 'example'
ORDER BY COLUMN_ID
  | ORA-00904: "TABLE_SCHEMA": invalid identifier

Error from Soda profiling:

[11:48:13] Running column profiling for data source: oracle_beneficiario_with_connstring
[11:48:13] Query oracle_beneficiario_with_connstring.profile-columns-get-table-and-column-metadata:
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM ALL_TAB_COLS
WHERE (((TABLE_NAME LIKE 'MYTABLE') AND (upper(COLUMN_NAME) LIKE upper('%'))))
  AND upper(OWNER) = 'example'
ORDER BY COLUMN_ID
[11:48:13] Your SodaCL profiling expressions did not return any existing dataset name and column name combinations for your 'oracle_beneficiario_with_connstring' data source. 
Please make sure that the patterns in your profiling expressions define existing dataset name and column name combinations. Profiling results may be incomplete or entirely skipped. See the docs for more information:
https://go.soda.io/display-profile
  +-> line=5,col=1 in checks/example_profile.yml

Note: here it uses the right columns name OWNER but it changes the value to lower case.

[tools-soda]

SAS-3248