Re: Help with Sequences in Embedded SQL

a copy also emailed to <tmah_at_home.com>

 In article <363CF48A.CAD60F1D_at_home.com>,   Teresa Mah <tmah_at_home.com> wrote:
> Hi,
>
> I've already created a table and a sequence for the primary key of the
> table in SQL*Plus. Now, I want to write an Embedded SQL function which
> inserts tuples into the table, uses the sequence for the primary key
> values. However, when I compile my Embedded SQL code and try to run it,
> nothing gets inserted into the table because the compiler does not
> recognize the sequence name. Does anyone know how to fix this?
>
> Any help would be very much appreciated.
>
> Thank you.
>
> Teresa
>

Teresa,

Just open a cursor for the sequence like you would for any other table. the query in this case uses the pseudo-column nextval. So,

SELECT seqname.NEXTVAL FROM DUAL;

Fetch that value into a host variable that you then use on your insert statement like any other data. Or you can use the sequence directly in the insert statement, like

insert into orders (po_number, item_id, item_count)  values (po_sequence.nextval, :var_item, :var_count) ;

where the var_* are host language variables.

If you have further trouble, post a sample of your code with the problem.

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Nov 02 1998 - 09:27:46 CST