Re: Q: Stored Procedures and VB?

In article <Dtxu90.7rn_at_cix.compulink.co.uk>,

   cwg_at_cix.compulink.co.uk ("Chun Wong") wrote:
>We've been converting some stored procedures in SQL Server to Oracle
>which are called from VB using ODBC. One of these procedures is a
>simple select statement like:
>
> create procedure xxx _at_varin as
> select * from table1
> where a = _at_varin
> go
>
>In VB, calling this proc with a createsnapshot returns a recordset i.e.
> set snp = db.createsnapshot("xxx " & val1, DB_SQLPASSTHROUGH)
>
>This doesn't seem possible using Oracle procedures! Has anyone else come
>across a similar problem?
>
>Any help would be appreciated.
>
>Regards
>
>Chun.
>

I have exactly the same problem. There seems to be an integral difference between Oracle and Sybase/Microsoft on this front. Whereas MS-SQL uses this as a great way to work out an execution plan, parse as well as compile the request up front, Oracle doesn't seem to recognise the benefit!

If I understand correcly, PL/SQL is viewed as a server-side 4GL rather than a superset of ANSI SQL. In versions up to 7.2 I can only suggest that you select into another table and then retrieve results.

The release notes for Oracle 7.3 mentions that functions can return cursors, and in effect, regular rows. Whether this will arrive as a result set to your client program, I haven't been able to verify yet. I would appreciate any other solutions that you may encounter!

Good Luck
Dion Received on Wed Jul 03 1996 - 00:00:00 CEST