Re: StoredProcedures & recordsets

You can not retrieve server side cursors from Oracle via MS access methods (DAO, RDO, ADO). You can via Oracle Objects for OLE (OO4O). OO4O is available on the Oracle 8 Client CD. Below is an example of how to use OO4O in VC++.

<+> This is what your package/stored procedure would look like in Oracle minus error handling and such.
create or replace package Employee as

    type t_cemployees is ref cursor return employees%rowtype;     procedure GetEmp(empid in number, EmpCursor in out t_cemployees); end Employee;
/
create or replace package body Employee as procedure GetEmp(empid in number, EmpCursor in out t_cemployees) is
begin

    open EmpCursor for
    select * from employees where employerId = empid; end GetEmp;
end Employee;
/

<+> This is what your VC++ code would look like with OO4O. ODatabase odb("OracleDatabase", "User", "Password"); OParameterCollection params = odb.GetParameters(); params.Add("EMPID", 20, OPARAMETER_INVAR, OTYPE_NUMBER); ODynaset odyn;
odyn.PlsqlOpen(odb, "Begin Employee.GetEmp (:EMPID,:p_cursor); end;", "EmpCursor");
isopen = odyn.IsOpen();

Need more info? Goto http://www.egroups.com/group/oo4ole/

Zane E

Stu wrote in message <37989383.E4D8B4F6_at_dkmsoft.com>...
>I am new to Oracle, but all I am wishing to do is convert some stored
>procedures from MSSQL6.5.
>However, I have hit a problem straight away, I do not seem to be able to
>get the procedures to behave in the same way.
>The MSSQL procedures are being called from VC++ and the results are held
>in a recordset, but with oracle I cannot see how to
>get them to behave in this way. For example a procedure in MSSQL may
>say:
>
>CREATE PROCEDURE sp_GetEmp @empid int
>as
> select * from employess where employerId=@empid
>return
>
>
>and this would return a recordset which I can iterate through if more
>than one value.
>
>
>
>Is it possible to do the same thing with Oracle (8) to acheive the same
>effect.
>
Received on Sat Jul 24 1999 - 21:18:03 CDT