Best way to get a list back from oracle via passthrough query?

[mat]

I am used to writing sql server stored procedures that contain an In
parameter and a select statement whose where clause uses that in
parameter to return a list to Access via the pass-through query. I need
to do the same kind of thing with an Oracle 10g backend but so far I've
not had success. Can someone outline an approach that works? If I'm not
mistaken, Oracle procs are not good for this, and views don't take
parameters. Functions can return lists but even though I've been able to
create a function in oracle that seems to work, I can't find a way to
call it successfully in the passthrough query.

Rather that explain all the unsuccessful routes I've taken I hope
someone can give an example for an approach that works.

 

[Stefan Hoffmann]

hi Mat,

I am used to writing sql server stored procedures that contain an In
parameter and a select statement whose where clause uses that in
parameter to return a list to Access via the pass-through query. I need
to do the same kind of thing with an Oracle 10g backend but so far I've
not had success. Can someone outline an approach that works?

The last time I did this, you needed the BEGIN END; block, e.g.

CurrentDb.QueryDefs.Item("pt").SQL = "BEGIN " & StoredProc & " END;"


mfG
--> stefan <--

 

[mat]

hi Mat,

The last time I did this, you needed the BEGIN END; block, e.g.

CurrentDb.QueryDefs.Item("pt").SQL = "BEGIN " & StoredProc & " END;"


mfG
--> stefan <--

But, an oracle procdure cannot return a list in the simple manner than
sql server can, right? So I don't see how that could work.

 

[Stefan Hoffmann]

hi Mat,

But, an oracle procdure cannot return a list in the simple manner than
sql server can, right? So I don't see how that could work.

Ahh, haven't read your initial post carefully enough. I've use global
temporary tables for returning records. Easy to do, and you can link
them, e.g.

CREATE GLOBAL TEMPORARY TABLE youtTempTable$ (
..
)
ON COMMIT PRESERVE ROWS
/

The $ isn't necessary, but I used it as a discriminator.


Otherwise, this may worth a try:

http://decipherinfosys.wordpress.co...eturning-a-recordset-from-a-stored-procedure/



mfG
--> stefan <--

 

[mat]

hi Mat,

Ahh, haven't read your initial post carefully enough. I've use global
temporary tables for returning records. Easy to do, and you can link
them, e.g.

CREATE GLOBAL TEMPORARY TABLE youtTempTable$ (
..
)
ON COMMIT PRESERVE ROWS
/

The $ isn't necessary, but I used it as a discriminator.


Otherwise, this may worth a try:

http://decipherinfosys.wordpress.co...eturning-a-recordset-from-a-stored-procedure/

mfG
--> stefan <--

I see so withing the sproc you send any rows to a temp table. That's a
fine workaround, in fact I've been using it already.

Re the interesting post that you liked to, I've tried returning
refcursors and while it's ok on the oracle side I was not able to get
them to work as passthroughs from Access. I read that refcursors aren't
compatible with Access passthroughs but I'm not sure if there isn't some
workaround.