Re: Procedure which returns a key value

In article <7k8q8b$lne$1_at_news.rz.uni-karlsruhe.de>,   "M.Weiss" <m.weiss_at_gmx.net> wrote:
> Hi there,
>
> I want to realize a function which shall act as follows:
>
> I've got a table locks having two columns:
> - iLockId integer PRIMARY KEY
> - vcComment varchar2(100)
>
> In addition there is a "on insert"-trigger running which creates
> the primary key value iLockId when inserting a new row. Now,
> when calling the function, for example setLock('Any Comment'),
> the value 'Any Comment' shall be inserted into the table locks.
> So far it's no problem, just a simple insert-statement, the
> trigger does the rest and counts up the key value iLockId.
> Finally now, the function shall return the created value
> iLockId, and there's the problem. How can I grab this value,
> which is created by the trigger ?
>
> I'll appreciate any hint ...
>
> Thanks and regards
> Marcus
>
>

Marcus,

There are two quick answers to your question.

First, are you using sequences to generate the id? If so, you can use the sequence.curr_val attribute of the sequence generator to return the last sequence generated using the current session.

Second, have the trigger store the generated sequence in a global variable within a package. Then your function can use that variable as the return value.

Hope this helps,

David Thompson

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jun 16 1999 - 17:27:33 CDT