Re: Calling a stored procedure from an insert statement

On 25 Mar 2006 19:56:40 -0800, "bmaxed" <brodellis_at_gmail.com> wrote:

>Can someone provide guidance on how I can call a stored procedure from
>a sql statement. We have a procedure which generates sequence numbers.
> I want to call this procedure to generate a value for an insert
>statement.
>
>Any help is greatly appreciated.

First of all: avoid using your own sequence number mechanism at all costs. It will *always* be less efficient than using Oracle sequences. Secondly: observing the difference between a procedure and a function (but you are probably from a Microsoft T-SQL background so you don't know the difference), you don't need a *procedure* but you need a *function*.
The function should be designed with the pragma autonomous_transaction attribute, and include a COMMIT.

--
Sybrand Bakker, Senior Oracle DBA