MS Access Passthrough Query That Returns Results from Oracle Stored Procedure

Posted on 2014-08-21
Last Modified: 2014-08-21
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:

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?
Question by:stopher2475
    LVL 24

    Accepted Solution

    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
    LVL 2

    Author Comment

    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?
    LVL 24

    Expert Comment

    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

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now