Oracle OLE DB Provider - "Execute SQL Task" to Truncate Table if Exists

• brad.mccollum

  SSCommitted

  Points: 1732

  More actions

  April 20, 2019 at 12:39 am

  #3473294

  I'm changing around some connection items from the old/deprivated Microsoft OLE DB for Oracle Provider to the preferred Oracle Provider for OLE DB.

  In my previous Microsoft OLE DB for Oracle Provider connection manager, I used the following that successfully determined if a specific table existed in Oracle, and if so, the table would be truncated:

  -- Trunate the RPL_ListingsStaging table

  BEGIN

  EXECUTE IMMEDIATE 'truncate table SQLREPLICATION.RPL_ListingsStaging';

  EXCEPTION

  WHEN OTHERS THEN

  IF SQLCODE = -942 THEN

  DBMS_OUTPUT.put_line('the table did not exist!');

  ELSE

  RAISE;

  END IF;

  END;

  GO

  That fails though when I try that same code in a new "Execute SQL Task" statement that uses the newly-created Oracle Provider for OLE DB connection manager.

  I *can*, however, just run this simple statement within an "Execute SQL Task" transformation that uses the Oracle Provider for OLE DB connection manager and the desired table is successfully truncated:

  truncate table SQLREPLICATION.RPL_ListingsStaging

  What I'd like to do, however, is to use the same logic that I used with the Microsoft OLE DB for Oracle Provider connection manager in the "Execute SQL Task" transformation to determine first if the table I'm wanting to truncate is found to exist, and if so, then proceed with the truncation.

  I've tried multiple ways to try to test for the existence of the table using the "Execute SQL Task" that's bound to the new Oracle Provider for OLE DB connection manager and I get a failure of execution for this task no matter of what syntax I try to use, where and how I implement or don't implement double quotes around the table names, etc.

  Any idea if this "Does the table exit then if so, truncate the table" statement/logic can be implemented within an Execute SQL Task SSIS transformation using the Oracle Provider for OLE DB connection manager?

  Many thanks in advance if anyone has done this / has any suggestions?