Re: Stored Procedure to return value (records) through ODBC

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 02 Nov 1998 13:22:41 GMT
Message-ID: <363eaf54.1654969@dcsun4.us.oracle.com>

On Mon, 02 Nov 1998 09:57:36 GMT, craigfullerton_at_my-dejanews.com wrote:

>I have encounted a few issues during my transition from MS SQL (of which i
>have considerable experience) to Oracle8.
>My current bugbear at the moment concerns returning values (through
>recordsets which i can access from ODBC) from a stored procedure.
>My MS SQL code would look something like this :
>
>Create Procedure MyProcedure
> (@inKeyID numeric,
> @iszVariable1 varchar(30),
> @iszVariable2 varchar(30))
>AS
> if @inKeyID > 0
> begin
> update tblMyTable set
> szVariable1 = @iszVariable1,
> szVariable2 = @iszVariable2
> where nKeyID = @inKeyID
> select @inKeyID as nKeyID
> end
> else
> begin
> insert tblMyTable values
> (@iszVariable1,
> @iszVariable2)
> select @@IDENTITY as nKeyID
> end
>GO

I don't know MS SQL code but if I had to guess then what you are doing is trying to update a row with the value @inKeyID or inserting a new row if you do not supply a value and return that new id. If this is right then in PL/SQL you can use in out parameters like...

create or replace
procedure MyProcedure(

  inKeyID      in out number,
  iszVariable1 in     varchar2,
  iszVariable2 in     varchar2 ) is

begin
  if inKeyID > 0 then
    update tblMyTable set

      szVariable1 = iszVariable1,
      szVariable2 = iszVariable2

    where nKeyID = inKeyID;
  else
    select mySeq.nextval

      into inKeyID
      from dual;

    insert into tblMyTable ( nKeyID, szVariable1, szVariable2 )     values ( inKeyID, iszVariable1, iszVariable2 );   end if;
end MyProcedure;
/

I think this is what you want???

Hope it helps.

chris.

>
>
>which works very nicely. Now i'm trying to do something similiar in PL/SQL,
>but i can't see how to get the return value.
>
>Any Ideas?
>
>thanks in advance
>
>---
>craig fullerton
>fab technology (pty) ltd
>craig_at_fab.co.za
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Nov 02 1998 - 07:22:41 CST