MS Access Passthrough Query That Returns Results from Oracle Stored Procedure

In the past I've used pass-through queries with SQL Server stored procedures as record sources in access to return record-recordsets that could be used as recordsources for a form or report, etc:
Exec sp_Materials;

Open in new window


I've tried doing this in Oracle and I can still fire off a stored procedure for the purposes of populating a table or updating a table like so:
BEGIN VALIDATION_TOOLS.SP_SETTLED_REBATES_TRIM_FIELDS('14-Aug-14'); COMMIT; END;

Open in new window

but I can't get it to return records like I can with SQL Server. The closest I can get is using a SYS_REFCURSOR to return a cursor I can loop through and see in the script output.
Can someone point me in the right direction to get the same functionality in Oracle that I have with SQL Server?
LVL 2
stopher2475Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Oracle Stored Procedures are implemented differently. They do not "return records". You can use one of the following methods:
1. Populate a temp table with a special column that will uniquely identify your session (if you use your application in multi-user environment (BTW, the temp table can well be a permanent table, if your SP is permanent)
BEGIN VALIDATION_TOOLS.SP_SETTLED_REBATES_TRIM_FIELDS('14-Aug-14', 'yourUniqueSessionID'); COMMIT; SELECT * FROM TempTable WHERE session = 'yourUniqueSessionID'; END;

Open in new window

2. Execute the code from the SP directly from your pass-through query
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stopher2475Author Commented:
I was afraid the answer would be something along those lines.

Regarding temp tables, just reading the documentation it seems like Oracle temporary tables are temporary only in that they are truncated at the end of the session. Do you typically declare the temp table out of the procedure?
0
chaauCommented:
As I said before, it does not need to be a temp table. You can create a table (call it working table) that will be used exclusively by this stored procedure. It can use a certain identifier (it could even be a sequence) that groups the set of rows for it's process. You can delete the records from the working table after you finished with the data. The advantage for using the working table is that there will be no overhead of creating/dropping the table each time the SP runs. Also, you can optimise it with indices
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.