In my ongoing journey of transitioning my knowledge from DB2 to Oracle, I 'm having a hard time doing something in Oracle that I was able to easily accomplish in DB2. I'm sure I'm missing something easy, but I'm not seeing it.
in DB2, when I called a stored procedure that returned a result-set, I just called the proc with its input parameters.
call MyProcWithNoInputParms ();
The result-set automatically returned into whatever client called the proc.
Calling the proc from within Microsoft Access works exactly the same. the result-set automatically comes back into Access.
It's my understanding that when calling an Oracle proc that returns a result-set, the result-set is included in the parameter-list as a variable of type "refCursor".
var rc refCursor;
exec MySchema.MyProcWithNoInputParms (:rc);
That works beautifully if I run from within Oracle, and I confirmed that the result-set is being returned correctly, but when I try to put that inside a Microsoft Access pass-through query, it throws an error saying:
ORA-00900: invalid SQL statement
I'm pretty sure the ODBC data-source is correct. Since it throws an Oracle error, it must be actually connecting to Oracle.
What am I missing?
Thanks for your help!