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.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!
DaveSlash
LVL 18
Dave FordSoftware Developer / Database AdministratorAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
Oracle does not return result sets.  What you are returning is a pointer to a cursor.  At the point where the procedure returns the ref cursor, no rows have actually been fetched on the database side.  Essentially all that has been done on the database side is to parse the query and generate a plan.  No real work has been done.  Oracle will not start the process of running the query until the first fetch is executed.  This is a huge difference from other RDBMS.

I honestly don't know very much about Access, but I did a little digging.

I found this article -> http://support.microsoft.com/en-us/kb/255043

From that article, it appears to me that  you cannot use the ODBC driver to do what you are trying to do.  You need to use the OLEDB driver.  So, if you are using the ODBC driver, my guess is that is where the error is coming from.
0
 
johnsoneSenior Oracle DBACommented:
Also, how are you calling the procedure?  Are you using EXEC?  That is a SQL*Plus construct (more or less a macro).  To properly call it, it should look something like this:

begin MySchema.MyProcWithNoInputParms (:rc); end;

However a lot of languages have their own construct to call a procedure in Oracle which may or may not mask that.
0
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
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.MyProcWithNoInputParms (:rc);
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
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
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. :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.