Ways to pass a calculated recordset from Oracle to Access
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°)
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°)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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°)
> 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°)
>>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
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
ASKER
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°)
Perhaps XML can be returned as result of a function? That would mean some heavy reading, but it could be a solution.
(°v°)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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°)
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
ASKER
Thanks to all for support and comments!
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
>>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
ASKER
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°)
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°)
ASKER
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°)