Skip to main content
Solved

Truncate and Load into Oracle Table Destination (JDBC)

  • February 28, 2022
  • 5 replies
  • 657 views
R
Roadie

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?

Best answer by Pradeep

@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

View original
    Did this topic help you find an answer to your question?

    5 replies

    Pradeep
    StreamSets Employee
    Forum|alt.badge.img+1
    • PradeepStreamSets Employee
    • StreamSets Employee
    • 48 replies
    • Answer
    • February 28, 2022

    @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
    Drew Kreiger
    1 person likes this
    • Drew Kreiger
      Drew Kreiger
    R
    Roadie

    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 Attachments
    Pradeep
    StreamSets Employee
    Forum|alt.badge.img+1
    • PradeepStreamSets Employee
    • StreamSets Employee
    • 48 replies
    • March 1, 2022

    @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. 

    Drew Kreiger
    1 person likes this
    • Drew Kreiger
      Drew Kreiger
    R
    Roadie

     

    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

    S
    Pradeep
    2 people like this
    • S
      Srinivasan Sankar
    • Pradeep
      Pradeep
    antmcmullen
    StreamSets Employee
    • antmcmullenStreamSets Employee
    • StreamSets Employee
    • 16 replies
    • March 1, 2022

    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! 

     

     

    Drew Kreiger
    1 person likes this
    • Drew Kreiger
      Drew Kreiger

    Reply


    Terms & Conditions