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

  • brad.mccollum

    SSCommitted

    Points: 1721

    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?

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply