• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 836
  • Last Modified:

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?
0
stopher2475
Asked:
stopher2475
  • 2
1 Solution
 
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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now