How to use Oracle Nextval function from Visual FoxPro

This article was previously published under Q186103
This article has been archived. It is offered "as is" and will no longer be updated.
The sample code below demonstrates how to use the Nextval function of anOracle database from Visual FoxPro. The Nextval function retrieves the nextavailable number from a sequence.

Oracle Sequence is similar to Identity Columns in Microsoft SQL Serverversion 6.0, which contains system-generated sequential values thatuniquely identify each row within a table.

SQL Passthrough sample code

   Sqlhandle=sqlconnect("Oracle_Server")   =Sqlexec(Sqlhandle,"Insert into person(P_id,lastname,firstname) ;   values (PersonSN.nextval,'Lastname','Firstname')")

Remote view

The Remote View designer parses 'PersonSN.nextval' as a field named"nextval" in a table named "PersonSN". Since a table named "PersonSN" doesnot exist, the following error occurs at run-time:
Alias not found.
In order to retrieve the sequence from Oracle, you must call an Oracle UserDefined Function (UDF) before appending a record.

   Oracle Server   -------------
   ** Create a Sequence name "PersonSN"   CREATE SEQUENCE PersonSN   INCREMENT BY 1   START WITH 100001   NOCACHE;   ** Create a Server Function "FNext" to return the next sequence from   ** Person.   CREATE OR REPLACE FUNCTION FNext RETURN NUMBER   IS      Count1 NUMBER(10,2);   BEGIN      SELECT PersonSN.nextval INTO Count1      FROM dual;    RETURN(Count1);   END;   /
   Visual FoxPro   -------------
   =SQLEXEC(Sqlhandle,"Select FNext from dual")
VFoxWin kbDSupport KBDSE

Article ID: 186103 - Last Review: 02/23/2014 01:09:21 - Revision: 3.1

Microsoft Visual FoxPro 3.0 Standard Edition, Microsoft Visual FoxPro 3.0b Standard Edition, Microsoft Visual FoxPro 5.0 Standard Edition, Microsoft Visual FoxPro 5.0a

  • kbnosurvey kbarchive kbhowto KB186103