return Oracle result-set into Microsoft Access
Greetings, experts!
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.
e.g.
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".
e.g.
var rc refCursor;
exec MySchema.MyProcWithNoInput
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!
DaveSlash
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.
e.g.
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".
e.g.
var rc refCursor;
exec MySchema.MyProcWithNoInput
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!
DaveSlash
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help, johnsone!
Based on your suggestion, I changed the way I'm calling the stored-proc in the Access query:
var rc refCursor;
begin
MySchema.MyProcWithNoInput
end;
I tried that exact thing from within SQL-Plus, SQL Developer, and Toad, and it worked beautifully in all three environments. But, when I use that method inside a pass-through ODBC query from MS Access, I still get an error-message that says, "ORA-0090: invalid SQL Statement".
So, it looks like I need to research how to use the OLEDB driver.
It can't be that uncommon to return an Oracle result-set into MS Access. How do most people accomplish this sort of thing?
Thanks!
DaveSlash
Based on your suggestion, I changed the way I'm calling the stored-proc in the Access query:
var rc refCursor;
begin
MySchema.MyProcWithNoInput
end;
I tried that exact thing from within SQL-Plus, SQL Developer, and Toad, and it worked beautifully in all three environments. But, when I use that method inside a pass-through ODBC query from MS Access, I still get an error-message that says, "ORA-0090: invalid SQL Statement".
So, it looks like I need to research how to use the OLEDB driver.
It can't be that uncommon to return an Oracle result-set into MS Access. How do most people accomplish this sort of thing?
Thanks!
DaveSlash
Honestly, we don't allow anyone to connect Access to Oracle. Causes too many problems. We aren't going to design multi-terabyte mission critical applications with the limitations that Access imposes.
There are other people here that use OLEDB, hopefully one of them will come along and see this. Having not used it, I don't think I would be a whole lot of help with it. There is some sample code in the link that I posted, so maybe that could get you started.
There are other people here that use OLEDB, hopefully one of them will come along and see this. Having not used it, I don't think I would be a whole lot of help with it. There is some sample code in the link that I posted, so maybe that could get you started.
ASKER
Greetings, johnsone!
I ended up getting around this by changing my stored-procedure to insert the results into a table instead of returning a result-set. My Access database calls the stored procedure to populate the table. Then, another Access query returns the results from that table using a simple pass-through query.
It's not quite as "elegant" as my previous method, but I've been doing this long enough to know that it doesn't have to be pretty to get the job done. :-)
I ended up getting around this by changing my stored-procedure to insert the results into a table instead of returning a result-set. My Access database calls the stored procedure to populate the table. Then, another Access query returns the results from that table using a simple pass-through query.
It's not quite as "elegant" as my previous method, but I've been doing this long enough to know that it doesn't have to be pretty to get the job done. :-)
begin MySchema.MyProcWithNoInput
However a lot of languages have their own construct to call a procedure in Oracle which may or may not mask that.