[AccessD] Calling stored procedures from Access 97 to Oracle 8i

Mark Breen marklbreen at gmail.com
Fri Jan 21 02:19:00 CST 2005


Hello All,

This is more a piece of information than a question.

As you may have seen, I have recently starting work on a project
involving Access 97 and Oracle 8i.  Some of you may also have noticed
that I asked a question about ADO.  To be honest, I wondered why my
colleague wanted to use ADO, why not use DAO I thought.  Of course his
answer was that he wanted to use output parameters in ADO.  I have
never really used output parameters ( in the MS SQL Server
environment), if I want another value, I usually just stick it on the
end of the columns that I am selecting.  Not perfect I know, but it is
fine unless I am returning millions of records.

So, last night I started to write my first Oracle procedure, it was a
simple task I wished to do, select one field from a table and group by
that field.

It turns out that Oracle cannot select (and return) records from
within stored procedures !!!  Sure you can open a cursor and loop
through the data and return them as out parameters and then, when you
get back you command object you can assign that to a recordset.

So two points to make from this
1) sproc's in Oracle cannot just select records
2) DAO cannot deal with output parameters

It comes as no surprise that DAO cannot deal with output params but I
am gobsmacked that Oracle 8i cannot just select a few bloody records
in a sproc and just return them as a recordset.

One option is to insert my work in to temp tables and then select from
them, but that is not really an option with such a big front end as
this.

So, it seems that Oracle likes to return cursors !

I think that I have a mindset change coming on.

Mark



More information about the AccessD mailing list