I’m using an Oracle Database and I’ve been trying to call a Stored Procedure but I can’t seem to find a way to retrieve the value of an OUT parameter in a Studio variable. Both CommandType options (‘Text’ and ‘StoredProcedure’) don’t seem to work properly when using an OUT parameter, regardless of the database activity used (Execute Query/Non Query). Here is what I have tried so far and the respective outputs:
- CommandType: StoredProcedure. Result: Execute Non Query: ERROR  [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement.
In this case, I can’t even make a valid call to the database. Something about the statement seems wrong but according to other forum posts this should work perfectly fine for other databases.
- CommandType: Text. Result: the procedure is correctly called, but no Out parameter is returned to Studio.
In this case, In parameters work but after the procedure is executed it seems like no binding is done between the output variable in Studio and the Out parameter in the procedure. The row is added to the database table but the variable that is supposed to return the recently added table ID remains equal to 0.
For all the calls I’m using this Provider Name: “System.Data.Odbc”.