Using Oracle Bulkload origin for initial data load throws an SQL Exception

asked 2019-10-21 14:15:16 -0600

atrivedi11
11 ●1 ●2 ●2

Hello, I'm trying to setup a simple pipeline with Oracle Bulkload origin and a Data Lake as the destination. However, when I execute the pipeline, I get following error:

com.streamsets.pipeline.api.StageException: ORACLE_03 - SQLException thrown while trying to retrieve table information from table: <schema_name>.<table_name>

I tried to look in the log files but there is not much more information other than the error message repeating.

I am using StreamSets Data Collector 3.10.1 and Oracle Bulkload Enterprise Stage library 1.1.0 along with Oracle JDBC driver ojdbc6.jar

Any pointers?

edit retag flag offensive close merge delete

add a comment

1

answered 2019-11-20 20:47:30 -0600

sakhtar
21 ●2 ●1

updated 2019-11-20 21:03:46 -0600

metadaddy

4830 ●26 ●41 ●77 https://about.me/patpa...

The Oracle Bulkload uses following query:

SELECT DISTINCT MAX(block_id) OVER (PARTITION BY grp) max_blocks FROM
(SELECT block_id, TRUNC((SUM(blocks) OVER (ORDER BY block_id)-0.01)/(SUM(blocks) OVER ()/4)) grp FROM dba_extents WHERE UPPER(segment_name) = UPPER('<<TABLE_NAME>>') AND UPPER
(owner) = UPPER('<<SCHEMA_NAME>>))  ORDER BY max_blocks

So you need to have right to access dba_extents table.

edit flag offensive delete link

add a comment

0

answered 2019-12-05 12:56:48 -0600

atrivedi11
11 ●1 ●2 ●2

I did have the right access to dba_extents table. I was actually using a user with DBA privileges. I did figure out the issue though. The Oracle BulkLoad uses USER_OBJECTS view to get the list of the tables. The issue with using USER_OBJECTS is that it only shows current/logged in user tables. So if I provide a different user that the owner of the tables, then it gets NULL for tables to be loaded. Hence the error.

This is a bug in the coding for Oracle BulkLoad consumer. It should have used ALL_OBJECTS. It can not be expected to share table owner password with other teams. The best practice would be to create a database user with Read Only privileges and use that to get data from the database.