If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access pass-through query to Oracle 10g stored procedure - results
I am able to create an Access 2003 pass-through query to Oracle 10g that uses
a SELECT statement; it returns a resultset. But, I cannot get a resultset when I try to execute an Oracle stored procedure in a pass-through. I set the stored procedure up with a SYS_REFCURSOR in it. The SP must have a reference cursor to return the resultset. How do I execute this in Access 2003? I have used the following syntax with IN date parameters: EXEC HREMPINFO('1-Jan-1900', '31-Dec-2005'); I also tried putting the name of the parameter between a begin...end block in the pass-through query, that didn't work. I believe the problem is that the SP ref_cursor is looking for a local variable or ref_cursor to send the resultset to. How do I set this up? Thanks in advance. |
#2
|
|||
|
|||
Access pass-through query to Oracle 10g stored procedure - results
Probably your best bet is to have a view created in Oracle using the
SYS_REFCURSOR then using a pass-through query against the view. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Blue and Silver" wrote: I am able to create an Access 2003 pass-through query to Oracle 10g that uses a SELECT statement; it returns a resultset. But, I cannot get a resultset when I try to execute an Oracle stored procedure in a pass-through. I set the stored procedure up with a SYS_REFCURSOR in it. The SP must have a reference cursor to return the resultset. How do I execute this in Access 2003? I have used the following syntax with IN date parameters: EXEC HREMPINFO('1-Jan-1900', '31-Dec-2005'); I also tried putting the name of the parameter between a begin...end block in the pass-through query, that didn't work. I believe the problem is that the SP ref_cursor is looking for a local variable or ref_cursor to send the resultset to. How do I set this up? Thanks in advance. |
Thread Tools | |
Display Modes | |
|
|