?
Solved

Ways to pass a calculated recordset from Oracle to Access

Posted on 2010-01-07
12
Medium Priority
?
541 Views
Last Modified: 2013-12-07
I am porting a database to Oracle (10g), while keeping an Access (2003) front-end for the time being.

Several features (searching, navigating, analysing) require a recordset to be built through code, including recursive functions for tree-traversal and data collection. I used to do this in Access with dynamic queries and ADO recordsets.

What are my options in Oracle? I can build a table in memory, but can I return that to an ADO recordset? Can functions or procedures return records? Do I need to create a temporary table and return a view on that table? Do I need to create an iterator to retrieve records one by one?

I'm basically searching for ideas, the implementation is totally open at the moment.

Thanks

Markus -- (°v°)
0
Comment
Question by:harfang
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 200 total points
ID: 26198943
I don't know about Oracle, but have done similar with SQL Server. I used a stored proc in SQL to create a temp table, do all the populating etc. These temp tables exist only for the life of the stored proc, so the final part of the SP is a select * FROM tempTable.
 
I then use ADO to create a command object based on the SP, and the open the recordset using the command.
 
It appears to work really well in that environment.
 
Have even used it to return data via Pass Through Queries.
 
Kelvin
0
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 1600 total points
ID: 26199304
Hi,

Yes stored functions can return a recordset, see:
http://www.oracle-base.com/articles/9i/PipelinedTableFunctions9i.php

You can also create a table to store tempory data:
http://www.oracle-base.com/articles/8i/TemporaryTables.php

About, tree-traversal, the 'connect by' and the 'recusive subquery factoring' may help to do int in SQL. As well as analytic functions. But for complex things, you have tho full power of procedural language with PL/SQL, returning ref cursors, and pipelined functions.

So you have the keywords to search the documentation ;)

Regards,
Franck.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 26199862
It's been a while since I've played with Access to Oracle but unless things have changed quite a bit, can't you just create linked tables and keep everything pretty much as-is?

There were some limitations on datatypes with linked tables but I remember that was primarily with LOBS.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Author Comment

by:harfang
ID: 26200241
Thanks for the support so far.

I haven't been able to return a recordset yet, still playing with it. I have used simple stored procedures (plain SQL queries) and functions, but not anything more complex yet, so it will take some time.

> slightwv

Linked tables work, but the result is too slow. With local Access tables, I can directly navigate indexes to surf the data; the Oracle ODBC drivers do not expose them (one can't use Seek on tables linked from Oracle).

Some background: I navigate about a quarter million botanical names, with several auto-links: taxonomic parent, phylogenic parent, autonym, basionym, hybridation formulas, variants (e.g. orthographic), and two types of synonyms. Most of these links are hierarchical and can be mixed (autonym of the basionym, synonym of a synonym of a hybrid parent, etc)

The questions cannot be expressed using joins. It needs an "inference engine", which I have to write. The result is always a list of names.

Back to my tests...
(°v°)
0
 
LVL 58

Author Comment

by:harfang
ID: 26201739
kelvinsparks,

> so the final part of the SP is a select * FROM tempTable.

Could you elaborate on that? I'm missing some vital point, I guess. I don't see how the select statement would return records to Access (in fact, Oracle requires an INTO clause for a query in a procedure, which makes sense).

I would hate to manage "hard" temporary tables, for various reasons. I hope there is a way to pass the records directly from a function / procedure to Access...

(°v°)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26201829
>>The result is always a list of names.

Could you not move all the business logic over to Oracle?  Pass some parameters in, return the required results?

I've never called Oracle procedures from Access but Google says it's possible:
http://www.techonthenet.com/access/queries/passthrough3.php
0
 
LVL 58

Author Comment

by:harfang
ID: 26202177
That's what I'm doing. The question is only how to report back to the interface...

Perhaps XML can be returned as result of a function? That would mean some heavy reading, but it could be a solution.

(°v°)
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 1600 total points
ID: 26202280
Hi,
On the first link I gave, you will see first:
- first how to return a recordset (in PL/SQL it is a collection that is called 'table' but it is in memory - just a collection of records ). That requires to play with oracle types ( object type and 'table of' type)
- next, how to 'pipeline that': the first example returns rows only once the whole collection is filed. But pipelined functions can return rows on the fly. Better if you have huge number of records: less latency and less memory.

But you can start with the begining.

Regards,
Franck.
0
 
LVL 58

Author Comment

by:harfang
ID: 26202397
Aaaah! Thanks for repeating. I read the pages, tried various things, then others... and missed this one piece of information: the call to table() in the select statement.

To return a dynamic (unnamed temporary) table from a function called get_list(), which can use arguments, call the function in the FROM clause. I think this is what my head refused subconsciously: the idea to call a function in a FROM clause is very bizarre for an old Access hog like me.

This solved it, I can now build my inference engine fully in Oracle!

(°v°)
    With New ADODB.Recordset
        .Open "select * from table(get_list)", SIB.Connection
    End With

Open in new window

0
 
LVL 58

Author Closing Comment

by:harfang
ID: 31673945
Thanks to all for support and comments!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 26202651
I realize you've already closed this but I had to comment on the following statement.

>>Perhaps XML can be returned as result of a function?

If XML s an option, then by all means do it this way.  Generating XML on the fly with Oracle is very easy.

Check out the following SQL functions (there are several but these will handle most issues): XMLELEMENT, XMLFOREST, XMLAGG and XMLATTRIBUTES
0
 
LVL 58

Author Comment

by:harfang
ID: 26203433
Thanks for following up.

At this point there are no fixed options. It's a new project, and all ideas are welcome. However, the existing code base doesn't use XML and if I want to finish the transfer in a reasonable amount of time, I should stick close to it.

One feature of the application is the creation of dynamic lists (with clickable elements). This is done by generating an XML file, shown via an XSLT transformation file in a Webbrowser. The browser events are captured and used to navigate the database.

If there are easier ways to generate XML (it is currently done "by hand"), I'm sure I will look into it. That will be in about one month, I would think. For the moment, I have what I need, but I will read the documentation around the keywords you mention.

Cheers!
(°v°)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

840 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