Access Pass-Through To Oracle 10g Stored Procedure - Return Result

  • Thread starter Thread starter Blue and Silver
  • Start date Start date
B

Blue and Silver

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.
 
hi,

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.
Click to expand...
I'm not sure about the ref_cursor, it may require additional
permissions, but the correct call to the SP in your passthrough query
must be:

BEGIN EXEC HREMPINFO('1-Jan-1900', '31-Dec-2005'); END;

without line breaks. At least this it, how I got it to work.


mfG
--> stefan <--
 
Back
Top