I am trying to call a function from oracle using OLE DB Command for SSIS, I have the connection set up Correctly but I think my Syntax for calling the function is incorrect?
EXEC UPDATE_PERSON ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? output
I have used this on a SQL stored proc for testing & it has worked. the oracal connection is 3rd party & they have just supplied the function name & expected parameters. I should get 1 return parameter.
The Error:
Error at Data Flow Task [OLE DB Command [3013]]: SSIS Error Code DTS_E_OLEDBERROR. AN OLE DB error has occurred. Error code 0x80040e14.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14
Description: "ORA-00900: invalid SQL statement".
in your syntax you have to change the command " EXCU " to " EXEC ".
EXEC UPDATE_PERSON ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? OUTPUT
i have to mention that there is no need for { }
other than that you have to be aware of your full path of the SP that your are executing " by means of specifying [databaseName].[dbo]. if needed
Regards,
S.ANDOURA
Try wrapping it in curly braces:
{EXEC UPDATE_PERSON ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? output}
I don't think Oracle functions have output parameters - are you absolutely certain this is a function and not a proc? functions don't normally perform updates either.
syntax:
SELECT crm_customer.UPDATE_PERSON( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) FROM dual
I will update further details once solution has been completed
Thanks for the help
