I need to extract data from an Oracle 10g database to SQL server 2005 data
mart using a pl/sql procedure to do the extract from the source system. I
can run PL/SQL selects and create statements from SSIS to an Oracle 10g
database using the MS OLE DB provider for Oracle. I cannot get an execute
statement for an Oracle PL/SQL procedure to execute from an Execute task or
OLE DB source. I can run it from SQL*plus and from the Oracle Developer
interface. I get an ORA-00900 error message back and cannot tell if this
is something related to the provider, oracle permissions, etc.... Grant
execute is given to the procedure for the ID, and have played around with all
options with the OLE DB provider to see it that would help.

It took a bit to find this. Apparently to run an Oracle PL/SQL procedure
from the MS OLE DB provider for Oracle from SSIS you need to add additional
statements to the call. I had to add a declare statement and a begin End
block around the call to get the provider to execute the stored procedure on
Oracle.

declare
begin
vnapoli.test_sql();
end;




"vnapoli" wrote: