Hi.. I’m facing an issue while ingesting oracle views into Datahub metadata repository as following: DatabaseError: (cx_Oracle.DatabaseError) DPI-1037: column at array position 0 fetched with error 1406. Could anyone suggest for this please?

Set include_views to False and try again. Let us know if it works.

Yes Abhishek, if i set include_views to False then it works…I suspect the content length of the select case expression for the view which throws the exception, might be longer than the supported type’s length.. I’m not sure…

Hi @clever-australia-61035, not really sure what's going on. Could you please share the full trace so I could know what operation is causing the error?

Thanks Varun.. PFB. Masked user and host details. text = ‘SELECT text FROM all_views WHERE view_name=:view_name AND owner = :schema’     params = {‘view_name’: ‘PRIMARY_DATA_INIT1782941’,          ‘schema’: ‘ABC’}     connection.execute = <method ‘Connection.execute’ of <sqlalchemy.engine.base.Connection object at 0x7f9cd2dcf4e0> base.py:943>     sql.text = <function ‘text’ <string>:1>    ..................................................   File “/home/xxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/result.py”, line 1385, in scalar    1375  def scalar(self):  (...)    1381    1382    return a Python scalar value , or None if no rows remain    1383    1384    “”" --> 1385    row = self.first()    1386    if row is not None:    ..................................................     self = <sqlalchemy.engine.result.ResultProxy object at 0x7f9cc3d7c240>     self.first = <method ‘ResultProxy.first’ of <sqlalchemy.engine.result.ResultProxy object at 0x7f9cc3d7c240> result.py:1347>    ..................................................   File “/home/xxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/result.py”, line 1364, in first    1347  def first(self):  (...)    1360    try:    1361      row = self._fetchone_impl()    1362    except BaseException as e:    1363      self.connection._handle_dbapi_exception( --> 1364        e, None, None, self.cursor, self.context    1365      )    ..................................................     self = <sqlalchemy.engine.result.ResultProxy object at 0x7f9cc3d7c240>     self._fetchone_impl = <method ‘ResultProxy._fetchone_impl’ of <sqlalchemy.engine.result.ResultProxy object at 0x7f9cc3d7c240> result.py:1213>     self.connection._handle_dbapi_exception = <method ‘Connection._handle_dbapi_exception’ of <sqlalchemy.engine.base.Connection object at 0x7f9cd2dcf4e0> base.py:1378>     self.cursor = <cx_Oracle.Cursor on <cx_Oracle.Connection to xxxxx@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxxxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxx)))>>     self.context = <sqlalchemy.dialects.oracle.cx_oracle.OracleExecutionContext_cx_oracle object at 0x7f9cc3d7c160>    ..................................................   File “/home/xxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1511, in _handle_dbapi_exception    1378  def _handle_dbapi_exception(    1379    self, e, statement, parameters, cursor, context    1380  ):  (...)    1507      if newraise:    1508        util.raise_(newraise, with_traceback=exc_info[2], from_=e)    1509      elif should_wrap:    1510        util.raise_( --> 1511          sqlalchemy_exception, with_traceback=exc_info[2], from_=e    1512        )    ..................................................     self = <sqlalchemy.engine.base.Connection object at 0x7f9cd2dcf4e0>     e = DatabaseError(<cx_Oracle._Error object at 0x7f9cc3d7c6f8>,)     statement = None     parameters = None     cursor = <cx_Oracle.Cursor on <cx_Oracle.Connection to xxxxxx@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxxx)))>>     context = <sqlalchemy.dialects.oracle.cx_oracle.OracleExecutionContext_cx_oracle object at 0x7f9cc3d7c160>     newraise = None     util.raise_ = <function ‘raise_’ compat.py:152>     exc_info = (<class ‘cx_Oracle.DatabaseError’>, DatabaseError(<cx_Oracle._Error object at 0x7f9cc3d7c6f8>,), <traceback object at 0x7f9cc3e9c4c8>, )     should_wrap = True     sqlalchemy_exception = DatabaseError(‘(cx_Oracle.DatabaseError) DPI-1037: column at array position 0 fetched with error 1406’,)    ..................................................   File “/home/xxxxxx/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py”, line 182, in raise_    152  def raise_(    153    exception, with_traceback=None, replace_context=None, from_=False    154  ):  (...)    178      # that out.    179      exception.cause = replace_context    180    181    try: --> 182      raise exception    183    finally:   File “/home/xxxxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/result.py”, line 1361, in first    1347  def first(self):  (...)    1357    if self._metadata is None:    1358      return self._non_result(None)    1359    1360    try: --> 1361      row = self._fetchone_impl()    1362    except BaseException as e:    ..................................................     self = <sqlalchemy.engine.result.ResultProxy object at 0x7f9cc3d7c240>     self._metadata = <sqlalchemy.engine.result.ResultMetaData object at 0x7f9cc3e03b48>     self._non_result = <method ‘ResultProxy._non_result’ of <sqlalchemy.engine.result.ResultProxy object at 0x7f9cc3d7c240> result.py:1234>     self._fetchone_impl = <method ‘ResultProxy._fetchone_impl’ of <sqlalchemy.engine.result.ResultProxy object at 0x7f9cc3d7c240> result.py:1213>    ..................................................   File “/home/xxxxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/result.py”, line 1215, in _fetchone_impl    1213  def _fetchone_impl(self):    1214    try: --> 1215      return self.cursor.fetchone()    1216    except AttributeError as err:    ..................................................     self = <sqlalchemy.engine.result.ResultProxy object at 0x7f9cc3d7c240>    ..................................................   ---- (full traceback above) ---- File “/usr/local/lib/python3.6/site-packages/datahub/entrypoints.py”, line 102, in main    sys.exit(datahub(standalone_mode=False, **kwargs)) File “/usr/local/lib/python3.6/site-packages/click/core.py”, line 1128, in call    return self.main(*args, **kwargs) File “/usr/local/lib/python3.6/site-packages/click/core.py”, line 1053, in main    rv = self.invoke(ctx) File “/usr/local/lib/python3.6/site-packages/click/core.py”, line 1659, in invoke    return _process_result(sub_ctx.command.invoke(sub_ctx)) File “/usr/local/lib/python3.6/site-packages/click/core.py”, line 1659, in invoke    return _process_result(sub_ctx.command.invoke(sub_ctx)) File “/usr/local/lib/python3.6/site-packages/click/core.py”, line 1395, in invoke    return ctx.invoke(self.callback, **ctx.params) File “/usr/local/lib/python3.6/site-packages/click/core.py”, line 754, in invoke    return __callback(*args, **kwargs) File “/usr/local/lib/python3.6/site-packages/datahub/telemetry/telemetry.py”, line 141, in wrapper    res = func(*args, **kwargs) File “/usr/local/lib/python3.6/site-packages/datahub/cli/ingest_cli.py”, line 82, in run    pipeline.run() File “/usr/local/lib/python3.6/site-packages/datahub/ingestion/run/pipeline.py”, line 149, in run    self.source.get_workunits(), 10 if self.preview_mode else None File “/usr/local/lib/python3.6/site-packages/datahub/ingestion/source/sql/sql_common.py”, line 362, in get_workunits    yield from self.loop_views(inspector, schema, sql_config) File “/usr/local/lib/python3.6/site-packages/datahub/ingestion/source/sql/sql_common.py”, line 591, in loop_views    view_definition = inspector.get_view_definition(view, schema) File “/home/xxxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py”, line 338, in get_view_definition    self.bind, view_name, schema, info_cache=self.info_cache File “<string>“, line 2, in get_view_definition File “/home/xxxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/reflection.py”, line 52, in cache    ret = fn(self, con, *args, **kw) File “/home/xxxxx/.local/lib/python3.6/site-packages/sqlalchemy/dialects/oracle/base.py”, line 2207, in get_view_definition    rp = connection.execute(sql.text(text), **params).scalar() File “/home/xxxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/result.py”, line 1385, in scalar    row = self.first() File “/home/xxxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/result.py”, line 1364, in first    e, None, None, self.cursor, self.context File “/home/xxxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py”, line 1511, in _handle_dbapi_exception    sqlalchemy_exception, with_traceback=exc_info[2], from_=e File “/home/xxxx/.local/lib/python3.6/site-packages/sqlalchemy/util/compat.py”, line 182, in raise_    raise exception File “/home/xxxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/result.py”, line 1361, in first    row = self._fetchone_impl() File “/home/xxxxx/.local/lib/python3.6/site-packages/sqlalchemy/engine/result.py”, line 1215, in _fetchone_impl    return self.cursor.fetchone()   DatabaseError: (cx_Oracle.DatabaseError) DPI-1037: column at array position 0 fetched with error 1406 (Background on this error at: http://sqlalche.me/e/13/4xp6)

Hi @clever-australia-61035, Is it possible that the specific view we are looking at has a very large view definition? Seems like this error is thrown when a particular string value returned by the db is larger than the variable used to store it in. Checkout this thread - https://stackoverflow.com/questions/18190584/how-to-solve-error-ora-01406-fetched-column-value-was-truncated/18195027

Thanks Varun. I could ingest few views before this specific view. I was also thinking to deny this particular view using the property that you suggested.. but I’m not sure on the syntax to use, i couldn’t see any examples in the documentation as well.. Could you please share an example if you’ve done it before? It would help me a lot.

@clever-australia-61035, I have the same errors as you, secifically the TEXT fro a specific view generates the error in when SQL alchemy tries to get it from ALL_VIEWS table. It does not look like text size matters (no buffer overflow AFAIC) as I have views with larger text definitions that are imported just fine. Did you have any progress on this?