Ask Your Question
1

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

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

atrivedi11 gravatar image

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

3 Answers

Sort by ยป oldest newest most voted
1

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

sakhtar gravatar image

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

metadaddy gravatar image

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 more
0

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

atrivedi11 gravatar image

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.

edit flag offensive delete link more
0

answered 2019-10-28 08:57:49 -0600

dong1lkim gravatar image

use ojdbc8

edit flag offensive delete link more

Comments

I did add ojdbc8 external library but still getting the same error.

atrivedi11 gravatar imageatrivedi11 ( 2019-10-29 14:13:08 -0600 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 145 times

Last updated: Dec 05 '19