Solved

Truncate and Load into Oracle Table Destination (JDBC)

  • 28 February 2022
  • 5 replies
  • 523 views
R
Rank
Userlevel 1

The data pipeline reads data from a SQLServer Table Origin (JDBC) and loads into Oracle Table Destination (JDBC). i am trying to truncate the table before loading data into oracle destination table (JDBC). Is there any way to truncate the table before load data into destination?

icon

Best answer by Pradeep 28 February 2022, 04:26

View original

5 replies

Pradeep
Rank
Userlevel 3
Badge +1

@raghuramxlsoftek You would want to try Dataflow Trigger ‘Start Event’ for this purpose. 

Start Event option runs before any stage starts processing in the pipeline(Attaching example screenshots).
 a. Configure 'Start Event'  in General settings of the pipeline.

 b. Use the Start Event - JDBC Query tab to run a truncate table query. You might want to use <truncate table if not exists <table-name>>.

 

More details refer https://docs.streamsets.com/portal/datacollector/4.4.x/help/datacollector/UserGuide/Event_Handling/EventFramework-Title.html#concept_amg_2qr_t1b

2 Attachments
R
Rank
Userlevel 1

Hi Pradeep,

 

Thanks for your inputs.

I tried your suggestions, but not able to truncate the table.

My destination is JDBC Producer (Oracle)

Here attached the screen shot for your reference.

 

Regards,

Raghuram

1 Attachment
Pradeep
Rank
Userlevel 3
Badge +1

@raghuramxlsoftek I see you are getting an Oracle SQL syntax error. Truncate query I have shared is just a reference to understand the Start Event functionality. You might want to check https://docs.oracle.com/cd/A58617_01/server.804/a58312/newcha16.htm#:~:text=ORA%2D03291%3A%20invalid%20truncate%20option,command%20and%20retry%20the%20operation. 

R
Rank
Userlevel 1

 

I made the following changes to overcome the error:

  1. Deleted use database
  2. Changed the syntax to truncate table database_name.table_name

Thanks for suggestions.

 

Regards,

Raghuram

antmcmullen
Rank
Userlevel 2

Just a bit more of a Tip certain permission sets can be restrictive in oracle for letting you truncate tables, However if you make a stored procedure that truncates the table, You can call the stored procedure from the start event as per the other answers on here..

 

Just incase you had a problem with non DBAs trying to do this! 

 

 

Reply


Terms & Conditions