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
EXECUTE IMMEDIATE 'truncate table SQLREPLICATION.RPL_ListingsStaging';
WHEN OTHERS THEN
IF SQLCODE = -942 THEN
DBMS_OUTPUT.put_line('the table did not exist!');
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?